Error Handling Quiz Week: Will a Transaction Help?

In yesterday’s post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn’t fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online’s summary of what TRY/CATCH does:

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.

The devil is in the details. If you scroll further down in the documentation – look, I’m not saying you’re actually going to do that, because we both know damn well that you don’t read the documentation – there are gotchas like:

TRY…CATCH constructs do not trap the following conditions:

  • Warnings or informational messages that have a severity of 10 or lower.
  • Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  • Attentions, such as client-interrupt requests or broken client connections.
  • When the session is ended by a system administrator by using the KILL statement.
Jokes as dark as my shades

So to recap, TRY/CATCH doesn’t catch small problems or big problems – only medium-sized problems.

Okay, that’s a bit of a turkey.

In our scenario this week, I’m causing the error by canceling the query (a client-interrupt request) – but no, we can’t just say, “Never abort your queries.” I’m pro-choice: people deserve the right to end their queries at any stage of their development. I also believe in the death penalty: DBAs deserve the right to kill your queries when they’re causing terrible harm to society at large.

The business is asking us to make sure that the data gets into both the Parent and Child tables, or neither. We’re gonna need something more powerful than TRY/CATCH.

Let’s try a transaction instead.

Microsoft’s documentation on SQL Server transactions starts out with the most important stuff:

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

That, not try/catch, is probably a better fit for what we’re trying to do. Let’s frame our two insert statements with transactional code:

And as before, run the proc, wait a few seconds, cancel it, and then see what we’ve got:

As with yesterday’s demo, I’m not going to show you the results here. Instead, 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

And then run it yourself to see whether your hunch was correct. Just as a reminder, only do these experimentations in a development environment, not production, especially if you happen to have RCSI or SI enabled in production.

To understand what’s happening and why, check out the next post in this series.

Previous Post
Error Handling Quiz Week: Tryin’ TRY/CATCH
Next Post
Error Handling Quiz Week: Combining Transactions And TRY/CATCH

