This page has 0 threads | Add post

Nesting Transactions

Written by Andrew Gould

In this tutorial

Checking How Many Transactions are Open
Nesting Transactions
Rolling Back Nested 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.

PRINT 'Original number of transactions'

PRINT @@TRANCOUNT

BEGIN TRAN Tran1

PRINT 'New number of transactions'

PRINT @@TRANCOUNT

COMMIT TRAN Tran1

PRINT 'Final number of transactions'

PRINT @@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.

BEGIN TRAN Tran1

PRINT 'After opening Tran1'

PRINT @@TRANCOUNT

BEGIN TRAN Tran2

PRINT 'After opening Tran2'

PRINT @@TRANCOUNT

COMMIT TRAN Tran2

PRINT 'After committing Tran2'

PRINT @@TRANCOUNT

COMMIT TRAN Tran1

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.

BEGIN TRAN Tran1

PRINT 'After opening Tran1'

PRINT @@TRANCOUNT

BEGIN TRAN Tran2

PRINT 'After opening Tran2'

PRINT @@TRANCOUNT

ROLLBACK TRAN Tran2

PRINT 'After rolling back Tran2'

PRINT @@TRANCOUNT

COMMIT TRAN Tran1

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

Error when rollling back nested transaction

The error message suggests that Tran2 doesn't exist.

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

BEGIN TRAN Tran1

PRINT 'After opening Tran1'

PRINT @@TRANCOUNT

BEGIN TRAN Tran2

PRINT 'After opening Tran2'

PRINT @@TRANCOUNT

ROLLBACK TRAN

PRINT 'After rolling back Tran2'

PRINT @@TRANCOUNT

COMMIT TRAN Tran1

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

Error from rolling back nested transaction

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:

BEGIN TRAN Tran1

PRINT 'After opening Tran1'

PRINT @@TRANCOUNT

BEGIN TRAN Tran2

PRINT 'After opening Tran2'

PRINT @@TRANCOUNT

COMMIT TRAN Tran2

PRINT 'After committing Tran2'

PRINT @@TRANCOUNT

ROLLBACK TRAN Tran1

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.

Previous
tutorial

Dynamic SQL

Previous
part

Errors within Transactions

Top of page

Next
part

Transaction Savepoints

Next
tutorial

Cursors

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Transactions

Nesting Transactions

Checking How Many Transactions are Open

Nesting Transactions

Rolling Back Nested Transactions

Head office

London

Manchester