SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Fun with Transactions - Part II

Fun with Transactions - Part II

Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51042 Visits: 13160

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 Garth
Tom Garth
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3505 Visits: 1499
Learnt sumpin. Thanks.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers



You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum