Retry Execution (Even on Deadlock!)

This business case is closely related to another post: Reduce Deadlocks

Business Case: A multi-client application frequently updates data. Occasionally, transactions fail with resulting loss of information and application instability. The application team has requested a solution in the database layer.

Environment: It has been determined that deadlock on lock resources is the primary cause of the failed transactions.

Approach: While there are a handful of ways to help mitigate locked resource deadlocks, in this instance we will simply fortify our database with a retry capability. In other words, if the first attempt at transaction execution fails, we will retry the transaction a couple more times. For deadlocks due to frequent, relatively short-duration transactions, this fortification can often due the trick.

Demonstration Preparation: For demonstration purposes, first we will create two tables with a handful of records in each. We will then create update queries that, when run back to back in separate processes, will produce a deadlock situation. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- If the first table already exists, drop it
IF OBJECT_ID('DB.dbo.TableOne') IS NOT NULL
    DROP TABLE DB.dbo.TableOne
;

-- Create the first table
CREATE TABLE DB.dbo.TableOne (
    ID INT)
;

-- Populate the first table with data
INSERT INTO DB.dbo.TableOne
VALUES
    (1)
    , (2)
    , (3)
;

-- If the second table already exists, drop it
IF OBJECT_ID('DB.dbo.TableTwo') IS NOT NULL
    DROP TABLE DB.dbo.TableTwo
;

-- Create the second table
CREATE TABLE DB.dbo.TableTwo (
    ID INT)
;

-- Populate the second table with data
INSERT INTO DB.dbo.TableTwo
VALUES
    (10)
    , (11)
    , (12)
;

Now we will create two queries that, when run within a few seconds of each other in separate processes, will produce a locked resource deadlock.

-- This transaction should be run in one query window (process)
BEGIN TRANSACTION
    UPDATE DB.dbo.TableOne
    SET ID = 1
    ;
    -- Delay execution for three seconds, to give
    -- the second transaction time to start
    WAITFOR DELAY '00:00:03'
    ;
    UPDATE DB.dbo.TableTwo
    SET ID = 11
    ;
COMMIT TRANSACTION


-- This transaction should be run in a second query window (process)

BEGIN TRANSACTION
    UPDATE DB.dbo.TableTwo
    SET ID = 12
    ;
    -- Delay execution for three seconds, to give
    -- the first transaction time to create the
    -- deadlock
    WAITFOR DELAY '00:00:03'
    ;
    UPDATE DB.dbo.TableOne
    SET ID = 2
    ;
COMMIT TRANSACTION

When the two transactions above are run within three seconds of each other, in separate windows (processes), it will create a deadlock and SQL Server will decide the second transaction is the victim and cancel its execution.

Solution: To fortify these transactions, we can build in a retry capability. For demonstration purposes, we will only fortify the second transaction; in the real world, all transactions could be fortified depending on business/application requirements.

Our fortification technique will include creating a few variables to capture any error message,  iterate through transaction attempts, and specify the maximum number of attempts we will try before allowing the transaction to fail. Then we will create a WHILE loop to iterate through attempts, and wrap our transaction in a TRY CATCH block to trap execution failures.

-- Declare and initialize variables
DECLARE @ErrorMessage NVARCHAR(2000) = '';
DECLARE @Iteration TINYINT = 0;
DECLARE @IterationLimit TINYINT = 2;

-- Begin the iteration via WHILE operation
WHILE
    -- Conditions for iteration
    @ErrorMessage IS NOT NULL --null indicates successful execution
    AND @Iteration < @IterationLimit -- do not exceed iteration limit
    BEGIN
        -- Increment the iteration counter
        SET @Iteration += 1;
        -- Attempt to execute the transaction
        BEGIN TRY
            BEGIN TRANSACTION
                UPDATE DB.dbo.TableTwo
                SET ID = 12
                ;
                -- Delay execution for three seconds, to give
                -- the first transaction time to create the
                -- deadlock
                WAITFOR DELAY '00:00:03'
                ;
                UPDATE DB.dbo.TableOne
                SET ID = 1
                ;
                -- Capture the error message
                SET @ErrorMessage = ERROR_MESSAGE()
                ;
            COMMIT TRANSACTION
        END TRY
        -- Catch any execution errors
        BEGIN CATCH
            -- Notify if iteration limit is reached
            IF @Iteration = @IterationLimit
                SELECT 'Iteration reached; last error: ' + @ErrorMessage
                ;
            -- Rollback the transaction
            ROLLBACK TRANSACTION
        END CATCH
    END

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: