Transactions Really Do Work

Believe it or not, transactions really do work — even when you forget to think about them! In the example below we’ll walk through how transaction rollback can prevent an attempt at error capture.

For this example, we will first create a table in which to store any errors we generate.

-- Create a table in which to store error messages
IF OBJECT_ID(N'tblLog') IS NOT NULL
    DROP TABLE tblLog;
CREATE TABLE tblLog (
    ID INT IDENTITY(1,1) NOT NULL
    , DateStamp DATETIME2(3) NOT NULL
    , ErrorNumber INT NOT NULL
    , ErrorMessage NVARCHAR(2048) NOT NULL
    )
;
ALTER TABLE tblLog
ADD CONSTRAINT DF_tblLog_DateStamp
DEFAULT (GETDATE()) FOR DateStamp
;
GO

With our error capture table created, let us now generate some errors! Specifically, we will produce an uncommitable transaction error so that we can eventually demonstrate how transaction rollback can affect error capture.

-- First, let's demonstrate capturing an uncommitable
-- transaction error
BEGIN TRY
    -- This is an uncommitable transaction because the
    -- table does not exist in the database
    DROP TABLE tblDoesNotExist;
END TRY
BEGIN CATCH
    -- Here is the catch where we capture the error
    INSERT INTO tblLog (ErrorNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
END CATCH

Assuming tblDoesNotExist does not, in fact, exist in your database, the above statement will result in an entry to the error capture table, tblLog. Let’s take a look at the error:

-- Notice that we've captured the error
SELECT * FROM tblLog;

Yes, indeed, the error was captured; and it told us that the table does not exist and we thus cannot delete it. So far, so good. This information is useful for us to be able to troubleshoot the problem.  Let’s clear the error log now to reset it for our next example.

-- Clear the log table
DELETE FROM tblLog;

In the following example, we will take the exact statement we ran above and put it inside a transaction that includes rollback logic. Using rollback logic of this type is very common and is good practice. In fact, more robust validation and tracking can be implemented — but that is superfluous to the current demonstration.

-- Now, let's see how error capture can be lost in a transaction
BEGIN TRY
    BEGIN TRANSACTION;
        -- Here is the code from above, but run
        -- within a transaction with rollback logic
        BEGIN TRY
            -- This is an uncommitable transaction because the
            -- table does not exist in the database
            DROP TABLE tblDoesNotExist;
        END TRY
        BEGIN CATCH
            -- Here is the inner catch where we attempt to
            -- capture the error
            INSERT INTO tblLog (ErrorNumber, ErrorMessage)
            VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
        END CATCH
    -- This commit will fail because the transaction is in
    -- an uncommitable state due to the attempted DROP TABLE
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- The catch is called; transaction must be rolled back
    ROLLBACK TRANSACTION;
    -- Capture the outer error
    INSERT INTO tblLog (ErrorNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
END CATCH

At first glance, it might seem like we would only expect the same, one error in our log table. Afterall, the DROP TABLE command was in a TRY-CATCH block and so it was “handled”, right? And, if it was handled, then the outer CATCH block would not even execute. Right? Not exactly.

The DROP TABLE command will fail and will result in the error being written to the log table. However, because that error makes the transaction uncommitable, the COMMIT TRANSACTION also fails and pushes execution to the outer CATCH block. And, in the outer CATCH block, the transaction is rolled back. Which means writing the inner error to the log does NOT occur! Let’s see what the log shows:

-- Notice that only the outer error is captured; the
-- inner error capture is rolled back in the outer catch
SELECT * FROM tblLog;

While I hope this example is easy to follow (as examples should be), I trust that you can imagine how the situation will get confusing if, rather than a single DROP TABLE command, there were nested procedures with their own transactions and try-catch blocks. At any point in that potentially complex web of execution a statement might fail. And, despite our intention to capture the error at the point of failure, all of that error capture activity could get rolled-back and the end result might be a simple statement saying the transaction was uncommitable. A single error statement such as that provides virtually no insight into which part of the transaction was uncommitable — only that something within it was uncommitable.

What can be done about that? Pay close attention to where your transactions begin and end, and ensure you are not capturing errors where they will not be persisted!

Oh yes, and don’t forget to clean up after yourself! 🙂

-- Clean up
IF OBJECT_ID(N'tblLog') IS NOT NULL
    DROP TABLE tblLog;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: