Nesting Transactions in Stored Procedures (Part 1)

Nesting transactional stored procedures is an absolute minefield.

Many projects I've worked on have just avoided opening transactions within stored procs altogether, and rely entirely on ADO / ADO.Net transactions in the application. This is certainally a simple way to avoid the problem, but isn't always appropriate. At some point there's always a data integrity rule that you want enforced at the lowest possible level (to avoid it being accidentally bypassed - I'm thinking auditing, or the classic 'move money between accounts' scenario). Putting that rule in a stored proc, and making that the only point of call for writes to the table involved makes it pretty safe. This is even more important if it's not just your app using the database.

But nevertheless avoidance is appropriate in many cases, if only because handling the nesting of transactions within nested stored proc calls is a complex matter. To summarize the problems:

Rollback and commit are asymmetric in nested transaction scenarios

Rollback aborts the whole nested stack, whereas commit just decreases the nesting level (only the final commit actually commits anything). So error handling has to behave differently in the proc at the 'root' of the transaction than it does in nested procs. Ulp!

SQL error handling is hard, and poorly-understood

Even basic TSQL error handling is poorly understood: having an exception raised in the calling application but the rest of the stored proc continue executing is pretty counter-intuitive, right? Whadyamean the transaction committed anyway!?!

The fact is that most SQL errors don't affect batch completion without explicit handling, some abort the batch but not the transaction, and some abort both. Furthermore, XACT_ABORT changes which do which. Erk!


Fortunately we don't have to come up with a solution, because there are already well-defined patterns for handling both these issues. There's an excellent article on the subject on Code magazine that's been the basis for my approach for a few years now:

Handling SQL Server Errors in Nested Procedures by Ron Talmage (I use the multi-level model)

Unfortunately barely any developers appear to be aware of the problem, let alone the solution patterns. This is particularly problematic, because either of Ron's solutions must be applied consistently in order to work properly. If that's an issue, maybe you're best going back to the avoidance pattern.

Anyway, the big question for me is "how has this pattern been affected by TRY ... CATCH functionality in Sql Server 2005?". We'll look at that next time...