Rollback nested transactions in SQL Server

To test ROLLBACK of nested transactions we will first create a table:

CREATE TABLE Table_rollback_test (Test_values nchar(10))
GO

Run this code to test if any values will be written to the table if the outermost transaction is rolled back:

BEGIN TRAN
INSERT INTO Table_rollback_test (Test_values)
VALUES ('AAA')
BEGIN TRAN
INSERT INTO Table_rollback_test (Test_values)
VALUES ('BBB')
COMMIT TRAN
ROLLBACK TRAN

As we can see there are no new data in the table:

More information regarding commit and rolling back nested transactions can be found here:

http://msdn.microsoft.com/en-us/library/ms190295.aspx

"When used in nested transactions, commits of the inner transactions do not free resources or make their modifications permanent. The data modifications are made permanent and resources freed only when the outer transaction is committed. Each COMMIT TRANSACTION issued when @@TRANCOUNT is greater than 1 simply decrements @@TRANCOUNT by 1. When @@TRANCOUNT is finally decremented to 0, the entire outer transaction is committed. Because transaction_name is ignored by the Database Engine, issuing a COMMIT TRANSACTION referencing the name of an outer transaction when there are outstanding inner transactions only decrements @@TRANCOUNT by 1.

Issuing a COMMIT TRANSACTION when @@TRANCOUNT is 0 results in an error; there is no corresponding BEGIN TRANSACTION.

You cannot roll back a transaction after a COMMIT TRANSACTION statement is issued because the data modifications have been made a permanent part of the database.

The Database Engine increments the transaction count within a statement only when the transaction count is 0 at the start of the statement."

...and here:

http://msdn.microsoft.com/en-us/library/ms181299.aspx

"ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT."

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">