In a recent Usergroup meet that I attended someone asked me this simple yet interesting question about how transactions work and is there any concept like nested transactions inside SQL Server. Though I have talked about this in the past, thought it would be worth revisiting this concept to drive home some learnings again to readers who are new to SQL Server. Let us learn this using the script code as shown below:
-- Create our database for testing
CREATE DATABASE TransactionsDemo;
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
SET NOCOUNT ON;
Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.
-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
INSERT INTO tbl_SQLAuth VALUES (1);
-- Create an explicit so called nested transaction
BEGIN TRAN InnerTransaction;
INSERT INTO tbl_SQLAuth VALUES (2);
-- Look at the trancount?
In the example above we started using an explicit transaction called as “OuterTransaction” and then inserted values post which we created yet another transaction called as “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now. Now that we know the transaction count is 2, let us try to rollback the inner transaction alone.
-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
Msg 6401, Level 16, State 1, Line 33
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
Now this is interesting to see the error, we have explicitly created a transaction called as “InnerTransaction” in our code and it is getting ignored.
ROLLBACK TRAN OuterTransaction;
Or the below command works:
And it is so strange that this above commands worked. If you try to query the table, it will have no rows.
-- What hapenned?
SELECT COUNT(*) FROM tbl_SQLAuth;
This is an important learning one needs to know when working with ROLLBACK command inside SQL Server. There is nothing like a nested transaction perse inside SQL Server. Go ahead and do the clean up.
-- Cleanup time
DROP DATABASE TransactionsDemo;
I am curious to know from developers out there, have you ever faced this situation in your environment and learnt it the hard way? If you can share your learning, it would be great.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Dear Pinal Sir,
Don’t know exact behavior behind this but as far as I know about Transaction I want to share my view, please correct me if I am wrong there.
In Transaction we can have multiple Nested Transaction.
We can have multiple Commit for all Nested Transaction.
But we cant have Multiple Rollback for all Transaction.
Which indirectly suggest that Rollback is only for Main Parent Transaction only.
So we can not use Nested Transaction Name with Rollback which we can use with Commit.
Thanks & Regards,
@Naresh – Good answer.
This is only one of the weird anomalies with transaction management in SQL Server. Although some would claim this works as advertised since the documented approach to rolling back only the nested transaction is to use a savepoint. This is less-than-intuitive.
Some other transaction weirdness that can bite you:
–if you name a transaction and roll it back then the name is case sensitive, regardless of db collation. But if you name a transaction and commit the transaction you can use any name you want.
–table variables (really any variables) don’t participate in transaction control.
–and then there are implicit transactions, which is the “default” for most java/jdbc implementations. There are so many exceptions to the rules that I don’t even want to list them. My advice is to avoid them on greenfield projects.
I did a presentation once on this:
Thanks for sharing your thoughts Dave Wentzel!
Using savepoint, we can go for rolling back particular part of transaction.
I’m try to commit the InnerTransaction and rollback the OuterTransaction but can’t do that …. what should I try.