What is a nested transaction?



By:

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.


Last Update: 3/25/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools