Nested Transactions Aren’t

Friends 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.

Related Posts

Benefits Of Explicit Transactions

Kendra Little talks about explicit transactions and when they’re useful for single-statement operations: If you do not enable implicit transactions, and you don’t start an explicit transaction, you are in the default “autocommit” mode. This mode means that individual statements are automatically committed or rolled back as whole units. You can’t end up in a […]

Read More

Phantom Reads

Arun Sirpal sees not-quite-there-yet transactions: With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction. I will show you what this looks like with an example. Please […]

Read More


November 2015
« Jan Dec »