Nested Transaction In SQL Server

SQL Server facilitate us to define nested explicit transaction. We may need to define nested transaction in many situation, so before you define or use nested transaction you must be aware of behavior of nested transaction.

Create tables from following script to perform following tests.

Create Table Parent_Table(Id int Identity(1,1),DataValue Varchar(20))
Go 
Create Table Child_Table(Id Int Identity(1,1),DataValue Varchar(20))
Go

This example explains that rolling back outer transaction will rollback all the inner transaction irrespective of whether those were committed or rolled back.

Start A Transaction and call it parent transaction

Begin Transaction Parent

Insert Data Into Parent Table

Insert Into Parent_Table Values('Parent')

Start another transaction and call it child transaction

Begin Transaction Child; --Start Child Transaction

Insert Data into Child Table

Insert Into Child_Table Values('Child')

Now Commit Child Transaction

Commit Transaction Child

Now Rollback  Parent Transaction

Rollback Transaction Parent

So in above code steps we have committed the child transaction and rolled back the parent transaction which implies that data should be inserted into parent table but here this is not the case rolling back outer transaction will rollback all the inner transaction (even if it was committed). So this is quite important and must be kept in mind while working or designing nested transaction. You can use following query to see data in parent and child table and you will see that no data exists in both the tables

Select * From Parent_Table
Go

Select * From Child_Table
Go