Rollback the inner transaction of nested transaction

Question

suppose I have following sql statement in sql server 2008:

BEGIN TRANSACTION     SqlStatement1     EXEC sp1     SqlStatement3 COMMIT TRANSACTION 

The code of sp1

BEGIN TRANSACTION SqlStatement2 ROLLBACK TRANSACTION 

My question is: Is SqlStatement3 actually executed?

Answer 1

SQL Server doesn't really support nested transactions. There is only one transaction at a time.

This one transaction has a basic nested transaction counter, @@TRANCOUNT. Each consecutive begin transaction increments the counter by one, each commit transaction reduces it by one. Only the commit that reduces the counter to 0 really commits the one transaction.

A rollback transaction undoes the one transaction and clears @@TRANCOUNT.

In your case, the funny result is that SqlStatement3 is run outside a transaction! Your final commit will throw an "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION" exception, but the effects of SqlStatement3 are permanent.

For example:

create table #t (col1 int) insert #t (col1) values (1) BEGIN TRANSACTION update #t set col1 = 2 -- This gets rolled back BEGIN TRANSACTION update #t set col1 = 3 -- This gets rolled back too ROLLBACK TRANSACTION update #t set col1 = 4 -- This is run OUTSIDE a transaction! COMMIT TRANSACTION -- Throws error select col1 from #t 

Prints 4. Really. :)