Transactions in SQL Server
Part three of a four-part series of blogs

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.

  1. Transactions in SQL Server
  2. Errors within Transactions
  3. Nesting Transactions (this blog)
  4. 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:

Counting transactions

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:

Opening nested transactions

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.

This blog has 0 threads Add post