What is a nested transaction?

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.