33 Comments. Leave new

  • Would have been interesting to have been a fly on the wall at the design meeting when this “feature” was introduced!

    As always you sum up the complex with a memorable phrase “TRY/CATCH doesn’t catch small problems or big problems – only medium-sized problems.”

    Reply
  • Alexander Speshilov
    January 11, 2022 3:14 pm

    “2. We’ll have a new row in Parent, but not Child” but… but transaction will be opened. If it will be rolled back than will be “1. We’ll have no new rows in either Parent or Child”, if it will be commited – “2. We’ll have a new row in Parent, but not Child”
    Anyway open transaction is really bad.

    Reply
  • Loving these posts Brent, keep ’em coming!

    Reply
  • I know where this is going but I don’t wanna spoil it! This is a common best practice for us in our company when it comes to stored procedures that perform DML operations. The hint here is that the transaction needs to be either committed or rolled back depending on the scenario. The question to consider is: how do you determine whether to commit or roll back? This is something to think about when trying to figure out what to do with your code.

    Reply
  • Ooo ooo ooh open transactions ??

    Reply
  • Thank you for formulating these quiz/challenges… the thinking cap is getting a workout.
    Love your work.

    Reply
  • Torgeir Fredriksen
    January 11, 2022 8:58 pm

    This is a template I have used for years and so far it has worked without problems, even if there is no initial transaction or if there is a transactions (initiated from SSMS or from the app). It is hard to find a recipe by googling, so I just want to share….

    CREATE PROC sp_xxx
    AS

    DECLARE @TranCount INT = @@TRANCOUNT;

    BEGIN TRY
    IF @TranCount = 0
    BEGIN TRANSACTION
    ELSE
    SAVE TRANSACTION tran_xxx;

    /* DO THE WORK … */

    IF @TranCount = 0 COMMIT
    END TRY

    BEGIN CATCH
    DECLARE
    @RaisErrorUser NVARCHAR(128),
    @ErrorMsg AS NVARCHAR(MAX),
    @XState INT;

    SELECT
    @RaisErrorUser = SUSER_SNAME(),
    @ErrorMsg = ERROR_MESSAGE() + ‘ – at line # ‘ + CAST(ERROR_LINE() AS NVARCHAR(10)),
    @XState = XACT_STATE()

    IF @TranCount = 0
    ROLLBACK
    ELSE IF @XState -1
    ROLLBACK TRANSACTION tran_xxx;

    RAISERROR(@ErrorMsg, 18, 1, @RaisErrorUser)
    END CATCH

    Reply
    • You should try that with this week’s exercise. You might be surprised to find out it doesn’t work either. You’ll find out why this week. Enjoy the series!

      Reply
      • Torgeir Fredriksen
        January 12, 2022 9:48 am

        Yes, you’re right 🙂
        It does not work if cancelling the query, and maybe even worse; it leaves the transaction open (!)
        Looking forward to how you are going to “catch” the cancelling…

        Reply
  • Within the context of this quiz topic, is my use of SET XACT_ABORT to catch the client-interrupt correct, or is @@TRANCOUNT a more “production” leaning solution? I realize that the answer to these types of questions is often — It depends.

    BEGIN TRY
    BEGIN TRANSACTION
    INSERT INTO dbo.parent(date_added) VALUES (GETDATE());
    WAITFOR DELAY ’00:00:30′; –TRY…CATCH constructs do not trap the following conditions: Attentions, such as client-interrupt requests or broken client connections.
    INSERT INTO dbo.child(date_added) VALUES (GETDATE());
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION
    END CATCH

    vs.

    SET XACT_ABORT ON –when SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
    BEGIN TRANSACTION
    INSERT INTO dbo.parent(date_added) VALUES (GETDATE());
    WAITFOR DELAY ’00:00:30′;
    INSERT INTO dbo.child(date_added) VALUES (GETDATE());
    COMMIT TRANSACTION

    Reply
  • My friend says you should write it like this instead. My friend is also an idiot who thinks crayons are delicious.

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

    INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
    WAITFOR DELAY ’00:00:30′;
    INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());

    IF @@TRANCOUNT > 0
    BEGIN
    COMMIT TRANSACTION; /* THIS IS NEW */
    END
    END TRY
    BEGIN CATCH
    PRINT ERROR_MESSAGE();
    IF @@TRANCOUNT > 0
    BEGIN
    ROLLBACK TRANSACTION;
    END
    END CATCH
    GO

    Reply
  • Can you please try this? have we stumble on some major bugs?

    DROP TABLE IF EXISTS dbo.Parent;
    DROP TABLE IF EXISTS dbo.Child;

    CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2, Comments varchar(max));
    CREATE TABLE dbo.Child (ID INT IDENTITY(1,1), DateAdded DATETIME2, Comments varchar(max));
    GO

    CREATE OR ALTER PROC dbo.Insert_Transaction (@Comments varchar(max)) AS
    BEGIN
    –SET XACT_ABORT ON
    BEGIN TRANSACTION; /* THIS IS NEW */

    INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
    WAITFOR DELAY ’00:00:30′;
    INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());

    COMMIT TRANSACTION; /* THIS IS NEW */
    END
    GO

    CREATE OR ALTER PROC dbo.Insert_Transaction2 (@Comments varchar(max))AS
    BEGIN
    SET XACT_ABORT ON
    BEGIN TRANSACTION; /* THIS IS NEW */

    INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
    WAITFOR DELAY ’00:00:30′;
    INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());

    COMMIT TRANSACTION; /* THIS IS NEW */
    END
    GO

    EXEC dbo.Insert_Transaction ‘Run Insert_Transaction full 30 secs’;
    SELECT * FROM dbo.Parent;
    SELECT * FROM dbo.Child;
    — Expected Result = 1 parent & 1 child

    EXEC dbo.Insert_Transaction ‘Run Insert_Transaction stop at 5 secs’;
    SELECT * FROM dbo.Parent;
    SELECT * FROM dbo.Child;
    — Expected Result = 2 parent & 1 child
    — Actual Result = 2 parent 1 child

    EXEC dbo.Insert_Transaction2 ‘Run Insert_Transaction2 full 30 secs’;
    SELECT * FROM dbo.Parent;
    SELECT * FROM dbo.Child;
    — Expected Result = 3 parent & 2 child
    — Actual Result = 3 parent 2 child

    EXEC dbo.Insert_Transaction2 ‘Run Insert_Transaction2 stop at 5 secs’;
    SELECT * FROM dbo.Parent;
    SELECT * FROM dbo.Child;
    — Expected Result = 3 parent & 2 child
    — Actual Result = 1 parent 1 child !!!!! WHAT !!!!!

    Reply
    • oh sorry, you can add the insert for comment if needed, but not necessary to produce result.

      Reply
    • Daniel – you’re probably trying a little too much in one test. Ease up and read this week’s posts instead. Cheers!

      Reply
    • oh, I see what’s happening, the 3rd execution has run the full begin-commit, but it’s considered as part of another begin transaction within the session, and rolled back by the 4th execution… but it’s counter intuitive… I would assume that the 3rd execution should actually be committed.

      Reply
  • You are such a tease … the suspense is killing me! 😀

    Reply
  • If it is a quiz *week*, the proposed solution on Day 2 will not work… 🙂
    Eagerly awaiting next installments.

    Reply
  • I love the way Erland Sommarskog put it [here](https://sommarskog.se/error_handling/Part2.html):

    “Every once in a while, I get the feeling that SQL Server is intentionally designed to be as confusing as possible. When they plan for a new release they ask each other what can we do this time to confuse the users? Sometimes they run a little out of ideas, but then someone says Let’s do something with error handling!”

    Reply
  • I’m not sure if this adds anything different to what has been mentioned, but this is a method we use to try to trap errors and roll back an transaction.

    BEGIN TRY
    BEGIN TRANSACTION;

    /*
    DO YOU BUSINESS HERE
    */

    IF @@TRANCOUNT > 0
    BEGIN
    COMMIT TRANSACTION;
    PRINT ‘BUSINESS DONE, THANK YOU!’;
    END;
    END TRY
    BEGIN CATCH
    /*
    YOUR BUSINESS IS BAD, LEAVE WITHOUT A TRACE
    (If Transaction exists, roll it back)

    1) ROLL BACK TRANSACTION
    2) LOG ERROR TO TABLE, NOTIFY DEVELOPERS
    3) RETURN ERROR CODE TO CALLING PROCESS
    */
    IF @@TRANCOUNT > 0
    BEGIN
    ROLLBACK TRANSACTION;
    END;

    PRINT ‘AN ERROR DONE HAPPENED!’;
    END CATCH;

    Reply
    • Chris – as I’ve kinda gently suggested to other folks throughout this week’s series, make sure to actually test your code before suggesting it’s an answer. This code fails just as the code in the blog post does. You’ll see why at the end of the week. 😉

      Reply
      • Christopher Knight
        January 12, 2022 10:14 am

        I look forward to it! We actually use this template in some of our production code (with actual logging code in place of the comments), so it will be interesting to see what’s going wrong 🙂

        Reply
  • First – Well that depends on how you cancel the query….

    If, like me, you killed the session then you get what you expect. Zip, zilch and nada.

    It took me a moment to work out what I was doing wrong!

    And isn’t this why xact_abort should be on when declaring transactions?

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

    The trick with the transaction is that you can see the uncommitted row if you run the select IN THE SAME SESSION that ran the cancelled proc in (that’s how it always works).
    If you select from the tables in another session, you get blocked (transaction is still open), or if using NOLOCK you see the row (but only because of NOLOCK). Now back to the session that ran the cancelled proc, @@TRANCOUNT = 1, so you either have to commit it, then you see the one row, or ROLLBACK, then you see nothing. However, @@ERROR is 0 since we probably lost out on that last statement context. so without looking at other stuff to check, there may not necessarily be a way of knowing it was cancelled (maybe, maybe not, I haven’t looked it over that far yet).

    If in any situation, there is potential for nested transaction and you want to make sure you dump it all, I use
    WHILE @@trancount > 0 ROLLBACK TRAN;

    Reply
  • I used my SP template (without my persistent logging code) and plugged in your code, tested it by waiting 5 seconds and then killing the session. No rows are present.

    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 OR ALTER PROC dbo.Insert_Transaction
    AS
    BEGIN
    DECLARE
    — Result Code from Logic. Should be zero (0) or something has gone awry.
    @ResultCode INT,

    — Error capture variables
    @msg NVARCHAR(4000),
    @erruser sysname,
    @errproc VARCHAR(128),
    @errnum INT,
    @errmsg NVARCHAR(4000),
    @errline INT,
    @errsev INT,
    @errstate INT;

    — Initialize variables
    SET @ResultCode = 0;

    BEGIN TRY
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION; /* THIS IS NEW */

    INSERT INTO dbo.Parent
    (
    DateAdded
    )
    VALUES
    (GETDATE());
    WAITFOR DELAY ’00:00:30′;
    INSERT INTO dbo.Child
    (
    DateAdded
    )
    VALUES
    (GETDATE());

    COMMIT TRANSACTION; /* THIS IS NEW */

    END TRY
    BEGIN CATCH
    SET @erruser = SYSTEM_USER;
    SET @errproc = ERROR_PROCEDURE();
    SET @errnum = ERROR_NUMBER();
    SET @errmsg = ERROR_MESSAGE();
    SET @errline = ERROR_LINE();
    SET @errsev = ERROR_SEVERITY();
    SET @errstate = ERROR_STATE();

    SET @msg
    = CONCAT(
    ‘erruser=’,
    @erruser,
    ‘ errproc=’,
    @errproc,
    ‘ errnum=’,
    @errnum,
    ‘ errmsg=’,
    @errmsg,
    ‘ errline=’,
    @errline,
    ‘ errsev=’,
    @errsev,
    ‘ errstate=’,
    @errstate
    );

    — Roll back any active or uncommittable transactions before
    — inserting information in the ErrorLog.
    — XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.
    — XACT_STATE = -1 The transaction is in an uncommittable state
    IF XACT_STATE() 0
    BEGIN
    ROLLBACK TRANSACTION;
    END;

    SET @ResultCode = @errnum;

    IF @ResultCode 0
    RAISERROR(‘%s’, 16, 1, @msg);

    END CATCH;

    RETURN @ResultCode;
    END;
    GO

    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