SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Nakul Vachhrajani / Fun with Transactions - Part I / Latest Posts

RE: Fun with Transactions - Part I

L' Eomot Inversé — Sat, 18 Sep 2010 11:40:13 GMT

[quote][b]da-zero (9/14/2010)[/b][hr][quote]Nesting does work with transactions. It's just that if you use rollback transaction, then all open transactions are rolled back. If you commit a transaction, then only the innermost transaction is committed.[/quote]No, you can't commit an inner transaction - what happens when you issue a commit command is that the transaction count gets decremented, and if the result is zero, the transaction (which is the outermost transaction) is committed (and the data modifications affected by this commit include all made by any so-called nested transactions); if the result is non-zero, nothing is committed. Neither can you roll back a nested transaction - you can only roll back the whole nest (including the outermost transaction).

RE: Fun with Transactions - Part I

mtassin — Wed, 15 Sep 2010 09:07:02 GMT

[quote][b]Nakul Vachhrajani (9/15/2010)[/b][hr]Hey, Mark!That's really good to know!Coincidence is a great phenomenon - I wonder how they happen?Have a wonderful rest-of-the-week ahead![/quote]For me,around here this happens about once every 3 months. But usually I learn about it 2-3 weeks before I need to use it.. not 24 hours :)Tally Tables, Dynamic Paramterized Queries and several other topics typically hit here about 3 weeks before I need them. This time it was in a QOTD. As I said, awesome.

RE: Fun with Transactions - Part I

Nakul Vachhrajani — Wed, 15 Sep 2010 08:40:34 GMT

[quote][b]mtassin (9/15/2010)[/b][hr]I don't even know how excitingly appropriate this is.Yesterday I learned about savepoints. Today a trigger that was invoking a stored procedure that created an inner transaction and rolled it back if a test case was pushed into production.Nobody could figure out why the table with the tirgger was never getting a record.Stuck in a transaction save point right after the BEGIN TRANSACTION in the stored proc and set the ROLLBACK to rollback to the savepoint and made the problem go away.Awesome[/quote]Hey, Mark!That's really good to know!Coincidence is a great phenomenon - I wonder how they happen?Have a wonderful rest-of-the-week ahead!

RE: Fun with Transactions - Part I

mtassin — Wed, 15 Sep 2010 08:37:59 GMT

I don't even know how excitingly appropriate this is.Yesterday I learned about savepoints. Today a trigger that was invoking a stored procedure that created an inner transaction and rolled it back if a test case was pushed into production.Nobody could figure out why the table with the tirgger was never getting a record.Stuck in a transaction save point right after the BEGIN TRANSACTION in the stored proc and set the ROLLBACK to rollback to the savepoint and made the problem go away.Awesome

RE: Fun with Transactions - Part I

Daniel Bowlin — Wed, 15 Sep 2010 07:20:18 GMT

QoTD keeping us on our toes. Nice question, thanks.

RE: Fun with Transactions - Part I

UMG Developer — Tue, 14 Sep 2010 23:29:59 GMT

Nice question and discussion, thanks!

RE: Fun with Transactions - Part I

Cliff Jones — Tue, 14 Sep 2010 12:17:17 GMT

Good question, thanks.

RE: Fun with Transactions - Part I

Rahul26 — Tue, 14 Sep 2010 11:09:01 GMT

try this: CREATE TABLE MyTable (MyId INT IDENTITY (1,1), MyCity NVARCHAR(50))BEGIN TRANSACTION OuterTranINSERT INTO MyTable VALUES ('Boston')BEGIN TRANSACTION InnerTranINSERT INTO MyTable VALUES ('London')ROLLBACK TRANBEGIN TRANSACTION InnerTranINSERT INTO MyTable VALUES ('paris') IF (@@TRANCOUNT = 0)BEGIN PRINT 'All transactions were rolled back' END ELSE BEGIN PRINT 'Outer transaction is still open....rolling back...' ROLLBACK TRANSACTION OuterTran ENDMessage:(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)Outer transaction is still open....rolling back...Msg 6401, Level 16, State 1, Line 23Cannot roll back OuterTran. No transaction or savepoint of that name was found.It will be the else loop case.

RE: Fun with Transactions - Part I

richard.maw — Tue, 14 Sep 2010 10:27:22 GMT

You cannot simply rollback a named nested transaction you can only rollback to a savepoint."Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back".http://msdn.microsoft.com/en-us/library/ms188929.aspxThis example uses a savepoint to rollback a nested transaction:CREATE TABLE MyTable (MyId INT IDENTITY (1,1), MyCity NVARCHAR(50))BEGIN TRANSACTION OuterTranINSERT INTO MyTable VALUES ('Boston')Save Transaction SavePoint1BEGIN TRANSACTION InnerTranINSERT INTO MyTable VALUES ('London')ROLLBACK TRAN SavePoint1 IF (@@TRANCOUNT = 0)BEGIN PRINT 'All transactions were rolled back' END ELSE BEGIN PRINT 'Outer transaction is still open....rolling back...' ROLLBACK TRANSACTION OuterTran ENDDROP TABLE MyTablego

RE: Fun with Transactions - Part I

marklegosz — Tue, 14 Sep 2010 09:15:28 GMT

