Nested Transactions in SQL Server: The Feature That Isn't

Every developer who writes a stored procedure that calls another stored procedure eventually asks the same question: what happens if both of them open a transaction? The natural assumption is that the inner one is a smaller unit inside the outer one, that committing it saves its work, and that rolling it back undoes only its own changes while the outer transaction carries on.

None of that is true. SQL Server parses nested BEGIN TRANSACTION statements, accepts them without complaint, and then does something completely different with them. There is only ever one transaction per session. Everything after the first BEGIN TRANSACTION is bookkeeping.

One Transaction, One Counter

The only thing a second BEGIN TRANSACTION does is increment @@TRANCOUNT. No new transaction is started, no separate scope is created, no isolation boundary appears. The engine still has a single transaction with a single log stream, a single transaction ID and a single set of locks.

Figure 1 · Syntax versus engine

You can watch the counter directly, and it is the single most useful diagnostic in this whole topic:

BEGIN TRANSACTION;
    SELECT @@TRANCOUNT AS depth;           -- 1
    BEGIN TRANSACTION;
        SELECT @@TRANCOUNT AS depth;       -- 2
        BEGIN TRANSACTION Inner3;
            SELECT @@TRANCOUNT AS depth;   -- 3

Commits Count Down, Rollbacks Go Straight to Zero

This is the asymmetry that breaks code. COMMIT TRANSACTION and ROLLBACK TRANSACTION are not opposites here. They do not even operate on the same thing.

• COMMIT while @@TRANCOUNT is greater than 1: the counter is decremented. Nothing is written, nothing becomes durable, no lock is released. The statement is very nearly a no-op.
• COMMIT while @@TRANCOUNT is exactly 1: this is the real commit. The commit log record is written and hardened, the locks are released, the work becomes visible to everyone else.
• ROLLBACK without a savepoint name: the entire transaction is undone and @@TRANCOUNT is set to 0, from whatever depth you were at. It never steps down one level.

Figure 2 · The @@TRANCOUNT ladder

Put those two rules together and the classic bug writes itself. A procedure that opens its own transaction, hits an error and issues a bare ROLLBACK TRANSACTION does not clean up after itself. It destroys the transaction of whoever called it, including work that had nothing to do with it, and it does so silently until the caller tries to commit.

Naming the Inner Transaction Changes Nothing

Because you can write BEGIN TRANSACTION Inner3, it is tempting to believe that ROLLBACK TRANSACTION Inner3 will undo only that part. It will not. The name on an inner BEGIN is accepted and then ignored. Only the outermost transaction name is remembered, so only that name can be rolled back:

BEGIN TRANSACTION OuterTran;
    BEGIN TRANSACTION InnerTran;
        ROLLBACK TRANSACTION InnerTran;

/* Msg 6401, Level 16, State 1
   Cannot roll back InnerTran. No transaction or savepoint of that name was found. */

The same holds for COMMIT TRANSACTION SomeName. The name is parsed, checked for syntax and discarded. It has no influence on which level is committed.

The Four Errors You Will Actually See

When transaction depth gets out of step, SQL Server reports it with a small and very recognisable set of messages. Learning them by number saves a lot of debugging:

Msg	Text	What actually happened
266	Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.	A procedure returned with a different @@TRANCOUNT than it was called with. Almost always: it rolled back a transaction it did not start.
3902	The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.	You committed at depth 0. Something below you already rolled everything back, or you have one COMMIT too many.
3903	The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.	The same situation seen from the rollback side. Typical in a CATCH block that rolls back without checking @@TRANCOUNT first.
6401	Cannot roll back name. No transaction or savepoint of that name was found.	You tried to roll back an inner transaction by name, or a savepoint that was never created or was already passed.

Savepoints: The Partial Rollback That Really Exists

What people want when they reach for nested transactions is a partial undo. SQL Server has that feature, it is just not spelled BEGIN TRANSACTION. It is spelled SAVE TRANSACTION.

A savepoint is a named marker inside the current transaction. ROLLBACK TRANSACTION savepoint_name undoes everything done after that marker and leaves the rest of the transaction alive and still open. Crucially, a savepoint does not touch @@TRANCOUNT, neither when it is created nor when it is rolled back to.

Figure 3 · What a savepoint rollback actually undoes

In code the difference is immediately visible, because the counter never moves:

BEGIN TRANSACTION;                      -- @@TRANCOUNT = 1
    INSERT dbo.Orders (CustomerId) VALUES (17);
    DECLARE @OrderId INT = SCOPE_IDENTITY();

    SAVE TRANSACTION BeforeLines;       -- @@TRANCOUNT = 1, still

    INSERT dbo.OrderLine (OrderId, Sku, Qty) VALUES (@OrderId, 'X-1', 5);

    ROLLBACK TRANSACTION BeforeLines;   -- the line is gone, the order is not
                                        -- @@TRANCOUNT = 1, still

