September 16, 2010 at 2:03 pm
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...
September 16, 2010 at 2:40 pm
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:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2010 at 2:41 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 12:23 pm
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:
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?
September 17, 2010 at 2:08 pm
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!
September 18, 2010 at 11:25 am
CirquedeSQLeil (9/16/2010)
I will defer to this:
Hugo's description falls into the trap of believing what MS apparently believes: that SQL Server supports nested transactions using the begin transaction statement; that means that his definition of nested transactions is one that allows nothing more than a purely syntactic appearence of nesting, where the nested begin and commit statements don't begin or commit transactions they just increment and decrement a counter and this nonexistent transaction can not be rolled back except as part of the operation of rolling back the outermost transaction. In addition there's the whole issue of what happens with triggers, which is outright bizarre and suggests that MS has lost the plot completely on the nested transaction story: the trigger creates an implied nested transaction on entry, and trancount is incremented by 1; after executing a rollback, the trigger can continue to make modifications which are not rolled back, but which can not cause nested triggers to be invoked, and when the trigger exits the remaining statements in the batch that invoked it are not executed (but modifications made by statements after a rollback will invoke triggers unless the rollback was in a trigger, so there's no consistency of approach here at all).
Paul Randall's comments which you reference appear to be mainly concerned with the issue that log entries can't be cleared down by committing a nested transaction (or actually more concerned that some people haven't realised that this is an essential property of nested transactions if we are to maintain the ACID properties). He does also make the point about being unable to roll back an inner transaction without rolling back the whole nest - and to me that's the crucial definitional issue: if you don't have innner rollback, you don't have nested transactions.
So what this mechanism is is a syntactic device to ensure that if something the carries out a transaction gets invoked from inside another transaction it doesn't create a new transaction at all, just changes the behaviour of the commit statement in such a way as to deliver a safe result.
Of course it is possible to get something not too far off nested transactions (ones in which the inner transaction can be rolled back without rolling back the outer transaction) by using save transaction with rollback to save point: but even this is not true nesting, because lock escalations made for the actions that are rolled back are not reversed; and there is an awful lot of messing around with testing trancount and xact_state to get it to work sensibly at all (because in fact no new transaction is created - what we have here is an attempt to provide a syntactic device independent of transaction start and rollback to support the semantics of true nested transactions without creating any nested transaction!), and even then it won't work with distributed transactions.
And then there are fully nested transactions, which are not ACID, so they are not transactions in the database sense. So it's eminently sensible for an RDBMS like SQL Server not to support them. (Paul R's main concern about "nested transactions" in SQL Server may be that far too many people think that these are what MS means when it talks about nested transactions; that's certainly a valid concern - there's nothing MS can do to stop people being stupid, but using a terminology that encourages them to believe something that just ain't so is not really a good idea). They are necessary in many other bits of software engineering - it's extremely common to see Atomicity differently at different levels of abstraction, so if a bundle of software has to operate at several different levels of abstraction it is likely that there will be transactions at a low level which are nested in transactions at a higher level of abstraction but are required to commit independently of the higher level (the higher level will then, if it rolls back, have to initiate new lower level transactions to reverse the already committed lower level ones) - this means that teh A and I properties are not automatically supported.
Tom
September 25, 2010 at 5:04 am
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply