Here’s the deal with nested transactions:
There really isn’t a concept of “nested transactions” in SQL Server. That is, an inner transaction that can operate independently of an outer transaction.
Let’s work through an example using the content from an Orders table:
The following code is a very simple example of a single transaction that performs a ROLLBACK:
The code within the transaction attempted to delete all rows from the Orders table, but the ROLLBACK operation basically undid that work. If we check the content of the Orders table again, everything is still there:
But let’s change things up. Let’s put this transaction within another transaction and change it from doing a ROLLBACK to actually doing a COMMIT.
One might look at this and think “Okay, the inner transaction will actually commit the changes to the database, and the outer transaction will attempt to do a ROLLBACK but it will be a wasted effort because the changes were already committed.”
But that would be wrong.
Folks, here’s a golden rule you need to remember:
COMMIT doesn’t actually mean anything until it changes the transaction count from 1 to 0.
When a COMMIT command changes the transaction count from 1 to 0, that is when any changes are actually committed to the database. Any other time, it is literally just reducing the transaction count number by 1.
You can always use the handy @@TRANCOUNT system function to see how many open transactions are in your connection. For example, let’s start two transactions and see what the @@TRANCOUNT value is:
Now we’ll perform our DELETE statement and a COMMIT, then see what the @@TRANCOUNT value is (I had to do a ROLLBACK before executing this code to start from scratch again, fyi):
So again folks, the COMMIT operation changed the transaction count from 2 to 1, which doesn’t do squat. It literally just changed that number from 2 to 1 and did nothing else.
If we ran another COMMIT operation now, then yes, it would change the transaction count value from 1 to 0 which would actually commit the changes to the database. But if we do a ROLLBACK, the changes are undone and the content in our Orders tables is still intact (again, I did a ROLLBACK before running this code to start again from scratch).
Superb!
You can’t use transaction names to roll back to an inner transaction (because there IS NO INNER TRANSACTION!)
Take a look at this code that assigned names to our outer and “inner” transactions:
The error message says “Cannot roll back InnerTran. No transaction or savepoint of that name was found.“.
I received this error message recently and was thoroughly confused. In my mind’s eye, there is clearly a transaction called “InnerTran“, so why is SQL Server telling me there isn’t?
BECAUSE THERE ISN’T!
The only transaction in SQL’s mind’s eye is the transaction named “OuterTran“.
If we wanted to do something like this, where we rolled back a portion of a transaction, we would actually need to create a savepoint, like this:
This code outlines that we want to roll back to a certain savepoint within the transaction. Very clever!
Next Steps:
Leave a comment if you found this quick tutorial helpful!
Everything discussed in this tutorial can be found in the following FREE Ebook:
FREE Ebook on SQL Server Transactions!
This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!
If you need a complete rundown on transactions, you should read the following beginner-friendly introduction to transactions in SQL Server:
SQL Server Transactions: An Introduction for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
