How do I rollback a transaction that has already been committed?

Question

I am implementing an undo button for a lengthy operation on a web app. Since the undo will come in another request, I have to commit the operation.

Is there a way to issue a hint on the transaction like "maybe rollback"? So after the transaction is committed, I could still rollback in another processes if needed.

Otherwise the Undo function will be as complex as the operation it is undoing.

Is this possible? Other ideas welcome!

Answer 1

Another option you might want to consider: If it's possible for the user to 'undo' the operation, you may want to implement an 'intent' table where you can store pending operations. Once you go through your application flow, the user would need to Accept or Undo the operation, at which point you can just run the pending transaction and apply it to your database.

We have a similar system in place on our web application, where a user can submit a transaction for processing and has until 5pm on the day it's scheduled to run to cancel it. We store this in an intent table and process any transactions scheduled for that day after the daily cutoff time. In your case you would need an explicit 'Accept' or 'Undo' operation from the user after the initial 'lengthy operation', so that would change your process a little bit.

Hope this helps.

Answer 2

The idea in this case is to log for each operation - a counter operation that do the opposite, in a special log and when you need to rollback you actually run the commands that you've logged.

some databases have flashback technology that you can ask the DB to go back to certain date and time. but you need to understand how it works, and make sure it will only effect the data you want and not other staff...

Oracle Flashback

I don't think there is a similar technology on SQL server and there is a SO answer that says it doesn't, but SQL keeps evolving...