When I was first learning about transactions in Microsoft SQL Server, there were a few simple rules I had to learn the hard way.
I found myself in a great amount of confusion because I didn’t know these basic rules about transactions. If I can save you from making those same mistakes, my life will be complete.
Are you encountering a strange problem within your transactions, but aren’t sure why? I hope this tutorial solves that problem, and gives you some clarification on how transactions work in SQL Server.
If you need a crash course on what transactions are and how they can help us, take a look at the full beginner-friendly introductory tutorial:
SQL Server Transactions: An Introduction for Beginners
In this tutorial, we will discuss the following 6 rules about transactions:
- There isn’t really the concept of nested transactions in SQL Server
- All COMMIT really does is reduce the number of open transactions by 1
- A transaction isn’t truly committed until the number of open transactions is reduced from 1 to 0
- A ROLLBACK will set the number of open transactions to 0, regardless of what it was before
- When you leave a stored procedure, the number of open transactions needs to be the same as when you entered the stored procedure.
- A ROLLBACK does not undo work that was done to variables
All these rules are outlined in a very helpful 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!
There’s so much juicy information in this post, so let’s do it.
1. There isn’t really the concept of nested transactions in SQL Server
Throughout this tutorial, we’ll use the handy @@TRANCOUNT system function to give us the current number of open transactions in our database. This is something we learned about in our introduction to transactions.
The first rule we need to remember is how there really isn’t the concept of nested transactions in SQL Server. By that, I mean an inner transaction isn’t completely isolated from an outer transaction.
Let me explain.
Let’s say we have the following Books table:
Let’s say we need to insert a new book into this table. The book is “Clean Code” by Robert Martin. Here is the INSERT statement:
Let’s put that INSERT statement within a nested transaction that is 4 levels deep and COMMIT it:
Ok, cool. So we committed the innermost transaction which inserted our new row. That change ought to be durable, right?
(The term ‘durable’ means the change is actually written to the database, and will still exist in the database even after something like a loss of power)
The innermost transaction said “1 row affected“, and we didn’t get any error messages. So the new row should be there, right?
Let’s take a look at the table:
Ok, awesome. It appears to be there.
But don’t forget, WE STILL HAVE 3 OPEN TRANSACTIONS.
Watch what happens when we do a ROLLBACK, then check the content of the table again:
The new row is GONE. But why?
Friends, that innermost transaction wasn’t actually committed to the database. It said it was, and it appeared to be in the table, but it wasn’t actually committed.
BUT WHY? That leads us to our next rule…
2. A transaction isn’t truly committed until the number of open transactions is reduced from 1 to 0
Did our innermost COMMIT change the number of open transactions from 1 to 0?
NOPE.
Take a look at the result again:
Folks, that’s the reason the INSERT wasn’t durable. The COMMIT did not reduce the number of open transactions from 1 to 0.
It reduced the number of open transactions from 4 to 3.
This leads us to our next rule….
3. All COMMIT really does is reduce the number of open transactions by 1
Again, you can see from the results of our query that the innermost COMMIT reduced the @@TRANCOUNT from 4 to 3, and that’s all it did.
Literally, that’s all it did. It didn’t actually commit anything since the number of open transactions at the time was greater than 1.
COMMIT doesn’t really mean anything until @@TRANCOUNT is 1 beforehand.
Folks, this is the crux of why there really isn’t the concept of nested transactions within SQL Server, in the sense that an inner transaction will be isolated from outer transactions. If you COMMIT something in an inner transaction, it won’t be actually committed until the outermost transaction is committed!
Let’s do the INSERT again, but this time let’s actually COMMIT all our outer transactions, too:
So again, since @@TRANCOUNT is now zero, we can be sure that what we see in the table is actually durable now:
Nice.
(By the way, I had to change my output window to be ‘Results to Text‘ in order to see both the Print statement and the query results. The ‘Results to Text‘ button is here:)
4. A ROLLBACK will set the number of open transactions to zero, regardless of what it was before.
ROLLBACK is different from COMMIT in that if you issue a ROLLBACK in an innermost transaction, it sets @@TRANCOUNT to zero. It does NOT simply reduce @@TRANCOUNT by 1.
If you opened 35 transactions, then open a 36th that experiences an error and issues a ROLLBACK, guess what, it just rolled back all 36 of the transactions.
Let’s prove it. Here is a WHILE Loop that opens 36 transactions, then issues a ROLLBACK. After that ROLLBACK occurs, we see the @@TRANCOUNT is back to zero:
This is very important to understand when it comes to creating transactions within stored procedures, for example. If you open a transaction within a stored procedure, then do ROLLBACK in that same stored procedure, understand that the ROLLBACK didn’t just effect the code within your stored procedure. If there were other transactions opened before your stored procedure was called, those other transactions would also be rolled back.
NOT GOOD.
Thankfully, there is a way to make sure ROLLBACKS done in a stored procedure only affect code within that stored procedure. It involves giving that innermost transaction a name.
I have a full tutorial on the topic of creating a transaction within a stored procedure here:
Are you making this ROLLBACK mistake in your stored procedures?
If you’re not sure what a ROLLBACK is in the first place, you should check out the full beginner-friendly tutorial on the topic here:
SQL Server ROLLBACK: Everything you need to know
5. When you leave a stored procedure, the number of open transactions needs to be the same as when you entered the stored procedure.
While we’re on the topic of stored procedures, let’s create a simple example to prove this next rule.
Let’s create a stored procedure that just does a ROLLBACK statement:
Now let’s call that stored procedure from within a transaction and see what happens:
We’ve broken the rule.
Here is that full error message, regarding a mismatch in the transaction count:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
When you leave a stored procedure, the number of open transactions must be the same as it was when you entered the stored procedure.
From the helpful PRINT statements in the stored procedure, we can see the number of transactions when we entered was one. Then a ROLLBACK was issued, which we now know will set the number of open transactions to zero. So when we leave the procedure, we have a different number of open transactions than when we started, which is not allowed.
I’ll also point out how the COMMIT also gave us an error message when it ran. Here’s that full message:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Since @@TRANCOUNT was zero at that point, there simply wasn’t any open transaction to commit. In order for COMMIT to succeed, at least one transaction needs to be open.
6. A ROLLBACK does not undo work that was done to variables
As we have learned, a ROLLBACK will undo everything that was done in the transaction(s) up to that point in the code….
Well, almost everything. The one thing that won’t be undone is work done to variables.
Check this out:
The variable @newVariable was declared and initialized within a transaction that was eventually rolled back. But because of the rule, the work still persists.
Outside the transaction, we recognize the existence of the variable, and also the value of that variable.
Pretty cool, right? This is true for all kinds of variables, including table variables.
Let’s create a quick regular table and populate it with rows using a transaction. Then we’ll roll it back and see the INSERTS were undone:
We don’t see any rows in our CatInfo table because all that work was undone by the ROLLBACK.
Now let’s try again, but change the table to be a table variable:
Well I’ll be darned.
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your 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. Everything discussed in this tutorial can also be found in the Ebook. 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 missed the full introductory tutorial on SQL Server transactions, you should definitely check that out:
SQL Server Transactions: An Introduction for Beginners
Make sure you understand how you should be creating transactions within stored procedures, too. There is a specific format you should follow to make sure you don’t accidentally ROLLBACK someone else’s transaction. Take a look at the article to see what I mean:
Are you making this ROLLBACK mistake in your stored procedures?
If you need to understand what a ROLLBACK is in the first place, check out the full tutorial on the topic here:
SQL Server ROLLBACK: Everything you need to know
If you need a quick rundown on what a stored procedure is, I got you:
Stored Procedures: The Ultimate Guide for Beginners
Stored procedure with parameters: A guide for beginners
Also, here is a link to the official Microsoft documentation on transactions: Transactions (Transact-SQL)
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!
