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

Looks like it depends on what `some nonsense` is, if it is a compile time error it behaves as you say. If it is a runtime error (such as `exec sp_executesql N'SELECT 1/0'`) it behaves how you want. — Martin Smith, Jul 19 '16 at 14:35
Ok, I didn't know that would make a difference. I had a syntax error, and that would explain it. However, is there some documentation that would explain why it does this? I'd still expect the whole transaction to be rolled back, even if it was a compile time error. Or does theXACT_ABORT option only deal with runtime errors? — Ivan Pintar, Jul 19 '16 at 14:47
I wasn't aware of this behaviour previously. I don't know if it is "by design" or not. — Martin Smith, Jul 19 '16 at 14:48
Well the documentation does say "When SET XACT_ABORT is ON, if a Transact-SQL statement raises a *run-time* error, the entire transaction is terminated and rolled back." so maybe technically it is doing what it is supposed to. As the error was a compile time error albeit in a batch run by the outer one. — Martin Smith, Jul 19 '16 at 14:57
@JeroenMostert Can you write this up in an answer so I can select it? I think this describes what I was after, which is why this is happening. — Ivan Pintar, Jul 21 '16 at 19:29

score 4 · Accepted Answer · answered Jul 21 '16 at 23:46

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.

Willie Cheng · Answer 2 · 2020-08-20T05:06:45.713

TRY...CATCH with TRANSACTION for MSSQL

BEGIN TRANSACTION;  
  
BEGIN TRY  
    -- Generate a constraint violation error.  
    SELECT 1/0
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
  
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  
  
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO

Referenc

Transactions breaking when using SP_ExecuteSQL

2 Answers2