What is a nested transaction?


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
        BEGIN TRAN Nested Tran
        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.

Comments For This Article

get free sql tips
agree to terms