[quote][b]LUCAB (9/14/2010)[/b][hr]I was wrong answer: "Outer transaction is still open....rolling back... ".I learned that my wrong answer can be obtained with a SAVEPOINT:[code="sql"]CREATE TABLE MyTable (MyId INT IDENTITY (1,1), MyCity NVARCHAR(50))BEGIN TRANSACTION OuterTranINSERT INTO MyTable VALUES ('Boston')BEGIN TRANSACTION InnerTranSAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINTINSERT INTO MyTable VALUES ('London')ROLLBACK TRAN InnerTranSavePoint -- MODIFIED CODE: RETURN TO SAVE TRAN IF (@@TRANCOUNT = 0)BEGIN PRINT 'All transactions were rolled back' END ELSE BEGIN PRINT 'Outer transaction is still open....rolling back...' ROLLBACK TRANSACTION OuterTran ENDDROP TABLE MyTable[/code][/quote]In the above code, the line [code="sql"]BEGIN TRANSACTION InnerTran[/code]Does not appear to be required (at least to effect the same result).

RE: Fun with Transactions - Part I

Lana Gold — Tue, 14 Sep 2010 09:14:41 GMT

A pleasant question.

RE: Fun with Transactions - Part I

KWymore — Tue, 14 Sep 2010 08:32:41 GMT

Very nice question. I was unaware of savepoints for transactions either.

RE: Fun with Transactions - Part I

daveb87 — Tue, 14 Sep 2010 08:30:18 GMT

Always good to have a refresher on transaction basics! Thanks

RE: Fun with Transactions - Part I

mtassin — Tue, 14 Sep 2010 07:04:43 GMT

[quote][b]LUCAB (9/14/2010)[/b][hr]I was wrong answer: "Outer transaction is still open....rolling back... ".I learned that my wrong answer can be obtained with a SAVEPOINT:[code="sql"]SAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINT[/code][/quote]And while the actual question didn't teach me anything new, I hadn't heard of Transaction savepoints... that's really neat.

RE: Fun with Transactions - Part I

sjimmo — Tue, 14 Sep 2010 06:25:50 GMT

Good question - almost got me. If i hadn't have reread the code and noticed the way the rollback was written. Proves that rereading code is good.

RE: Fun with Transactions - Part I

larsts — Tue, 14 Sep 2010 05:27:10 GMT

Learned something new today:)

RE: Fun with Transactions - Part I

LUCAB — Tue, 14 Sep 2010 01:59:25 GMT

I was wrong answer: "Outer transaction is still open....rolling back... ".I learned that my wrong answer can be obtained with a SAVEPOINT:[code="sql"]CREATE TABLE MyTable (MyId INT IDENTITY (1,1), MyCity NVARCHAR(50))BEGIN TRANSACTION OuterTranINSERT INTO MyTable VALUES ('Boston')BEGIN TRANSACTION InnerTranSAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINTINSERT INTO MyTable VALUES ('London')ROLLBACK TRAN InnerTranSavePoint -- MODIFIED CODE: RETURN TO SAVE TRAN IF (@@TRANCOUNT = 0)BEGIN PRINT 'All transactions were rolled back' END ELSE BEGIN PRINT 'Outer transaction is still open....rolling back...' ROLLBACK TRANSACTION OuterTran ENDDROP TABLE MyTable[/code]

RE: Fun with Transactions - Part I

phil.wood 94423 — Tue, 14 Sep 2010 01:57:48 GMT

That'll teach me to shoot before I aim.Thanks

RE: Fun with Transactions - Part I

Koen Verbeeck — Tue, 14 Sep 2010 01:52:42 GMT

[quote][b]phil.wood 94423 (9/14/2010)[/b][hr]Oops, I got it wrong.In my defence, nesting works all over Microsoft so I'm a little surprised they never got it working here. Almost seems like an oversight.Would have made more sense if they had employed a slightly different keyword such as e.g.ROLLBACK TRAN ALLorROLLBACK TRANSACTIONSJust my view :-)[/quote]Nesting does work with transactions. It's just that if you use rollback transaction, then all open transactions are rolled back. If you commit a transaction, then only the innermost transaction is committed.If you really want full nesting, you can use checkpointing in your transactions and use named transactions.

RE: Fun with Transactions - Part I

phil.wood 94423 — Tue, 14 Sep 2010 01:46:55 GMT

Oops, I got it wrong.In my defence, nesting works all over Microsoft so I'm a little surprised they never got it working here. Almost seems like an oversight.Would have made more sense if they had employed a slightly different keyword such as e.g.ROLLBACK TRAN ALLorROLLBACK TRANSACTIONSJust my view :-)

RE: Fun with Transactions - Part I

Nakul Vachhrajani — Tue, 14 Sep 2010 01:14:25 GMT

[quote][b]da-zero (9/14/2010)[/b][hr]Nice question about the transaction basics. As I'm reading the self-paced training kit for the database developper certification, this was a really easy one for me, as it is explained quite clearly in the book.[/quote]It is really good to know that the question helped in your preparations. Best of luck for your exam!

RE: Fun with Transactions - Part I

Koen Verbeeck — Tue, 14 Sep 2010 00:57:49 GMT

Nice question about the transaction basics. As I'm reading the self-paced training kit for the database developper certification, this was a really easy one for me, as it is explained quite clearly in the book.

RE: Fun with Transactions - Part I

rals — Tue, 14 Sep 2010 00:08:05 GMT

My guess is right. :-)Nice Question.

RE: Fun with Transactions - Part I

deepak.a — Mon, 13 Sep 2010 23:55:14 GMT

Good Question thanks :)

RE: Fun with Transactions - Part I

ziangij — Mon, 13 Sep 2010 22:41:52 GMT

learned something new today... thanks...:-)

RE: Fun with Transactions - Part I

SQLRNNR — Mon, 13 Sep 2010 21:32:01 GMT

Nice question - thanks.

Fun with Transactions - Part I

Nakul Vachhrajani — Mon, 13 Sep 2010 21:31:46 GMT

Comments posted to this topic are about the item [B]Fun with Transactions - Part I[/B]