Error Handling Quiz Week: Tryin’ TRY/CATCH

T-SQL
34 Comments

Let’s say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We’ll write a single stored procedure to do both inserts:

I put a WAITFOR in there, but that isn’t the problem – I’m just using that to demonstrate why the code isn’t production-ready. If I execute the stored procedure, wait a few moments, and then click cancel – what happens?

The results show that a row was added in the Parent table – but not in the Child table:

Well, that’s not good: the business has asked us to guarantee that we either insert both rows, or neither. Our code can’t be doing a half-*ss job. We either gotta have all *ss or no *ss in our back end systems.

This isn’t just about canceling queries.

This problem pops up in many scenarios, like these:

  • Our query loses a deadlock
  • Our query gets killed because it’s blocking others
  • The server runs out of drive space
  • Our application calls multiple statements (rather than a proc) and the app fails midway through

Foreign keys don’t fix this kind of problem, either – in fact, in many situations, foreign keys even cause problems. That’s outside of the scope of this week’s posts, though: this week, I need to teach you why and how to make a turkey sandwich.

Let’s try a TRY/CATCH.

According to the TRY/CATCH documentation – don’t worry, I know it’s a long page, but this important part is right up there at the top of the page so you can ignore it more quickly:

A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Let’s implement it in our code:

And then try the same trick – run it, wait a few seconds, then cancel it, and see what’s inside the tables:

I’m not going to show you what happens, dear reader, because this is a short demo, and I want you to run it yourself to see what happens. Guess what’ll happen before you run it:

  1. We’ll have no new rows in either Parent or Child, or
  2. We’ll have a new row in Parent, but not Child, or
  3. We’ll have new rows in both Parent and Child

Then run the query yourself to see what actually happens. In this next post post, we’ll continue the experiments.

Previous Post
Registration is Open Now for My SQLBits Workshop!
Next Post
Error Handling Quiz Week: Will a Transaction Help?

34 Comments. Leave new

  • 2

    Reply
  • 2- a new row only in parent, catch block is not executed. so same execution as first example

    Reply
  • 2 – unless SET IMPLICIT TRANSACTIONS ON is set.
    By default, each statement is run as a separate transaction unless you explicitly use “BEGIN TRANSACTION” to wrap a series of statements in a Transaction and then use “COMMIT” or “ROLLBACK” to complete or discard the entire set of statements.
    Thus, the first statement is successfully executed as a complete transaction and a row written in the first table. There is no transaction in progress when you terminate the procedure, so the second Insert doesn’t execute and the rollback in the catch block does nothing.
    If you use “SET IMPLICIT TRANSACTIONS ON”, then this example would result in no rows in either table. Of course, if you did set that on, you would need to include an explicit “COMMIT” statement after the second Insert.
    Alternately, wrap both inserts in a block with “BEGIN TRANSACTION” and a “COMMIT” and you would get the desired behavior of both inserts either completing or being rolled back.

    Reply
    • Kim – try running a ROLLBACK without typing BEGIN TRAN. What happens?

      In light of that, does that change anything about your answer?

      Reply
      • Hmm… I think this is an artifact of running the stored procedure inside SSMS, possibly related to the WAITFOR. Try to close the SSMS tab and note that you have uncommitted transactions. Also – scatter a bunch of “print ‘ ‘ + isnull(convert(varchar(32), @@TRANCOUNT),’NULL’);” and you can see the transaction counter incrementing with each run in the same tab.
        IRL I have never had a problem with BEGIN TRANSACTION … BEGIN TRY blocks leaving orphan records, but I have never tried (intentionally) to interrupt one in an interactive SSMS session.

        Reply
        • That might be an artifact of you changing the demo. Close SSMS entirely and follow the instructions in the scripts, exactly as is.

          Reply
          • Yes – the uncommitted transactions message occurred after I added a BEGIN TRANSACTION … COMMIT block around the Inserts, which implies to me that SSMS is maintaining the session state of the tab and the “transaction” is neither completed nor rolled back entirely until you try to close the tab.
            Looking forward to tomorrow’s analysis.

  • Interesting.. I expected that the catch block will not be executed on Abort, so only parent row gets added (2).
    But I get the same result even after adding BEGIN TRANSACTION; .. COMMIT TRANSACTION inside SP, it seems to always commit open transactions on abort?

    Reply
  • Yeah, I thought it was just a missing “begin transaction” at the beginning and a “commit” after the second INSERT. Still doesn’t work though. I’m curious to see the solution.

    Reply
  • Um, I get a row in both tables with either sproc. The parent time stamp is 30s prior to the child time stamp. I’m running the code without change on my local SQL Server 2019.

    Reply
  • That was fun. Thank you for prompting us to think about things like this. I learned something new today!

    Reply
  • Very nice puzzle – I would have thought it would have worked as written – I think to work it needs a “SET XACT ABORT ON” and an explicit transaction around the code in the TRY section.

    Reply
  • Looking forward to part two…

    Reply
  • Wow this is a real head scratcher! Looking forward to the next post 😀

    Reply
  • Thanks for posting this code-along. I think I inadvertently figured things out sort of, but I’m very interested to see the next part to make sure I fully understand.

    Reply
  • christopher pearson
    January 11, 2022 3:20 am

    Got it 🙂

    SET XACT_ABORT ON;
    BEGIN TRAN

    COMMIT

    Reply
  • Interesting one! I’m looking forward to seeing your solution.

    Reply
  • Martin Rancourt
    January 11, 2022 2:49 pm

    a little above my head but
    would the XACT_STATE or the TRANCOUNT have something to do with it?

    a better TRY/CATCH construct like the following would help the “all or nothing” for your two insert statements:
    https://dba.stackexchange.com/questions/134129/transaction-in-a-stored-procedure

    Reply
  • Martin Rancourt
    January 11, 2022 8:02 pm

    But how will it ever get to the CATCH (and rollback) if no error was ever raised?

    Reply
  • The link to the next post in the series is broken.

    Reply
  • Hi Brent,

    The link to the next post in the series is broken. I will search for them individually, but wanted to let you know about it.

    All the best,

    Jamie

    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.