Error Handling Quiz Week: Combining Transactions And TRY/CATCH

T-SQL
18 Comments

In yesterday’s epic cliffhanger, you might have been shocked to discover that a plain ol’ transaction does not solve our problem – and in fact, it makes things worse. With yesterday’s code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will have to deal with down the road.

The size of the turkey might have become apparent when you tried to close your query. I’m using SQL Server Management Studio, and when I try to close that query’s tab, I get a popup warning:

Let’s look at our code again to understand what happened:

When we executed the proc, waited a few seconds, and then clicked cancel, we only canceled the execution of the waitfor. The transaction was still left open.

I’ve seen this error in so many applications, I can’t even begin to tell you. But here’s the crappy part: it’s almost impossible to track down where the bug is. All it takes is to make this coding mistake just once, and the transaction will be left open for other parts of code to deal with. I’ve seen so many cases where applications leave transactions open for days, and nobody’s sure exactly where the transaction was started.

The solution is to combine TRY/CATCH and a transaction. Before I get to the code, though, I’ll make sure to run this a few times just to make doggone sure I didn’t leave any transactions open in this window:

Let’s do a transaction in a TRY/CATCH block.

I know, I know, the code is a little wordy given that all we’re tryin’ to do is a pair of inserts, but here we go:

You know the routine: clear out the tables, run the stored procedure, wait a couple of seconds, cancel it, and check the contents of our tables:

And before you try it, for the last time this week, take a guess at what’s going to happen:

  • We’ll have no new rows in either Parent or Child, and no open transactions
  • We’ll have a new row in Parent, but not Child, and an open transaction

And then run it yourself to see whether your hunch was correct. After your experimentations, make sure to close your SSMS window juuuust in case – we’ve been playing around with a lot of transactions this week.

The fun continues in the next post.

Previous Post
Error Handling Quiz Week: Will a Transaction Help?
Next Post
Error Handling Quiz Week: Making a Turkey Sandwich with XACT_ABORT

