MSSQL - How to Handle and Retry Deadlocks

Introduction

This tutorial will guide you through handling and retrying deadlocks in Microsoft SQL Server (MSSQL). Deadlocks occur when two or more sessions are waiting for each other to release locks on resources. By implementing retry logic, you can improve the robustness of your database applications. We will cover creating a sample table, simulating deadlocks, and implementing retry logic for various database operations.

Step 1: Create a Sample Table

To start, you'll need to create a sample table that we'll use for demonstrating deadlocks.

1. Open your SQL query window.
2. Execute the following SQL commands:

SELECT @@SPID
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'SampleTable')
    DROP TABLE SampleTable

CREATE TABLE [SampleTable] (
    [Id]          [int] IDENTITY(1,1) NOT NULL,
    [Name]        [varchar](100) NULL,
    [Value]       [varchar](100) NULL,
    [DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
    CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)

INSERT INTO SampleTable(Name, Value)
SELECT 'Name1', 'Value1' UNION ALL
SELECT 'Name2', 'Value2' UNION ALL
SELECT 'Name3', 'Value3'

SELECT * FROM SampleTable

Step 2: Simulate Deadlocks

Next, simulate deadlocks by running two separate sessions that will compete for the same resources.

Session 1

1. In a new query window, execute the following commands:

SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
UPDATE SampleTable SET Name = Name + Name WHERE ID=2
WAITFOR DELAY '00:00:10'
UPDATE SampleTable SET Name = Name + Name WHERE ID=1
COMMIT TRAN

Session 2

2. In another new query window, execute these commands:

SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE SampleTable SET Name = Name + Name WHERE ID=1
WAITFOR DELAY '00:00:10'
UPDATE SampleTable SET Name = Name + Name WHERE ID=2
COMMIT TRAN

Tip

Make sure to run these sessions simultaneously to create a deadlock scenario.

Step 3: Implement Retry Logic for Transactions

To handle deadlocks effectively, implement a retry mechanism in your transactions.

1. Use the following template to create a retry block for INSERT, UPDATE, or DELETE operations:

SET DEADLOCK_PRIORITY LOW
DECLARE @RETRY_COUNT_CURRENT INT
DECLARE @RETRY_COUNT_MAXIMUM INT
DECLARE @ERROR_NUM INT
DECLARE @ERROR_MSG NVARCHAR(MAX)

SET @ERROR_NUM = 0
SET @RETRY_COUNT_CURRENT = 0
SET @RETRY_COUNT_MAXIMUM = 3

WHILE @RETRY_COUNT_CURRENT < @RETRY_COUNT_MAXIMUM
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        UPDATE SampleTable SET Name = Name + Name WHERE ID=2
        WAITFOR DELAY '00:00:10'
        UPDATE SampleTable SET Name = Name + Name WHERE ID=1
        COMMIT
        BREAK
    END TRY
    BEGIN CATCH
        SELECT @ERROR_NUM = ERROR_NUMBER(), @ERROR_MSG = ERROR_MESSAGE()
        PRINT @ERROR_MSG
        PRINT @ERROR_NUM
        ROLLBACK
        SET @RETRY_COUNT_CURRENT = @RETRY_COUNT_CURRENT + 1
        WAITFOR DELAY '00:00:05'
        CONTINUE
    END CATCH
END

Step 4: Implement Retry Logic for SELECT Operations

You can also implement retry logic for SELECT operations, especially if they could become deadlock victims. Use the following template:

SET DEADLOCK_PRIORITY LOW
DECLARE @RETRY_COUNT_CURRENT INT
DECLARE @RETRY_COUNT_MAXIMUM INT
DECLARE @ERROR_NUM INT
DECLARE @ERROR_MSG NVARCHAR(MAX)

SET @ERROR_NUM = 0
SET @RETRY_COUNT_CURRENT = 0
SET @RETRY_COUNT_MAXIMUM = 3

CREATE TABLE #TABLE_01 (
    Id INT,
    Name VARCHAR(100),
    Value VARCHAR(100),
    CreatedOn DATETIME
)

WHILE @RETRY_COUNT_CURRENT < @RETRY_COUNT_MAXIMUM
BEGIN
    BEGIN TRANSACTION
    BEGIN TRY
        TRUNCATE TABLE #TABLE_01
        INSERT INTO #TABLE_01 SELECT * FROM SampleTable
        COMMIT
        SELECT * FROM #TABLE_01
        BREAK
    END TRY
    BEGIN CATCH
        SELECT @ERROR_NUM = ERROR_NUMBER(), @ERROR_MSG = ERROR_MESSAGE()
        PRINT @ERROR_MSG
        PRINT @ERROR_NUM
        ROLLBACK
        SET @RETRY_COUNT_CURRENT = @RETRY_COUNT_CURRENT + 1
        WAITFOR DELAY '00:00:05'
        CONTINUE
    END CATCH
END

Conclusion

Handling deadlocks effectively is crucial for maintaining the performance and reliability of your SQL Server applications. By implementing retry logic for both transaction and select operations, you can mitigate the impact of deadlocks. Follow these steps and utilize the provided code templates to improve your database interactions. Consider testing your implementation under various load conditions to ensure its robustness.