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
No comments:
Post a Comment