A Fine Slice Of SQL Server
Gail Shaw asks, why name transactions?
So what conclusion can we come to here? Pretty much that naming of transactions has one real use, as a form of documentation. Instead of putting a comment above an BEGIN TRANSACTION we can give the transaction a name that indicates what the transaction does, That’s about the only real use.
With two exceptions.
The one reason I have to name transactions: name and shame.
Comments closedGail Shaw has part 2 of her transactions series up:
Again, exactly the desired behaviour. The changes made in the outer procedure were committed, the changes in the inner procedure, the procedure where the error was thrown, were rolled back.
Used correctly, savepoints can be a powerful mechanism for managing transactions in SQL Server. Unfortunately they’re not well known and as such their use can also make code much harder for later developers to debug.
I’ve used conditional transactions fairly regularly (procedures can have calling parent procedures, or sometimes can be called on their own), but never savepoints.
Comments closedFriends don’t let friends nest transactions:
Before getting into the details, I need to make one thing clear. Nested transactions are a lie. They do not exist in SQL Server.
This is part 1 of a three-part series by Gail Shaw. Read the whole thing. Also read Paul Randal:
Nested transactions do not actually behave the way the syntax would have you believe. I have no idea why they were coded this way in SQL Server – all I can think of is someone from the dim and distant past is continually thumbing their nose at the SQL Server community and going “ha – fooled you!!”.
Nested transactions are somebody’s attempt at trolling. They succeeded.
Comments closed