18 Comments. Leave new

  • Alexander Speshilov
    January 12, 2022 2:30 pm

    Of course “new row in Parent, but not Child, and an open transaction”. But! But if we cancel execution in trigger transaction will be rolled back:

    DROP TABLE IF EXISTS dbo.Parent;
    DROP TABLE IF EXISTS dbo.Child;
    CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2);
    CREATE TABLE dbo.Child (ID INT IDENTITY(1,1), DateAdded DATETIME2);
    GO
    CREATE TRIGGER dbo.parent_i ON dbo.Parent AFTER INSERT AS
    BEGIN
    SET NOCOUNT ON;
    WAITFOR DELAY ’00:00:01′; — we will not cancel here
    END
    GO
    CREATE TRIGGER dbo.child_i ON dbo.Child AFTER INSERT AS
    BEGIN
    SET NOCOUNT ON;
    WAITFOR DELAY ’00:00:30′; — we will cancel here!
    END
    GO

    CREATE OR ALTER PROC dbo.Insert_Transaction AS
    BEGIN
    BEGIN TRANSACTION; — no any try/catch
    INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
    INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
    COMMIT TRANSACTION;
    END
    GO

    TRUNCATE TABLE dbo.Parent;
    TRUNCATE TABLE dbo.Child;
    GO

    EXEC dbo.Insert_Transaction
    /* Let that run for a few seconds, then cancel it. Then: */

    SELECT * FROM dbo.Parent;
    SELECT * FROM dbo.Child;
    GO

    Reply
    • OK, cool – let’s hold off introducing more unrelated features for now, and just follow along with the code. Most folks are going to struggle getting the transaction right as it is without triggers involved. (And your example doesn’t fix it either, heh.) Neat demo though!

      Reply
  • There is no wordy code even if you think you are doing a simple thing. My experience from Oracle is that we always had exception block WHEN OTHERS and in this block we had a proper error logging, rollback and throwing error to procedure / code which called it. It saved us so many times 🙂

    Reply
  • adding SET XACT_ABORT ON in the proc, before the transaction block, will rollback all the inserts

    Reply
  • Carlos Benito
    January 12, 2022 5:44 pm

    I must be doing something wrong because I do not get any rows on the Parent table, or open transactions (no changes to the stored proc). To check, I opened another query and set it to read uncommitted, run the stored proc and I see the row on the Parent table, I cancel the query, I rerun the select and the parent table is empty.

    Reply
  • Richard Powell
    January 12, 2022 5:55 pm

    My friend wants to know if this article is the last in the series because he’s still not getting the result he wants. He found changing a setting that starts with the 24th letter of the alphabet before running todays code gave him the result he was expecting. If this is the last article in the series (which he thinks is implied by the text “the solution is”), he feels that setting should probably be mentioned as well for others with the same issue. My friend thinks this message should be deleted if today was another cliffhanger.

    Disclaimer: My friend can be an idiot sometimes….

    Reply
  • I think Brent is heading towards XACT_STATE lines for COMMIT or ROLLBACK.

    Lucky for me I don’t gamble.

    Reply
  • Brandon Forest
    January 13, 2022 5:58 am

    My Standard Transaction code is something like this:
    BEGIN TRANSACTION
    BEGIN TRY
    Update dbo.TableName
    Set FirstName = ‘MyFirstName’
    COMMIT
    END TRY
    BEGIN CATCH
    Print Error_Message()
    ROLLBACK
    END CATCH

    Reply
  • Seems sql server doesn’t treat cancelling a query execution in SSMS as an error

    Reply
  • HWDan the implicit transaction man
    January 13, 2022 7:42 pm

    I’m behind, see my post in the previous article, same rules apply. open transaction left after cancel, then run commit, see 1 row, or run a rollback instead and see nothing. You can see all the changes in the same session when not committed, and blocked in another session until the transaction state is resolved.

    Reply
    • HWDan – I hear you, but make sure you read the full series in detail where I talk about the problems with open transactions. I see that you’ve left a couple of comments already saying you haven’t read things yet – let’s hit the brakes until we’ve finished reading. Cheers!

      Reply
  • Just tried this and wonder: Isn’t torture against the Geneva Convention?

    Reply
  • HWDan the implicit transaction man
    January 13, 2022 10:16 pm

    Right, basically ATTENTION is untrappable. And you are dead in the water awaiting command with an open transaction. Only the same session running the proc can see the uncommitted insert and that part might confuse some. Other sessions will get blocked, or if RCSI is on in this database and with moderate activity on the same table, someone will be scratching their head in a few weeks when tempdb is 500TB and still growing (or you run out of space, whichever occurs first).

    You can check the @@TRANCOUNT if the transaction was left open, but at least in SSMS, you need manual intervention to kick start the next command to check it. XACT_ABORT may only stop the orphaned transaction from being inadvertently left open. But unless you are paying attention with SSMS, some logic in this case will be needed after the fact to check if the expected data made it in. I think this was your point, more-or-less right? Orphaned transaction inherited by the next process using the same session_id, and/or potentially lost inserts if the code doesn’t validate it’s work.

    Also the best debugging you can do if the transaction stops right there is get the sql_handle from dbo.sysprocesses (not in any of the more modern EXEC DMV’s) as that’s the only place an inactive session still has the sql_handle exposed. But you can only see the SQL for the procedure call itself, not the last executed statement. If real app code reuses the same connection, then the only clue you can find about the orphan is where the table and indexes locks are on.

    I get this a lot with vendor code, especially with implicit transactions where they either forgot to check the @@TRANCOUNT and add explicit commit code, or didn’t realize that their connection defaulted to use implicit transactions rather than auto-commit.

    Reply
    • Hi Dan. No, these are all really interesting points, but they’re beyond the scope of this week’s blog post series.

      It does sound like you’ve got a lot of interesting information you’d like to share – have you thought about starting your own blog?

      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