Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Fun with Transactions - Part II Expand / Collapse
Author
Message
Posted Thursday, September 16, 2010 5:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 1,032, Visits: 1,295
Nice question and good explanation from Hugo. Thanks

Thanks
Post #987115
Posted Thursday, September 16, 2010 6:40 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
Nakul Vachhrajani (9/16/2010)
Hugo Kornelis (9/16/2010)
This question is actually almost similar to the one two days ago. The only real difference is the use of the extra keywork WORK (that is not required).


That is correct, Hugo. In fact there are 2 more on similar lines coming up. I originally encountered the behaviour that was covered in the question 2 days ago. The rest of the questions are derivatives of my research on the original question.

I hope you like them all


Ah crap. Not more. Have already lost 4 points aint that enough... have mercy
Post #987184
Posted Thursday, September 16, 2010 6:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
Great question, great follow on discussion. Thanks.
Post #987198
Posted Thursday, September 16, 2010 7:57 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:30 AM
Points: 3,871, Visits: 3,621
Thanks Hugo for the explanation.
Post #987268
Posted Thursday, September 16, 2010 8:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
Thanks Nakul for a very good question and to Hugo for the detailed explanation.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #987277
Posted Thursday, September 16, 2010 2:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 12, 2013 2:07 PM
Points: 168, Visits: 38
Good questions, it is very clear when you wrote statement
(if _statement) after that. If should be the first print. Because you mention before two insert statement, the last statement you make roll back. What is meant it? That all insert that you make if roll back... That’s I thought...
Post #987681
Posted Thursday, September 16, 2010 2:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 21,290, Visits: 14,982
Hugo Kornelis (9/16/2010)
CirquedeSQLeil (9/15/2010)
Good question. I think it is worth noting that this behavior is exhibited because nested transactions do not truly exist in SQL Server.

I have to disagree with you, Jason. Nested transactions do exist, and they work exactly as I think common sense dictates.

Transactions are primarily intended to implement the A and I of the ACID properties: atomicity and isolation. Nessting fringes with that goal, and I can easily imagine that the people on the ANSI committee have considered not allowing nested transactions. But that would invalidate many common use cases. If I write a stored procedure that does multiple things but should be considered as a single unit of work, I use BEGIN TRAN and COMMIT TRAN (or ROLLBACK TRAN in case of error) in the procedure code. But what if I next have to implement a stored procedure that is also considered a single unit of work, but that includes the first stored procedure? I use BEGIN TRAN/COMMIT TRAN in the outer procedure; I call the inner procedure, and there we have the nesting. Prohibiting nesting would requiere me to duplicate the code, which I obviously don't want.

Support for nested transactions is unavoidable. But what are the "most correct" semantics? Consider the example above. The inner stored procedure implements actions 2.1 and 2.2, as a single unit of work. The outer stored procedure implements actions 1, 2.1, 2,2, and 3, also as a single unit of work. And it does so by caling the inner procedure.
So what should a nested COMMIT do? It can not really commit the changes of actions 2.1 and 2.2. After all, action 3 might still fail, and in that case the whole outer procedure needs to be rolled back, including actions 2.1 and 2.2. Otherwise, the outer procedure would not be atomic. So the only thing the COMMIT can do is to decrease the nesting level counter; actually committing the data has to be postponed until all nesteed transactions have finished.
Conversely, a ROLLBACK in the nested transaction should roll back ALL open transactions. If it would only rollback the effects of the nested transaction, the outer procedure could continue to perform action 3 and commit. IN that case, actions 1 and 3 are committed, but actions 2.1 and 2.2 are not. The procedure is no longer ACID. Making any rollback, regardless of nesting level, roll back ALL open work prevents that problem.

This question is actually almost similar to the one two days ago. The only real difference is the use of the extra keywork WORK (that is not required).


I will defer to this:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #987726
Posted Thursday, September 16, 2010 2:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:30 PM
Points: 21,290, Visits: 14,982
Adi Cohn-120898 (9/16/2010)
CirquedeSQLeil (9/15/2010)
Good question. I think it is worth noting that this behavior is exhibited because nested transactions do not truly exist in SQL Server.


It is worth mentioning that although nested transaction doesn't exist in SQL Server, you can still rollback parts of transaction and commit the rest of the transaction with the use of save points

Adi


Good point.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #987728
Posted Friday, September 17, 2010 12:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 11:46 PM
Points: 70, Visits: 123
CirquedeSQLeil (9/16/2010)
Hugo Kornelis (9/16/2010)
CirquedeSQLeil (9/15/2010)
Good question. I think it is worth noting that this behavior is exhibited because nested transactions do not truly exist in SQL Server.

I have to disagree with you, Jason. Nested transactions do exist, and they work exactly as I think common sense dictates.

Transactions are primarily intended to implement the A and I of the ACID properties: atomicity and isolation. Nessting fringes with that goal, and I can easily imagine that the people on the ANSI committee have considered not allowing nested transactions. But that would invalidate many common use cases. If I write a stored procedure that does multiple things but should be considered as a single unit of work, I use BEGIN TRAN and COMMIT TRAN (or ROLLBACK TRAN in case of error) in the procedure code. But what if I next have to implement a stored procedure that is also considered a single unit of work, but that includes the first stored procedure? I use BEGIN TRAN/COMMIT TRAN in the outer procedure; I call the inner procedure, and there we have the nesting. Prohibiting nesting would requiere me to duplicate the code, which I obviously don't want.

Support for nested transactions is unavoidable. But what are the "most correct" semantics? Consider the example above. The inner stored procedure implements actions 2.1 and 2.2, as a single unit of work. The outer stored procedure implements actions 1, 2.1, 2,2, and 3, also as a single unit of work. And it does so by caling the inner procedure.
So what should a nested COMMIT do? It can not really commit the changes of actions 2.1 and 2.2. After all, action 3 might still fail, and in that case the whole outer procedure needs to be rolled back, including actions 2.1 and 2.2. Otherwise, the outer procedure would not be atomic. So the only thing the COMMIT can do is to decrease the nesting level counter; actually committing the data has to be postponed until all nesteed transactions have finished.
Conversely, a ROLLBACK in the nested transaction should roll back ALL open transactions. If it would only rollback the effects of the nested transaction, the outer procedure could continue to perform action 3 and commit. IN that case, actions 1 and 3 are committed, but actions 2.1 and 2.2 are not. The procedure is no longer ACID. Making any rollback, regardless of nesting level, roll back ALL open work prevents that problem.

This question is actually almost similar to the one two days ago. The only real difference is the use of the extra keywork WORK (that is not required).


I will defer to this:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx


To add to Mr. Randal's excellent points in the linked article, I'd like to observe that Hugo's example of two stored procedures, each containing a ROLLBACK statement, doesn't work cleanly.

Supposed ProcA begins a transaction, then executes ProcB. ProcB begins a "nested" transaction and then performs a ROLLBACK before exiting. When ProcB exits, SQL Server raises error # 266 - "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0." Here I disagree with Hugo that this is as "common sense dictates."

To cover your bases, every stored procedure that might perform a ROLLBACK needs to always check that @@TRANCOUNT = 1 before rolling back. If I'm going to have to always check whether I'm in a "nested" transaction prior to rolling back, I might as well check for an open transaction prior to performing BEGIN TRAN in the first place, rather than pretend nested transactions are real in SQL Server.

Or am I missing something?
Post #988488
Posted Friday, September 17, 2010 2:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 5,930, Visits: 8,179
Terry Mott (9/17/2010)
Supposed ProcA begins a transaction, then executes ProcB. ProcB begins a "nested" transaction and then performs a ROLLBACK before exiting. When ProcB exits, SQL Server raises error # 266 - "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0." Here I disagree with Hugo that this is as "common sense dictates."

To cover your bases, every stored procedure that might perform a ROLLBACK needs to always check that @@TRANCOUNT = 1 before rolling back. If I'm going to have to always check whether I'm in a "nested" transaction prior to rolling back, I might as well check for an open transaction prior to performing BEGIN TRAN in the first place, rather than pretend nested transactions are real in SQL Server.

Or am I missing something?

Hi Terry,

As an old-school lover of clean programming, I can only endorse this - but make sure that you also return the error condition to the calling stored procedure. If the nested procedure runs into an error, rolls back all changes, and then starts a new transaction to bump the @@TRANCOUNT back to 1, the calling procedure needs to know in some other way that it should not continue performing the rest of the actions and then committing that partial change.

The error #266 you mention, while ugly as can be, at least ensures that the calling procedure doesn't continue as if all is dandy!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #988548
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse