Fixing Locking and Blocking Issues in SQL Server – Part 7 – Handling Deadlocks in T-SQL

This is part 7 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI
  • Part 3 looked at the impacts of indexing on locking and blocking
  • Part 4 looked at what deadlocks really are and how SQL Server handles them
  • Part 5 looked at how applications should handle deadlocks
  • Part 6 looked at how to avoid deadlocks in the first place

Today, though, I want to look at how to handle deadlocks if you must do that in T-SQL.

The Situation

OK, let's start with the following situation:

Your application is calling a stored procedure, and it regularly ends up being involved in deadlocks. To make this easy to demonstrate, let's create a table of data:


USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Products;
GO

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_dbo_Products PRIMARY KEY,
    ProductName nvarchar(50) NOT NULL,
    UnitPrice decimal(18,2) NOT NULL,
    OnHandQuantity decimal(18,3) NULL
);
GO

INSERT dbo.Products
(
    ProductID, ProductName, UnitPrice, OnHandQuantity
)
VALUES
    (1, N'Product 1', 12.35, 150),
    (2, N'Product 2', 14.30, 155),
    (3, N'Product 3', 15.80, 165);
GO

Then we'll create a stored procedure that can be used to convert a quantity of one product into another product. This can lead to deadlocks:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

Note that I added a delay of 10 seconds between the updates to make it easy for us to generate a deadlock. So let's cause one now.

In one query window, execute the following:


-- Query window 1

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 2,
    @FromProductQuantity = 12.0,
    @ToProductID = 3,
    @ToProductQuantity = 1;

And at the same time, in another query window, execute a similar but different query:


-- Query window 2

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 3,
    @FromProductQuantity = 1,
    @ToProductID = 2,
    @ToProductQuantity = 14.1;

No surprise, we get the dreaded deadlock message in one window:

Adding Retry Logic

So how do we fix this? We can either change this procedure to have retry logic, or we can rename this procedure and call it from a wrapper procedure that has the deadlock retry logic.

Let's do the second option this time.

We'll recreate the procedure as ConvertProduct_Inner:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct_Inner
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

And then we'll add a wrapper procedure:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @MAXIMUM_RETRIES int = 5;

    DECLARE @RemainingRetries int = @MAXIMUM_RETRIES;

    WHILE (@RemainingRetries > 0) -- retry update for victim
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            EXEC dbo.ConvertProduct_Inner
                @FromProductID = @FromProductID,
                @FromProductQuantity = @FromProductQuantity,
                @ToProductID = @ToProductID,
                @ToProductQuantity = @ToProductQuantity;

            SET @RemainingRetries = 0;

            COMMIT;
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) -- deadlock victim
            BEGIN
                SET @RemainingRetries -= 1;
                PRINT 'Warning: Deadlock occurred';
                PRINT 'Remaining retries: '
                      + CAST(@RemainingRetries AS varchar(20));
            END
            ELSE BEGIN
                SET @RemainingRetries = -1;
            END;
            IF XACT_STATE() <> 0
            BEGIN
                ROLLBACK TRANSACTION;
            END;
      END CATCH;
    END;
END;
GO

Now I'll execute both procedures again, and notice that both complete as expected. One has a warning about a deadlock that had occurred but was retried:

This is great, and is something we couldn't do before SQL Server 2005 so many people weren't used to doing it. However, while this will help at the T-SQL end, most other types of retries need to happen at the client end.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *