Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT

T-SQL
23 Comments

CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY’S POST?!?

I know, right? You thought that by combining try/catch with a transaction, you’d get robust error handling.  Instead, you ended up with half the tables populated, and a leftover open transaction. You’d already forgotten Tuesday’s post in which I pointed out that TRY/CATCH doesn’t catch low severity or high severity errors.

TRY/CATCH and transactions are turkeys.

And two turkeys don’t make an eagle.

For a better experience, you need the Turkey Sandwich approach to transaction and error handling. You have to surround those turkeys with two key pieces of code:

We start with the XACT_ABORT option which increases our odds that entire transactions are rolled back or forwards together. However, that alone still may not be enough – remember Tuesday’s post where I talked about how some catastrophic errors skip the CATCH block altogether. We have to finish our turkey sandwich with an additional line to check to see if any transactions still remain, and if so, roll them back.

We’ll do yet another round of clearing the tables, running our proc, waiting a few seconds, canceling it, and then checking the table contents:

FINALLY, we achieve our goal: when the proc is canceled or fails, neither of our tables have any rows in them.

You are not a chef.
You just made your first Turkey Sandwich.

This is not the end of your culinary journey. You’ve merely learned that TRY/CATCH and transactions are turkeys, and you have to make a turkey sandwich by framing them with additional options and code. There is way more work involved.

The next step in your learning journey is to read Erland Sommarskog’s Error and Transaction Handling in SQL Server: Part 1, and then Part 2, and then Part 3. My blog posts this week have not even covered everything in Part 1! My job this week was really just to challenge what you thought you knew about error and transaction handling, and convince you that you need to dig into Erland’s posts.

Also, put down that jar of NOLOCK. That’s not mayonnaise.

Previous Post
Error Handling Quiz Week: Combining Transactions And TRY/CATCH
Next Post
Error Handling Quiz Week: Where Are You Handling Errors?

23 Comments. Leave new

  • loveMeHoldMeCreateMeAnIndex
    January 13, 2022 3:09 pm

    Seems that the link for ‘TRY/CATCH doesn’t catch low severity or high severity errors.’ is broken. Btw. Great stuff again. Will you also cover nested stored procedures? I believe that you could save the state of a transaction in some way to fix issues with that.

    Reply
  • I was actually right about XACT_ABORT?

    Mirabile dictu.

    Reply
  • I appreciate the series. I’d like to propose an alternate title: “You keep using [TRY/CATCH]. I do not think it [does] what you think it [does].”

    I also wanted to thank you that it drove me to learn something new about the scope of SET statements in stored procedures. I was worried that i’d have to turn XACT_ABORT back off at the end of the statement, but after some independent study, it appears you do not.

    Thank you for continuing to encourage us to get in there and try these things out and not just read a blog and move on.

    Reply
  • Interesting. I have had issues in the past with IF @@TRANCOUNT > 0 ROLLBACK; where it appeared that @@TRANCOUNT wasn’t reliable and it sometimes would throw the error that there’s nothing to rollback, or it just would leave an open transaction. But then again, this was when the IF @@TRANCOUNT > 0 ROLLBACK; was inside the catch, not outside the try/catch block. Seems I have more future testing to do.

    Reply
    • I believe the issue you were encountering there was that you weren’t hitting the CATCH block because of the limitations around “small” and “big” exceptions

      Reply
  • Does having this in the CATCH block help, or does setting XACT_ABORT ON make it unnecessary?

    IF XACT_STATE() 0 BEGIN
    ROLLBACK;
    END;

    I’m looking at https://www.brentozar.com/archive/2016/05/try-catch-doesnt-handle.

    Reply
    • Give it a shot and test, by all means. That’s why I do all this work in the open – so y’all can take this and run with it to answer your own questions quickly.

      Reply
  • Just great stuff Brent, thank you so much. You know I came to SQL Server from Oracle, and I just carried on using transactions the same as I did there. Looks like I’ve been doing it wrong for years now, you know this could explain some of those occasional “funnies” over the years I could never get to the bottom of and always had to go and sort out the damage manually afterwards? Brilliant, many thanks.

    Reply
  • I wish T-SQL allowed you to alias words so that I could use SELECT…WITH (NOMAYO).

    Feel free to fork Postgres and implement that feature btw

    Reply
  • Great series. thanks, Brent!

    Reply
  • Thanks Brent for all you done for the community. My first post to you!

    An MS Technet article says using XACT_ABORT solves the open transaction issue but at the cost of the logging implemented in the CATCH block and is not needed if you have QA in place prior to deployment in production, (my paraphrasing)
    See conclusion in https://social.technet.microsoft.com/wiki/contents/articles/40078.sql-server-set-xact-abort-vs-try-catch.aspx

    Any comments?

    Reply
  • Great writeup. That is nasty. And very unexpected behavior. If I’m understanding correctly, this wouldn’t exist if T-SQL’s CATCH actually caught *everything*?

    I wear both the DBA hat and the .NET developer hat at my org. All of our multi-table stuff is client-side. Assuming we are using a language that has exception handling that catches *everything*, this won’t be a problem, right? Or will it?

    Our code follows this pattern:

    Try
    Start Transaction
    Try
    Insert into table 1
    Insert into table 2
    Commit

    Catch
    Rollback
    End Try

    Finally
    Close Transaction
    End Try

    Reply
  • I spent some more time thinking about this yesterday and am stumbling on something. How is line 17 executed when we cancel the request while it’s executing line 8? When i put other statements below the Catch statement they do not appear to be executed, but that rollback does?

    What am i missing?

    Reply
    • Stephen Schissler
      January 14, 2022 10:07 pm

      KC, a friend of mine told me that you are not missing anything. The CATCH block and the ROLLBACK block are not executed in the steps Brent outlined. The canceling of the query during the WAITFOR DELAY stops the execution of the stored procedure. The XACT_ABORT ON takes care of the ROLLBACK.

      Reply
  • Stephen Schissler
    January 14, 2022 10:15 pm

    A friend of mine says you can test it. You can run this changed version of the code, where the TRY and CATCH and Test for TRANCOUNT > 0 are commented out.

    CREATE OR ALTER PROC dbo.Turkey_Sandwich AS
    BEGIN
    SET XACT_ABORT ON; /* THIS IS NEW */

    /* BEGIN TRY */
    BEGIN TRANSACTION;
    INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
    WAITFOR DELAY ’00:00:30′;
    INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
    COMMIT TRANSACTION;
    /* END TRY

    BEGIN CATCH
    ROLLBACK;
    Select ‘Catch Block’;
    END CATCH

    Select ‘How about the rollback test’
    IF @@TRANCOUNT > 0 ROLLBACK; /* THIS IS NEW */
    */
    END
    GO

    Reply
  • INSERT dbo.Parent (DateAdded)
    SELECT DateAdded
    FROM (INSERT dbo.Child(DateAdded)
    OUTPUT Inserted.DateAdded
    SELECT GETDATE()
    ) SQ

    Reply
    • It’d be so awesome if we could boil every transaction down to just one insert statement, right? Unfortunately, in the real world, that isn’t the case. I’m using a simple pair of inserts here in order to illustrate the problem of error handling in transactions, and typically it’s a lot bigger than a pair of insert statements. Thanks though!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu