By: Brady Upton
A nested transaction is a transaction that is created inside another transaction….Huh? It sounds confusing, but it’s not. A nested transaction’s purpose is to support transactions in stored procedures that can be called from a process already in a transaction or from a process that has no active transaction.
See below for an example of a nested transaction:
BEGIN TRAN Tran1
GO
BEGIN TRAN Nested Tran
GO
INSERT INTO Table1 DEFAULT Values
GO 10
COMMIT TRAN Nested Tran
SELECT * FROM Table1
I get back 10 results. So far, so good. When I rollback the first transaction….
ROLLBACK TRAN Tran1
The 10 transactions are gone. Why? The nested transaction is based on the action of the outermost transaction. Since I rolled back the outer transaction, the entire transaction is rolled back no matter what I did in between. If I would have committed Tran 1, then my nested transaction would have also committed.
