Can You Nest Transactions in SQL Server?


To find out, let’s set up a simple status log table:

And then let’s try a two-part transaction:

Right now, SQL Server shows that I have 2 open transactions:

What Happens If I Roll Back?

But what does “2 open transactions” mean, really? If I do a rollback, what gets rolled back? Let’s find out:

The results:

Both of our transactions were rolled back, and there’s nothing left in the table.

Let’s Try It Again, but Commit This Time

Roll through the same setup code, but then commit:

The results are a little weird:

Only one transaction is shown as open – and right now, both rows are in the results table. If we roll back now, what happens?

Even though we said we “committed” our inner transaction, it doesn’t matter – BOTH of our transactions got rolled back.

You Can’t Really Nest Transactions Like This.

Think of @@TRANCOUNT as the number of times remaining that you either need to commit or roll back. If you open a bunch of nested transactions in a row, it’s up to you to commit every single one of ’em. If ANY of them are rolled back, EVERYTHING is rolled back.

This is particularly troublesome if you try to break up locking by using a bunch of little transactions wrapped in one big outer transaction. I recently had a client who thought they could:

  1. Start an outer transaction for a business process
  2. Start an inner transaction, acquire locks on OrderHeader, make changes, commit, and release the OrderHeader locks when the transaction committed
  3. Go on to another inner transaction, acquiring other locks on OrderDetails, while other processes were able to work lock-free on the OrderHeader table because step 2’s locks were released

But as you can see here, even when you commit one part of a transaction, SQL Server still isn’t quite done with it. It has to maintain those locks because if any of your open transactions are rolled back, SQL Server’s gonna roll back everything you did – even the parts you thought were finished.

Update: a couple of commenters have pointed out SAVE TRANSACTION, and I gotta say I’m not a fan of that because of the complexity, especially around lock escalation. If you choose to use that feature, read the documentation really carefully, especially around lock escalation. Saved transactions don’t release the locks once they’ve been escalated, and if you’ve been through my Mastering classes, you know how easy it is to hit lock escalation.

Previous Post
Find 40 Problems in This Stored Procedure.
Next Post
I’m Coming to Gothenburg and Oslo This Fall!

5 Comments. Leave new

  • If you don’t worry about locks, and have to “rollback” part of transaction, you can use “SAVE TRANSACTION” with named savepoints or named trans. But this is truly the gate to hell. If it will be accurately implemented, all your stored proc become unreadable with tons on “if @@trancount” blocks and dozens lines of code in it.

    • Handling nested trans with SAVE TRAN @SavePoint is about 20 lines total of boilerplate code instead of 5 not doing it (i.e. just BEGIN TRAN/COMMIT/ROLLBACK) so we do this on all our stored procedures which modify data in a transaction out of habit — not that it’s super useful and our business logic relies on this.

      • Great point. I’ve added an update to the bottom of the post talking about my feelings about SAVE TRAN. (I hate that thing.)

        • I never new that ROLLBACK TRAN @SavePoint actually released *any* of the locks acquired but blindly assumed it behaves like nested COMMIT which holds everything intact so there is nothing to complain on my part if ROLLBACK at least tries to drop non-escalated locks — nice to know it does this!

  • Jonas Gunnarsson
    August 1, 2023 4:26 pm

    Prefer starting and committing transactions at a _single_ level, and making transaction handling part of the application’s design.

    If a procedure is called, with an open transaction, you need to save a checkpoint, to handle local commit/rollback.

    declare @Independent bit = case when (@@tranCount = 0) then 1 else 0 end; -- 0 = called with open transaction
    if @Independent = 1
    begin transaction;
    save transaction MySavePoint;

    /!\ Never use the transaction name on the rollback statement, as it leaves the code with a potential hidden bug.

    The with mark option causes the transaction name to be placed in the transaction log. Can be useful when restoring the database.

    begin transaction CandidateDelete with mark N'Deleting a Job Candidate';

    Also, always use begin try.., when using transactions, we don’t want them hanging around.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.