BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
This blog examines how to use Transactions in Microsoft SQL Server to allow you to control when data changes are committed to the database. You'll also discover how you can rollback transactions, effectively giving you the chance to undo changes to your data.
- Transactions in SQL Server
- Errors within Transactions
- Nesting Transactions (this blog)
- Transaction Savepoints
This blog is part of a complete SQL Server Tutorial.
Posted by Andrew Gould on 11 October 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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.
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:

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:

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.
- Transactions in SQL Server
- Errors within Transactions
- Nesting Transactions (this blog)
- Transaction Savepoints