Difference Between Nested Transaction and Multi Transaction in SQL Server

Nested Transactions are logical levels within a single physical transaction where only the outermost COMMIT actually commits and any ROLLBACK rolls back the entire transaction. Multiple Transactions are independent transactions where each COMMIT or ROLLBACK affects only its own transaction scope.

Feature	Nested Transaction	Multiple Transactions
Definition	Transaction inside another transaction	Separate independent transactions
BEGIN TRAN Count	Multiple BEGIN TRAN statements before final COMMIT	Each transaction starts and ends independently
Physical Transaction	One	Multiple
Logical Transaction Levels	Multiple	One per transaction
@@TRANCOUNT	Increases with each BEGIN TRAN	Resets to 0 after each COMMIT
Independent Commit	❌ No	✅ Yes
Independent Rollback	❌ No	✅ Yes
Inner COMMIT Commits Data?	❌ No	✅ Yes
Rollback Effect	Entire transaction rolls back	Only current transaction rolls back
Transaction Log BEGIN Record	One physical BEGIN	One BEGIN per transaction
Transaction Log COMMIT Record	One physical COMMIT	One COMMIT per transaction
Lock Release	Only after outer COMMIT	After each COMMIT
Blocking Risk	Higher	Lower
Production Usage	Rare	Very Common
Best Use Case	Stored procedure call hierarchy	Normal OLTP processing

Difference between save transaction and nested transaction

SAVEPOINT creates a rollback checkpoint within a transaction and allows partial rollback, whereas a Nested Transaction only increases @@TRANCOUNT and does not provide independent commit or rollback capability. A rollback in a nested transaction rolls back the entire transaction, while a rollback to a SAVEPOINT affects only the work performed after that checkpoint.

Feature	SAVEPOINT (SAVE TRANSACTION)	Nested Transaction
Purpose	Create a rollback checkpoint	Create logical transaction levels
Syntax	SAVE TRANSACTION SP1	BEGIN TRAN inside another BEGIN TRAN
Creates New Transaction?	❌ No	❌ No (logical only)
Increases @@TRANCOUNT?	❌ No	✅ Yes
Supports Partial Rollback?	✅ Yes	❌ No
Can Rollback Specific Work?	✅ Yes	❌ No
Can Rollback Inner Transaction Only?	❌ No	❌ No
Transaction Log Entry	Savepoint marker	New BEGIN TRAN record count
Commit Inner Unit Separately?	❌ No	❌ No
Releases Locks?	❌ No	❌ No
Ends Transaction?	❌ No	❌ No
Commonly Used In Production?	✅ Very Common	⚠️ Less Common
Main Use Case	Error recovery	Modular code structure
Survives Until Commit?	✅ Yes	✅ Yes
Works With TRY/CATCH?	✅ Excellent	⚠️ Limited benefit

 

Nested Transaction in SQL Server

A Nested Transaction in SQL Server occurs when a BEGIN TRAN statement is executed inside another active transaction. However, SQL Server does not create independent physical transactions. Instead, it maintains a transaction counter called @@TRANCOUNT. Each BEGIN TRAN increases the count, and each COMMIT decreases it. The actual commit occurs only when @@TRANCOUNT reaches zero. A key interview point is that an inner COMMIT does not permanently save data; it merely decreases the transaction count. Similarly, a ROLLBACK issued at any nested level rolls back the entire transaction and resets @@TRANCOUNT to zero. Therefore, SQL Server nested transactions are logical constructs rather than true independent transactions. For partial rollback requirements, SAVEPOINTs should be used instead of nested transactions.

Syntex

BEGIN TRAN OuterTran

    BEGIN TRAN InnerTran

    COMMIT TRAN InnerTran

COMMIT TRAN OuterTran

This looks like two transactions, but internally SQL Server treats them as ONE Physical Transaction Multiple Logical Levels.  We use nested transactions primarily for modularity, code reusability, and stored procedure independence. They allow multiple stored procedures to participate in the same transaction without knowing whether they were called directly or by another procedure.

Nested transactions are not used for independent commits. 

Nested transactions provide logical transaction boundaries and allow reusable stored procedures to participate in a larger transaction. They help manage transaction ownership and modularize code, but they do not provide independent commits. Only the outermost COMMIT permanently commits the transaction. For partial rollback requirements, SAVEPOINT should be used instead.

SAVEPOINT in SQL Server Transaction

A SAVEPOINT in SQL Server is a named checkpoint within a transaction that allows partial rollback without undoing the entire transaction. It is created using the SAVE TRANSACTION statement and can be referenced later using ROLLBACK TRANSACTION SavePointName. SAVEPOINTs are useful in complex business processes where certain steps can be undone while preserving earlier successful work. Unlike COMMIT, a SAVEPOINT does not make data permanent, release locks, or increase @@TRANCOUNT. It simply marks a position in the transaction log to which SQL Server can roll back if needed. In enterprise applications, SAVEPOINTs are frequently used in ETL jobs, financial transactions, and stored procedures to implement granular error recovery and partial rollback strategies.

Suppose we are writing notes in a notebook before an exam. Scenario Without SAVEPOINT we write:

Ø  Chapter 1 Notes

Ø  Chapter 2 Notes

Ø  Chapter 3 Notes

And Suddenly Tea falls on notebook ☕ we lose everything.

Ø  Chapter 1 ❌

Ø  Chapter 2 ❌

Ø  Chapter 3 ❌

This is similar to ROLLBACK TRANSACTION Entire transaction is rolled back.

Scenario With SAVEPOINT

Suppose after every chapter you save a copy.

Ø  Chapter 1 Completed

o   📌 Savepoint 1

Ø  Chapter 2 Completed

o   📌 Savepoint 2

Ø  Chapter 3 Started

Now tea falls on notebook. We don't need to rewrite everything. We restore from Savepoint 2.

Result:

Ø  Chapter 1 ✔

Ø  Chapter 2 ✔

Ø  Chapter 3 ❌

Only the latest work is lost.

Transaction = Writing Notebook SAVEPOINT = Ctrl + S (Checkpoint) ROLLBACK TO SAVEPOINT = Restore Last Saved Version ROLLBACK TRANSACTION = Throw Away Entire Notebook

 Let’s see the demo

Creating a table

IF OBJECT_ID('dbo.Employee') IS NOT NULL     DROP TABLE dbo.Employee; GO CREATE TABLE dbo.Employee (     EmployeeID INT primary key not null,     EmployeeName VARCHAR(50) ); GO

Inserting few records

BEGIN TRAN; INSERT INTO dbo.Employee VALUES (1,'John'); PRINT 'Employee 1 Inserted'; SAVE TRANSACTION SP1; PRINT 'SAVEPOINT SP1 Created'; INSERT INTO dbo.Employee VALUES (2,'David'); INSERT INTO dbo.Employee VALUES (3,'Mike'); PRINT 'Employee 2 and 3 Inserted';  SELECT * FROM dbo.Employee;

Transaction is still open.

Now ROLLBACK TRANSACTION SP1; let see

ROLLBACK TRANSACTION SP1;

SELECT * FROM dbo.Employee;

See here record before save point data is committed. After that it is rollbacked. When we are saying that Savepoint is rollback not yet whole transaction is rollback. Opening the another session and seeing the data.

Table is locked because transaction is not yet completed. Now we can see the uncommitted data.

2 rows already rollback.

Now we are committing the open transaction.

In other session we can see this data

See the example with try… catch.

BEGIN TRY     BEGIN TRAN;     INSERT INTO dbo.Employee VALUES (2,'Bagesh');     SAVE TRANSACTION BeforeRiskyStep;      INSERT INTO dbo.Employee VALUES (2,'Rajesh');     COMMIT; END TRY BEGIN CATCH     PRINT ERROR_MESSAGE();     ROLLBACK TRANSACTION BeforeRiskyStep;     COMMIT; END CATCH;

Here 1 row inserted and one row rollbacked. See the records in the table.

Insert Bagesh           → Success
SAVEPOINT Created     → Success
Duplicate Insert      → Error
Rollback to SAVEPOINT → Executed 

Commit Remaining Work → Success