Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««123

Fun with Transactions - Part II Expand / Collapse
Posted Saturday, September 18, 2010 11:25 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:34 AM
Points: 9,823, Visits: 11,892

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.

Post #988761
Posted Saturday, September 25, 2010 5:04 AM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #993247
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse