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)GOBEGIN TRAN OuterTxnINSERT INTO NestedTransaction VALUES(1)BEGIN TRAN InnerTxnINSERT INTO NestedTransaction VALUES(1)ROLLBACK TRAN InnerTxnSELECT @@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 OuterTxnGOSELECT @@TRANCOUNTThis 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 OuterTxnINSERT INTO NestedTransaction VALUES(1)BEGIN TRAN InnerTxnSELECT @@TRANCOUNT
INSERT INTO NestedTransaction VALUES(2)COMMIT TRAN InnerTxnSELECT @@TRANCOUNTROLLBACK
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)GOCREATE TABLE NestedTransaction_2 ( Id INT)CREATE TABLE NestedTransaction_3 ( Id INT)GOBEGIN TRAN OuterTxnINSERT INTO NestedTransaction_1 VALUES(1)BEGIN TRAN InnerTxn_1INSERT INTO NestedTransaction_2 VALUES(1)BEGIN TRAN InnerTxn_2INSERT 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