MS SQL Server : Nested Transaction and Partial Rollback of Transaction

Couple of days back, one of my colleague came to me and asked about partial rollback of a transaction.He was trying to do with nested transactions and it was throwing the  following error.

Msg 6401, Level 16, State 1, Line 1

Cannot roll back SaveTran. No transaction or savepoint of that name was found.

In this post let us go through the nested transactions (named transactions) and how to do partial rollback of transactions.

Nested Transactions

SQL server will allow you to start transaction inside the transaction which is called as nested transaction . Nested  transaction will allow to commit transaction individually but  will not allow to rollback individual transactions.In fact nested transaction is a myth in SQL server. Let us see below sample.

CREATE TABLE NestedTransaction (  Id INT)
GO
BEGIN TRAN OuterTxn
INSERT INTO NestedTransaction  VALUES(1)
BEGIN TRAN InnerTxn
INSERT INTO NestedTransaction  VALUES(1)
ROLLBACK TRAN InnerTxn
SELECT @@TRANCOUNT

 

Above script will throw an error while trying to rollback the inner transaction and the transaction count is two which means that there are two open transactions in the current session. Try to execute the below two lines 

ROLLBACK TRAN OuterTxn
GO
SELECT @@TRANCOUNT

This will works perfectly and transaction count will be zero now. In short SQL server will allow you to rollback only the outer transaction and rollback of outer transaction will rollback all nested transactions and hence making the transaction count to zero.Let us see how commit work in this scenario.

BEGIN TRAN OuterTxn
INSERT INTO NestedTransaction  VALUES(1)
BEGIN TRAN InnerTxn
SELECT @@TRANCOUNT 
INSERT INTO NestedTransaction  VALUES(2)
COMMIT TRAN InnerTxn
SELECT @@TRANCOUNT
ROLLBACK

This is more interesting. In the 4th line we can see that there are two open transaction and after that we are inserting a value 2 to our table. After committing the inner transaction, the transaction count reduced to one. Which says that the inner transaction is committed, but if you do a select query on our table after executing the last rollback statement ,there will not be any record in the table. In fact, there is no effect in commiting the inner transaction , but to commit nested transaction , we have to commit all nested transactions individually. In SQL server , inner transaction does not play any role. All locking of object also will be taken care  by the outer transaction. let us see below example.

CREATE TABLE NestedTransaction_1 ( Id INT)
GO
CREATE TABLE NestedTransaction_2 (   Id INT)CREATE TABLE NestedTransaction_3 (   Id INT)
GO
BEGIN TRAN OuterTxn
INSERT INTO NestedTransaction_1  VALUES(1)
BEGIN TRAN InnerTxn_1
INSERT INTO NestedTransaction_2  VALUES(1)
BEGIN TRAN InnerTxn_2
INSERT INTO NestedTransaction_3  VALUES(1)



Logically the locks on each table should be held by respective transactions but if you look into the TransactionName  column  in the output of  this query (Query to find the locking info), it will be outer transaction.  

if you ask me, what is the use of a nested transactions, I do not have a definitive answer.

Partial Rollback of Transactions

Partial rollback of transaction is possible by setting a save point inside a transaction using the Save Transaction command. Please find the sample script below.


CREATE TABLE PartialTxn( Name CHAR(10))
GO--Transaction Starting hereBEGIN TRAN
INSERT INTO PartialTxn VALUES('James')--Setting the savepointSAVE TRANSACTION Txn1INSERT INTO PartialTxn VALUES('George')SELECT * FROM PartialTxn ROLLBACK TRANSACTION Txn1COMMIT
SELECT * FROM PartialTxn

You can see that only one record exists in the table after the final commit. This can be implemented inside the procedure also. In case of multiple procedures are part of single transaction , you can rollback only the failed(due to some validation error)  procedure and can still commit the remaining data.   



If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba