x

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

more ▼

asked Oct 06, 2010 at 01:31 PM in Default

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Oct 06, 2010 at 01:47 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

Thanks. As I expected, just wanted to verify my suspicion.
Oct 06, 2010 at 01:48 PM Slick84
I suppose since both updates are identical it doesn't matter if one, or both of the statements are rolled back :)
Oct 07, 2010 at 05:36 AM Scot Hauder
@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...
Oct 07, 2010 at 05:42 AM Matt Whitfield ♦♦
@Matt fair enough, the jager was talking again...
Oct 07, 2010 at 03:29 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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. 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
more ▼

answered Oct 06, 2010 at 02:05 PM

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 07, 2010 at 01:49 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

I guess thats why savepoints aren't used that much.
Oct 08, 2010 at 01:22 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x292
x133

asked: Oct 06, 2010 at 01:31 PM

Seen: 2299 times

Last Updated: Oct 06, 2010 at 01:53 PM