sql server - 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. :)