Pages

Sunday, 20 May 2012

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








12 comments:

  1. There really is no such thing as nested transactions. A session either has a transaction open, or it doesn't. The @@trancount value just keeps track of how many BEGIN TRAN statements have been encountered, but a value of 2 does NOT mean there are 2 transactions.

    The reason for keeping track is that you might have multiple BEGIN TRAN statements if you are in a transaction, and you call a procedure that has its own BEGIN TRAN, and then perhaps that calls another procedure that has its own BEGIN TRAN, etc. The @@trancount value allows SQL Server to know when you have executed the correct number of COMMIT TRAN values.

    And as you've shown, ROLLBACK always sets @@trancount back to 0 and exits out of the transaction, and releases all the locks.

    ~Kalen Delaney

    ReplyDelete
    Replies
    1. Thank you Kalen Delaney for finding time to read this post and for giving a feedback. Now I am more clear on the concept of having multiple begin trans

      Thanks
      Nelson

      Delete
    2. dongtam
      mu moi ra hom nay
      tim phong tro
      http://nhatroso.com/
      nhạc sàn
      tổng đài tư vấn luật
      văn phòng luật hà nội
      tổng đài tư vấn luật
      thành lập công ty trọn gói
      http://we-cooking.com/
      chém gió
      trung tâm tiếng anh



      - Nhạc Thành, ngươi xem biện pháp này có hữu dụng không?

      Yêu Huyên ở sau lưng của Nhạc Thành cất tiếng nói, trong ánh mắt hiện ra vẻ lo lắng.

      - Yên tâm đi, hẳn là không có vấn đề gì.

      Nhạc Thành cất tiếng nói.

      - Thời gian còn sớm, bất quá như vậy cũng đủ rồi, chúng ta chuẩn bị lên đường thôi.

      Nhạc Thành cất tiếng nói.

      - Yêu Huyên, Đại Hắc các ngươi hãy từ trong Hạo Thiên Tháp mà đi ra đi, sau đó ra cho ta, miễn bị người khác phát hiện tung tích.

      Nhạc Thành cất tiếng nói sau đó thủ ấn của hắn đánh ra, Hạo Thiên Tháp xuất hiện trước người của hắn.

      - Xoẹt xoẹt.

      Thủ ấn của Nhạc Thành đánh ra, Yêu Huyên và Đại Hắc tiến nhập vào trong Hạo Thiên Tháp.

      - Ta cũng nên chuẩn bị một chút, miễn cho bị mấy lão gia hỏa kia để ý tới.

      Delete
  2. Thanks for the blog/ post. The nested transaction is very cleverly and clearly explained.

    ReplyDelete
  3. Very nice explanation. Thank you!

    (BTW it seems the last code snippet is missing a few newlines.)

    ReplyDelete
  4. Very simple and clean explaination. Good job!

    ReplyDelete
  5. Nested transactions were really myths for me. Thanks for the clear explanation.
    Fatih Yarbaşı.

    ReplyDelete
  6. If I have one transaction say mainTransaction, within this mainTransaction i have another two transaction say t1 and t2. Now while execution t1 completes successfully and commit statement fires, but while executing t2 some error occurs and rollback statement fires. What happen to t1 and MainTransaction , these will be rollback ? kindly provide answer with example.
    Thank you.

    ReplyDelete
  7. In your last example, leaves an transaction open.

    ReplyDelete
  8. Thanks Nelson John for sharing this article with us I was facing the same error for a very log time and it helps me a lot. I have found another helpful post regarding to the same error. Must see from here: http://www.sqlservercentral.com/blogs/zoras-sql-tips/2015/11/25/know-more-about-sql-server-error-number-6401/

    ReplyDelete



  9. I loved the way you discuss the topic great work thanks for the share, Let me share this, dotnet training in pune

    ReplyDelete