Nesting Transactions

It's possible to have more than one transaction open at the same time, although it can be tricky to keep track of what happens to each transaction as you commit and roll things back.

Checking How Many Transactions are Open

Each time you begin a new transaction the transaction count increases by one.  When you commit a transaction the transaction count decreases by one.  You can find out how many transactions are open at any point by interrogating a global variable called @@TRANCOUNT.

The result of executing this code is shown in the diagram below:

The transaction count changes as you begin and end transactions.

Nesting Transactions

If you already have one transaction open and you choose to open another, the transaction count will reflect this.

The result of executing this code is shown in the diagram below:

The transaction count increases and decreases as transactions are opened and committed.

Rolling Back Nested Transactions

When you start rolling back nested transactions, things become a little more tricky.  Firstly, if you attempt to rollback a named, nested transaction, you'll find that you aren't able to do so.

Executing the code shown above results in the following error message:

The error message suggests that Tran2 doesn't exist.

Removing the name of the transaction from the rollback statement results in a different error.

After executing the code shown above, this is the message that we see:

The problem here is that there are no remaining transactions to be committed.

As you can see from the transaction counts in the diagram above, when you attempt to rollback a nested transaction SQL Server actually rolls back every transaction that is currently open.

This can also be an issue if you choose to commit the inner, nested transaction and then rollback the outer transaction, as shown in the code below:

Although you wouldn't see any error messages when you try to execute this code, it doesn't do what it appears to do.  Even though it looks like you have committed the inner, nested transaction, when you choose to rollback the outer transaction all of the transactions are rolled back.