Lesson 5	Nested transactions
Objective	Define and identify nested transactions in SQL Server.

Defining and Identifying Nested Transactions in SQL Server

A nested transaction occurs when code issues BEGIN TRANSACTION while a transaction is already active in the same session. This situation is common in real-world SQL Server systems because application code frequently calls stored procedures, and stored procedures often call other stored procedures. If multiple layers of code attempt to “own” the transaction boundary, nesting appears—sometimes intentionally, and often accidentally.

The important design point is this: SQL Server supports nested BEGIN TRAN statements, but it does not implement “true” independent nested transactions. Instead, SQL Server uses a single transaction scope per session and tracks the nesting depth using @@TRANCOUNT.

What SQL Server Means by “Nested”

In SQL Server, each BEGIN TRANSACTION increments @@TRANCOUNT. Each COMMIT decrements @@TRANCOUNT. The work is not fully committed to the database until the outermost transaction commits (when @@TRANCOUNT returns to 0).

Nested transaction behavior is only meaningful with explicit transactions. Autocommit mode commits each statement automatically, and implicit transaction mode changes how transactions are started, but it does not change the core rule that SQL Server maintains a single effective transaction scope per session.

How to Identify Nested Transactions

The most direct way to identify nesting is to check @@TRANCOUNT:

@@TRANCOUNT = 0 means no active transaction in the session.
@@TRANCOUNT = 1 means one active explicit transaction.
@@TRANCOUNT > 1 means nested BEGIN TRAN statements have been issued.


BEGIN TRAN;                -- @@TRANCOUNT becomes 1
PRINT @@TRANCOUNT;

BEGIN TRAN;                -- @@TRANCOUNT becomes 2 (nested BEGIN)
PRINT @@TRANCOUNT;

COMMIT TRAN;               -- @@TRANCOUNT becomes 1 (not fully committed yet)
PRINT @@TRANCOUNT;

COMMIT TRAN;               -- @@TRANCOUNT becomes 0 (transaction truly ends)
PRINT @@TRANCOUNT;

Commit and Rollback Behavior

Nested transaction semantics are easiest to misunderstand at commit/rollback time:

COMMIT inside a nested level: reduces @@TRANCOUNT, but does not make changes permanent unless the outermost transaction also commits.
ROLLBACK without a savepoint: rolls back the entire transaction scope and resets @@TRANCOUNT to 0, discarding all work—no matter how deeply nested the code is.

This is why nested transactions are often a source of production defects: an inner procedure may “commit” and assume the work is permanent, but an outer procedure can still roll back everything. Conversely, a rollback in an inner procedure can erase changes the outer procedure intended to keep.

Why Nested Transactions Occur in Stored Procedures

Nesting is frequently unplanned. You might call a stored procedure written by someone else that opens and closes a transaction internally. If your caller is already in a transaction, you now have a nested transaction situation, even if the developer did not design it intentionally.

Consider this common structure:

Stored Procedure Name	Transaction Within Stored Procedure
SP1	BEGIN, COMMIT/ROLLBACK
SP2	BEGIN, COMMIT/ROLLBACK

If SP1 calls SP2 and both procedures start and end transactions, SQL Server will see a sequence like:

SP1: BEGIN TRANSACTION
SP2: BEGIN TRANSACTION
SP2: COMMIT TRANSACTION (decrements @@TRANCOUNT)
SP1: COMMIT TRANSACTION (final commit if no rollback occurred)

Even though SP2 executes a COMMIT, its commit does not make the work permanent if SP1 later performs a rollback. This is the practical meaning of “SQL Server does not have true independent nested transactions.”

Savepoints: Granular Rollback Within a Transaction

When you need finer control than “commit everything” or “rollback everything,” SQL Server provides savepoints using SAVE TRANSACTION. A savepoint marks a point within a transaction that you can roll back to without rolling back the entire transaction.


BEGIN TRAN;

SAVE TRAN SavePointName;

-- Do some work here
-- If something goes wrong, roll back only to the savepoint:
ROLLBACK TRAN SavePointName;

-- Continue with alternative logic, then finish:
COMMIT TRAN;

Savepoints are widely used in modern stored procedure patterns to provide partial undo while still allowing the outer transaction to continue. They are also a practical way to write “transaction-safe” procedures that can run both inside and outside an existing transaction.

Modern Implementation Guidance

In current SQL Server development, the most reliable approach is to establish a clear transaction ownership rule:

Application-owned transactions: the application starts the transaction and stored procedures assume they are participating.
Procedure-owned transactions: a procedure starts a transaction only if one is not already active, and uses savepoints if needed.

In later lessons, you will connect nested transaction behavior to locking and isolation levels. Nested transactions can keep transactions open longer than expected, which can increase blocking and deadlock risk if transaction scope is not controlled carefully.

Committed Transactions

Even with nested BEGIN TRAN statements, only the outermost commit ends the transaction. If an inner procedure “commits” but the outer procedure rolls back, the entire unit of work is undone. This is a core principle you must remember when troubleshooting “my procedure committed but the data vanished” scenarios.

The next lesson introduces locking, which explains what SQL Server must do internally to isolate concurrent work while transactions remain open.

Transaction Isolation - Quiz

Before moving on, use the quiz link below to check your understanding of nested transactions and how they behave in SQL Server.
Transaction Isolation - Quiz