COMMIT TRANSACTION;                     -- the order is committed, without its line

Three limits are worth knowing before you build on this:

• Locks are not released. Rolling back to a savepoint undoes data changes, not lock acquisition. Everything the transaction has locked since BEGIN stays locked until the transaction really ends. Savepoints reduce how much work you undo, never how much you block.
• Names are limited and reusable. A savepoint name is at most 32 characters. Duplicate names are allowed, and a rollback goes to the most recent savepoint carrying that name. Useful in recursive procedures, dangerous if you did not intend it.
• Not available everywhere. SAVE TRANSACTION is not allowed inside a distributed transaction, and it is useless once the transaction is doomed. That second point deserves its own section.

Doomed Transactions and XACT_STATE()

Some errors do not merely fail a statement, they poison the transaction. After such an error the transaction is still open but can no longer be committed and can no longer be rolled back to a savepoint. The only legal move left is a full rollback. SQL Server calls this state uncommittable, everyone else calls it doomed.

XACT_STATE() is how you ask:

Value	Meaning	What you may do
1	An open transaction that is still healthy	Commit, full rollback, or rollback to a savepoint
0	No open transaction on this session	Nothing. A COMMIT here raises 3902, a ROLLBACK raises 3903
-1	Open but uncommittable (doomed)	Full ROLLBACK TRANSACTION only. A savepoint rollback raises 3931

Note what @@TRANCOUNT does not tell you here. It reports depth, not health. A doomed transaction at depth 3 still shows @@TRANCOUNT = 3, which looks entirely normal right up to the moment the commit fails.

The Procedure Pattern That Survives Both Cases

A stored procedure that changes data cannot know whether it was called standalone or from inside someone else's transaction. That is the entire problem, and it has one well-established answer: read @@TRANCOUNT on entry and behave according to what you find.

If the counter is 0, you own the transaction: begin it and commit it. If the counter is greater than 0, the caller owns it: do not begin, do not commit, set a savepoint instead, and on error undo only back to that savepoint. The procedure must return with exactly the @@TRANCOUNT it was called with, otherwise error 266 is waiting on the way out.

CREATE OR ALTER PROCEDURE dbo.usp_TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(19,4)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @OuterTran INT = @@TRANCOUNT;

    IF @OuterTran > 0
        SAVE TRANSACTION TransferFunds;   -- the caller owns the transaction
    ELSE
        BEGIN TRANSACTION;                -- we own it

    BEGIN TRY

        UPDATE dbo.Account
           SET Balance = Balance - @Amount
         WHERE AccountId = @FromAccount;

        IF @@ROWCOUNT <> 1
            THROW 50001, 'Source account not found.', 1;

        UPDATE dbo.Account
           SET Balance = Balance + @Amount
         WHERE AccountId = @ToAccount;

        IF @@ROWCOUNT <> 1
            THROW 50002, 'Target account not found.', 1;

        IF @OuterTran = 0
            COMMIT TRANSACTION;           -- commit only what we started

    END TRY
    BEGIN CATCH

        IF XACT_STATE() = -1
            ROLLBACK TRANSACTION;                 -- doomed, nothing else is legal
        ELSE IF XACT_STATE() = 1 AND @OuterTran = 0
            ROLLBACK TRANSACTION;                 -- our transaction, ours to end
        ELSE IF XACT_STATE() = 1 AND @OuterTran > 0
            ROLLBACK TRANSACTION TransferFunds;   -- the caller's transaction, undo our part only

        THROW;   -- the caller decides what happens next

    END CATCH
END

Read that CATCH block as a decision, not as a sequence. There are exactly four situations it can be in, and only one of them allows the savepoint:

Figure 4 · What a CATCH block is allowed to do

Triggers Are Always Nested

Inside a trigger, @@TRANCOUNT is never 0. Every DML statement runs in a transaction, either the explicit one you opened or the implicit one SQL Server created for the statement, so trigger code is by definition running at depth 1 or deeper. That makes ROLLBACK TRANSACTION inside a trigger the most destructive statement in the whole topic:

CREATE OR ALTER TRIGGER dbo.trg_Account_NoNegative
ON dbo.Account
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM inserted WHERE Balance < 0)
    BEGIN
        ROLLBACK TRANSACTION;   -- takes the caller's entire transaction with it
        THROW 50010, 'Balance would go negative.', 1;
    END
END

That rollback does not undo just the offending UPDATE. It ends the whole transaction and aborts the rest of the batch, so statements queued after the UPDATE never run. A trigger that only rolls back reports it as Msg 3609, The transaction ended in the trigger. The batch has been aborted. Add a THROW after the rollback, as above, and the caller sees your error number instead, which hides the fact that a transaction was destroyed behind a message about a negative balance.

