Rolling back database changes

Question

Hi,

I work in team which uses Work flow management tool. I was presented with a challenge where the user should be able to roll back the changes made anytime during the flow to a certain point in the past. Surely the toll can handle it but the additional database calls that may have made during these activities have to be manually rolled back.

the challenge is that there are multiple parallel paths and there different permutations of external database calls. So is there a frame work or a way to keep track of these DB calls and rollback them?.

thank you, Justin

Answer 1

Hmm, i dont think so.

In Entity Framework for example you have TRANSACTIONS and ROLLBACK function but this ROLLBACK only changes in this TRANSACTION from begining of this start (START TRANSACTION - SQL Statement)

Then you will must have in multiple parallel paths start a TRANSACTION and take a decision of COMMIT or ROLLBACK changes. But before COMMIT other parallel instances will not see changes maded in other TRANSACTIONS who will not COMMITED yet.

I think, you must do some refactorization on your application/database, but this is only offtopic hint.

Answer 2

Are these changes committed to the database already at an intermediate state? That's going to be problematic. Since other users may have started actions based on committed data.

I have seen systems which support n-level undo prior to commit (CSLA does this), but this is at the object model level in memory.

You would not hold a database transaction open indefinitely waiting for user action.

Answer 3

Do you need to rollback or do you need the data to be in a certain state? I ask since if the latter is the case, then it is a matter or setting your record back to values that you had prior to Workflow step X.

There is a design pattern called the Momento Pattern that takes a snapshot of your objects at key intervals and persists that data in addition to the "current" data in your database. This type transaction could be stored as a serialization of your objects and recorded and hence you would need to create a history table for every table that you currently have.

The trick will be to restore your objects from the Memento transactions, the save the data back to you standard tables.