Notes on Nested Transactions

Last week I was approached by one of our developers, with a troublesome piece of code.  Essentially, the T-SQL he was attempting to run was inside a transaction, and using an INTO clause to put the result set into a target table.  Unfortunately, it wasn't working as anticipated - he was able to open the transaction and run the code, but when it came to checking the row count output he noted 0 rows were inserted into the table before he committed - and he was curious why.

My immediate suspicion was that because the transaction wasn't committed, the rows weren't being inserted, so an alternative method of getting the row count would be to print the value of the @@ROWCOUNT variable at the appropriate time.  However, in the course of testing this, I made a bit of a mistake - I decided to try a nested transaction, to get a better understanding of the problem.

So here's some example code of a straightforward use of the INTO clause.

Working as expected.  So now let's open up a transaction and do the same.  

We have ten rows reported.  Let's query the table and see if the rows actually exist.

Querying the table returns the columns.  Yet the transaction remains open.  This behaviour is not what I was seeing from the developer's code, so I decided to play with it a little and see if we could force this expected behaviour by using a nested transaction - that is, to introduce ANOTHER transaction that deals explicitly with this insert, then leave the outer transaction open so all other behaviours could be rolled back.  So here's what I did.

So far, so good.  The COMMIT was hit, and the transaction committed, and using DBCC OPENTRANS confirms this (not shown).  So now I needed to test that this behaviour would work if the ROLLBACK was hit instead of the COMMIT.  So I modified the code as follows:

This has worked, right?  The inner transaction would have rolled back, leaving one open transaction?  Right?  Wrong.

In fact BOTH transactions were rolled back.  It didn't selectively roll back one transaction, even though the COMMIT only commits one transaction.  And here's the lesson.  When using ROLLBACK, all transactions are rolled back.  This is actually documented in BOL, and I kicked myself for not checking before messing with it.  Here's the quote from BOL:

"When nesting transactions, this [ROLLBACK] statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement." 

Here's an interesting thing.  I could have attempted to avoid this by using a transaction name, so e.g. BEGIN TRAN b and ROLLBACK TRAN b.  It would have errored though - it appears (though playing with this) that transaction b is subsumed by transaction a - @@TRANCOUNT is 2 as expected, but I cannot roll back b and DBCC OPENTRAN only shows the oldest active transaction, a:

So why was this such a bad thing?  Well, in the code above, -- some other stuff here was actually a couple of DELETE statements - that weren't supposed to be run without an explicit COMMIT from the developer.  Because the nested transaction did NOT commit and a ROLLBACK was issued, the rollback rolled back BOTH the open transactions, and the batch proceeded as normal and executed the DELETE statements, outside of a transaction context - leaving us no way of getting them back, bar recovery from a backup.

Luckily this wasn't a disaster in this case as the data wasn't of huge importance.  But it was a lesson for me on using nested transactions.  Many other sources on the web call them 'evil' and 'not to be trusted' - and now I know why.