In almost every case the better trigger raises the error and lets the caller decide:

    IF EXISTS (SELECT 1 FROM inserted WHERE Balance < 0)
        THROW 50010, 'Balance would go negative.', 1;

With SET XACT_ABORT ON in the calling code, or a CATCH block that handles the failure, the outcome is the same rollback, but it happens where someone is in a position to log it, compensate for it and report it. And if the rule really is "this column may never go negative", a CHECK constraint enforces it for a fraction of the cost and none of this complexity.

What an Open Transaction Costs While You Are Nesting

Because the inner commits are bookkeeping, the transaction stays open the whole time. Everything that depends on transaction lifetime therefore depends on the outermost commit, not on the inner ones:

• Locks are held. Every lock taken at any depth is held until the final commit or rollback. An inner COMMIT releases nothing, so a long call chain with many apparently committed steps blocks exactly as long as one giant transaction, because that is what it is.
• The log cannot be truncated. The active portion of the log starts at the oldest open transaction. One forgotten depth-1 transaction pins the log indefinitely, and log_reuse_wait_desc in sys.databases reports ACTIVE_TRANSACTION while the file grows.
• The version store keeps growing. Under snapshot isolation or Read Committed Snapshot, row versions cannot be cleaned up while an older transaction might still need to see them. That is how one idle session fills TempDB.

The first point is easy to see for yourself. Count the locks a session holds before and after an inner commit:

BEGIN TRANSACTION;
    UPDATE dbo.Account SET Balance = 123 WHERE AccountId = 2;
    BEGIN TRANSACTION;
        UPDATE dbo.Account SET Balance = 124 WHERE AccountId = 1;

        SELECT COUNT(*) FROM sys.dm_tran_locks
         WHERE request_session_id = @@SPID
           AND resource_type <> 'DATABASE';        -- 4

    COMMIT TRANSACTION;                             -- the "inner" commit

        SELECT COUNT(*) FROM sys.dm_tran_locks
         WHERE request_session_id = @@SPID
           AND resource_type <> 'DATABASE';        -- 4, unchanged

COMMIT TRANSACTION;

        SELECT COUNT(*) FROM sys.dm_tran_locks
         WHERE request_session_id = @@SPID
           AND resource_type <> 'DATABASE';        -- 0

Four locks before the inner commit, four after it, zero after the outer one. The inner COMMIT did not release a single lock, because there was nothing there to release.

The distinctive failure mode here is an application that sent a BEGIN TRANSACTION, then a second one from a nested method, and then died or wandered off. The session sits idle, @@TRANCOUNT is 2, nothing is executing and nothing is being released. From the outside it looks like a healthy sleeping connection.

This query finds those sessions, including their transaction depth, which is the outside view of another session's @@TRANCOUNT:

SELECT  s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.open_transaction_count,
        s.status                                AS session_status,
        t.transaction_begin_time,
        DATEDIFF(SECOND, t.transaction_begin_time,
                 SYSDATETIME())                 AS tran_age_sec,
        c.last_read,
        c.last_write
FROM        sys.dm_tran_session_transactions st
JOIN        sys.dm_tran_active_transactions   t  ON t.transaction_id = st.transaction_id
JOIN        sys.dm_exec_sessions              s  ON s.session_id     = st.session_id
LEFT JOIN   sys.dm_exec_connections           c  ON c.session_id     = st.session_id
WHERE       s.is_user_process = 1
ORDER BY    t.transaction_begin_time;

Two numbers decide what you are looking at. An open_transaction_count above 1 tells you the session nested its transactions. A tran_age_sec in the thousands next to a last_read that is just as old tells you nobody is coming back to finish it. DBCC OPENTRAN gives the short version for a single database, but it will not show you the depth.

What the Client Side Does With All This

Application frameworks do not rescue you from any of it, they mostly refuse to play along. In ADO.NET, calling BeginTransaction() a second time on the same SqlConnection does not nest anything, it throws: SqlConnection does not support parallel transactions. TransactionScope with the default Required option does not create an inner transaction either, it enlists into the ambient one that already exists, which is the correct behaviour and also the reason a rollback in an inner scope aborts the entire outer operation.

The practical consequence is the same rule as on the T-SQL side, expressed one layer up. One logical operation, one transaction, opened and closed at one place in the code. Everything below that layer participates, nothing below it commits.

Q&A

The Bottom Line

SQL Server has one transaction per session and a counter that tracks how many times you asked for one. Commits walk that counter down and only the last one means anything. A rollback ignores the counter entirely and ends everything. Savepoints are the only real partial undo, they do not release locks, and they stop working the moment the transaction is doomed.

Everything else follows from those four facts. Read @@TRANCOUNT when your procedure starts, never issue a bare ROLLBACK for a transaction you did not open, keep transaction control at the top of the call chain, and treat error 266 as a message that something below you already broke the rules.