Some witty finding on Nested Transaction with Rollback and Commit

When we have transaction block defined inside transaction block, we call it as Nested transaction, and here in this article we will see how Rollback and Commit works with Nested transaction. Let’s start with Rollback

   

Nested Transaction with Rollback

We are creating a table with a single column for our demo.

create table TAB_NestedTransaction_Demo

(col1 int)

Command(s) completed successfully.

Now lets start with our transaction block, here we are creating a transaction block named “firstTran”, inside it we are first inserting a record in newly created table “TAB_NestedTransaction_Demo” thereafter again we are creating one more transaction block named “secondTran” in inside the second transaction block we are inserting one more record in “TAB_NestedTransaction_Demo” table. Then we are rollbacking the second transaction and checking the transaction count (number of active transaction)

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

     

      begin tran secondTran

            insert into TAB_NestedTransaction_Demo values(2)           

      rollback tran secondTran

      select @@TRANCOUNT     

Msg 6401, Level 16, State 1, Line 6

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

No of Active Tran

-----------------

2

The above script throw an error while trying to rollback the inner transaction “secondTran”. And we can see the transaction count comes out as 2. That means none of the transaction gets rollbacked even after explicitly rollbacking innner transaction “secondTran”.Lets now execute the below lines and see the finding

rollback tran firstTran

go

select @@TRANCOUNT as [No of Active Tran]

No of Active Tran

-----------------

0

These two lines successfully gets executed and we can see transaction count comes out to be zero now.

Conclusion : from the above example we can see that  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.

Nested  Transaction with Commit

Lets now check out nested transaction with COMMIT, here we can see in the below code block we open first transaction firstTran then we inserted a record in TAB_NestedTransaction_Demo then we are opening one more transation inside the outer one and inserting one more record to the same table, thereafter we are committed second transaction. Then we rollback first transaction.

   

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

      begin tran secondTran

            select @@TRANCOUNT [No of Active Tran from Inner]

            insert into TAB_NestedTransaction_Demo values(2)

      commit tran secondTran

      select @@TRANCOUNT [No of Active Tran from Outer]

rollback

[No of Active Tran from Inner]

-----------

2

[No of Active Tran from Outer]

-----------

1

Now lets query TAB_NestedTransaction_Demo.

select * from TAB_NestedTransaction_Demo

col1

-----------

(0 row(s) affected)

To our sock what we saw there is no records which get inserted in the table even after committing the inner transaction after inserting record into the table.

One more finding which we can see is, after committing the inner transaction, the transaction count reduced to one. Which says that the inner transaction is committed, but when doing select query on table, after executing last rollback statement, we found no records in the table?

Conclusion:  There is no effect in committing the inner transaction in Nested Transaction. And you cannot rollback inner transaction.  

Ask a programmer about his experience of living with code, he will say love, grace and beautiful…   

Post Reference: Vikram Aristocratic Elfin Share