Transactions breaking when using SP_ExecuteSQL

Question

I'm using SQLServer 2014 and I have a simple db with one table which has an ID and a varchar column called data. There's some weird behavior when I run the following statement:

SET XACT_ABORT ON
BEGIN TRANSACTION
    exec sp_executesql N'some nonsense'
    insert into testTable values ('b')

COMMIT  

SSMS shows that there was an error because I tried to run an incorrect query in the sp_executesql call. However, it also shows 1 row(s) affected. If I run a select query on the testTable, I can see that value 'b' was inserted.

If I wrap the statements in a TRY/CATCH block everything works as expected, and the whole transaction action is rolled back:

BEGIN TRANSACTION
BEGIN TRY
    exec sp_executesql N'some nonsense'
    insert into testTable values ('b')

    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    ROLLBACK
END CATCH

Shouldn't the SET XACT_ABORT ON ensure that the entire transaction is rolled back if something goes wrong? Is there a setting that I'm missing?

Thanks

Answer 1

This happens because a runtime syntax error that isn't wrapped in a TRY/CATCH doesn't abort an active transaction, even with XACT_ABORT set to ON. The exact rules for what does and doesn't abort, and under what circumstances, are not at all straightforward or obvious. Erland Sommarskog has an excellent write-up on error handling in general and the rules of what does and doesn't abort in particular.

I won't reproduce all that here, but here's the issue boiled down to its essentials:

SET XACT_ABORT ON   -- or OFF, it makes no difference
BEGIN TRANSACTION
EXEC ('SELECT')     -- Incorrect syntax near 'SELECT'
PRINT @@TRANCOUNT   -- Prints 1, transaction is still going
COMMIT
PRINT @@TRANCOUNT   -- Prints 0, transaction succeeded

Despite XACT_ABORT ON, execution not only doesn't stop, the transaction isn't even aborted. Adding TRY/CATCH changes the rules:

SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
    EXEC ('SELECT')              -- Incorrect syntax near 'SELECT'
    PRINT 'After bad statement.' -- Does not print
    COMMIT
END TRY
BEGIN CATCH
    PRINT @@TRANCOUNT            -- Prints 1, transaction is still going, but it's doomed
END CATCH
-- Error here: 
-- 'Uncommittable transaction is detected at the end of the batch. 
--  The transaction is rolled back.'

Now the transaction is doomed, and if we don't roll it back ourselves, SQL Server does it for us (with an error). This dooming is courtesy entirely of the XACT_ABORT, because turning it off yields something different yet again:

SET XACT_ABORT OFF
BEGIN TRANSACTION
BEGIN TRY
    EXEC ('SELECT')               -- Incorrect syntax near 'SELECT'
    PRINT 'After bad statement.'  -- Does not print
    COMMIT
END TRY
BEGIN CATCH
    PRINT @@TRANCOUNT             -- Prints 1, transaction is still going
END CATCH
PRINT @@TRANCOUNT                 -- Prints 1, transaction is still going!
ROLLBACK

The moral of the story is: proper error handling in T-SQL is very tricky. What usually works for me is doing SET XACT_ABORT ON for any non-trivial batch of statements, and having transactions initiated and committed or rolled back outside SQL Server entirely (through client code). This circumvents much of the difficulties with understanding what does and does not halt or doom a transaction, because any error that SQL Server passes back on to the client will ultimately result in a rollback. But, of course, even that is no silver bullet.