Malathi's Blog

Nested Transactions and @@Trancount

Nested Transactions means putting one transaction inside of another – According to BOL if you have one transaction statement ‘nested’ inside another,

“ Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.”

Let us look at practical usage of this statement and also with @@trancount variable which is supposed to show how many transactions are open.All examples are from Adventureworks.

Scenario 1: This is a very regular scenario of two nested transactions and both being committed.

3 Nested transactions are affected by a single commit or rollback. In other words if commit or rollback statements are used with no transaction name, they affect all the 'nested' transactions. This is what leads to the argument that 'nested transaction' is only a term and there is no such thing in reality.

In this situation - the inner 'rollback' gives an error stating 'Cannot roll back tran2. No transaction or savepoint of that name was found.' This further confirms our understanding that there are no 'nested transactions' in reality, only one transaction really exists that can be committed or rolled back.