asked
Multiple updates in a Transaction
Hello All, I'd like to know what happens when I have something like below: BEGIN TRANSACTION 1AB UPDATE tblA SET Cola=Colb WHERE Colc='1213' UPDATE tblA SET Cola=Colb WHERE Colc='1213' --COMMIT TRANSACTION 1AB --ROLLBACK TRANSACTION 1AB If I run those updates and then decide to rollback, will it rollback both updates or only the second one? Thanks, S
Since both update statements are within the transaction, your rollback will undo both updates. This is a frequently used technique to **see the future** and then undo the damage in case if the statements produce undesired result. Oleg
I suppose since both updates are identical it doesn't matter if one, or both of the statements are rolled back :)
@Scot Hauder - yes it would - if it rolled back 1 it would still have made the change, whereas if it rolled back both then it wouldn't...
Oleg is right, just want to add some information. If you want to do a rollback on some of the transaction, you can use [savepoints][1]. With SAVE TRANSACTION you create a savepoint and then you can do a rollback to that savepoint. I haven't seen many implementations of it, but if you are using nested procedures and the outer procedures starts a transaction, it may be okay if it commits even if the inner procedure fails. BEGIN TRANSACTION 1AB UPDATE tblA SET Cola=Colb WHERE Colc='1213' SAVE TRANSACTION mySavePoint UPDATE tblA SET Cola=Colb WHERE Colc='1213' ROLLBACK TRANSACTION mySavePoint --COMMIT TRANSACTION 1AB --ROLLBACK TRANSACTION 1AB [1]:
http://msdn.microsoft.com/en-us/library/ms188378.aspx
One further bit of information - if you issue a ROLLBACK TRANSACTION command that references a named transaction, rather than a save point, then you will roll back everything - even if you are starting a nested transaction. You can use a save point - but, if you create a save point, then call a procedure which issues a rollback, then that procedure will roll back right past your save point all the way to the outer transaction begin. So while save points are good, they are not bullet proof - and you can still get tripped up by calling code which issues a ROLLBACK. Edit -> Clarification from MSDN: >ROLLBACK TRANSACTION erases all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction. >ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
