This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Transactions in SQL Server Errors within Transactions Nesting Transactions (this blog) Transaction Savepoints This blog is part of a complete SQL Server Tutorial.

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.

This blog is part of a complete SQL Server Tutorial.

Posted by Andrew Gould on 11 October 2013

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:

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.

Parts of this blog
Transactions in SQL Server Errors within Transactions Nesting Transactions (this blog) Transaction Savepoints

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Nesting Transactions

Checking How Many Transactions are Open

Nesting Transactions

Rolling Back Nested Transactions

Head office

London

Manchester