Nested Transaction in Sql Server

In previous article, we learnt about transaction

In this article we will see how nested transactions work.

The variable @@TranCount is used check the count of running transaction(s)

In nested transactions, the variable @@TranCount can be used to check the count of level of nested transactions.

In nested transaction, commit works only on the same level as of transaction (current transaction) i.e. all the statements under that level of transaction will be committed where as rollback always works on parent transaction i.e. all the statements of all the levels will be rolled back.

NOTE: Rollback reverts all the changes even if any nested transaction was committed.

Let's take an example where we will have two level of transaction and we will commit the 2nd level transaction and rollback the 1st level transaction.

begin tran
print 'Tran Count on 1st level : ' + cast(@@trancount as varchar)
    insert into TableA select 'EMP003','Ritesh'   
    begin tran
    print 'Tran Count on 2nd level : ' + cast(@@trancount as varchar)
        insert into TableA select 'EMP004','Abhay'       
    commit tran
    print 'Tran Count after 2nd leve commit : ' + cast(@@trancount as varchar)
rollback tran
print 'Tran Count after 1st level rollback : ' + cast(@@trancount as varchar)
select * from TableA

OUTPUT

Look at the output, we inserted a row in the inner transaction (2nd level) and committed it but even then it is not inserted. This is because we have rolled back the outer transaction (1st level) which even rolled back the committed transaction (2nd level)

In the next example, lets try to rollback the inner transaction and then commit the outer one

begin tran
print 'Tran Count on 1st level : ' + cast(@@trancount as varchar)
    insert into TableA select 'EMP003','Ritesh'   
    begin tran
    print 'Tran Count on 2nd level : ' + cast(@@trancount as varchar)
        insert into TableA select 'EMP004','Abhay'       
    rollback tran
    print 'Tran Count after 2nd leve commit : ' + cast(@@trancount as varchar)
commit tran
print 'Tran Count after 1st level rollback : ' + cast(@@trancount as varchar)
select * from TableA

OUTPUT

See, we got an error here. This is because as I have already mentioned above that rollback works on parent level so it has rolled back all the transaction(s) so there are no transaction(s) left to commit or rollback.