SQL Server Deadlock Error Handling

Back to: SQL Server Tutorial

SQL Server Deadlock Error Handling

In this article, I am going to discuss SQL Server Deadlock Error Handling i.e.  How to catch deadlock errors using try/catch in SQL Server. Let us understand this with an example. We are going to use the following two tables in this demo.

SQL Server Deadlock Error Handling

Please use the below SQL Script to create and populate the tables with the test data.

-- Create table TableA
CREATE TABLE TableA
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableA values (101, 'Anurag')
INSERT INTO TableA values (102, 'Mohanty')
Go

-- Create table TableB
CREATE TABLE TableB
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableB values (1001, 'Priyanka')
INSERT INTO TableB values (1002, 'Dewagan')
Go

Let’s create two procedures as shown below. The below procedures are self-explained so please go through the comments.

CREATE PROCEDURE spTransaction1
AS
BEGIN
    BEGIN TRANSACTION
  BEGIN TRY
    UPDATE TableA SET Name = 'Anurag From Transaction 1' 
    WHERE Id = 101

    WAITFOR DELAY '00:00:10'

    UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
    WHERE Id = 1001

    -- If both the update statements are succeeded.
        -- Then there is no Deadlock. 
    -- So commit the transaction.
    COMMIT TRANSACTION
    SELECT 'Transaction Completed Successfully'   
  END TRY
  BEGIN CATCH
       -- Check if the error is deadlock error
         IF(ERROR_NUMBER() = 1205)
         BEGIN
             SELECT 'Deadlock Occurred. The Transaction has failed. Please retry'
         END
         -- Rollback the transaction
         ROLLBACK TRANSACTION
  END CATCH
END


CREATE PROCEDURE spTransaction2
AS
BEGIN
    BEGIN TRANSACTION
  BEGIN TRY
    UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
    WHERE Id = 1001
    
    WAITFOR DELAY '00:00:10'
  
    UPDATE TableA SET Name = 'Anurag From Transaction 2' 
    WHERE Id = 101

    -- If both the update statements are succeeded.
        -- Then there is no Deadlock. 
    -- So commit the transaction.
    COMMIT TRANSACTION
    SELECT 'Transaction Completed Successfully'  
  END TRY
  BEGIN CATCH
    -- Check if the error is deadlock error
         IF(ERROR_NUMBER() = 1205)
         BEGIN
             SELECT 'Deadlock Occurred. The Transaction has failed. Please retry'
         END
         -- Rollback the transaction
         ROLLBACK TRANSACTION
  END CATCH
END

Once you create the stored procedures. Then open two instances of SQL Server Management Studio and from the first instance execute the spTransaction1 stored procedure and from the second instance execute the spTransaction2 stored procedure and you will notice that the deadlock error is handled by the catch block.

Retry Logic When Deadlock Occurred

When a transaction fails in SQL Server stored procedure due to deadlock then we can write some logic so that it will rerun the transaction again. The deadlocks usually last for a very short duration. So rerunning the transaction may complete successfully.

Let’s modify the above-stored procedures to implement the Retry Logic.

Stored Procedure spTransaction1:

ALTER PROCEDURE spTransaction1
AS
BEGIN
  -- Declare and initialize the required variables
  DECLARE @ErrorMessage NVARCHAR(2000) = '';
  DECLARE @Iteration INT = 0;
  DECLARE @IterationLimit INT = 2;

  -- Begin the iteration using WHILE loop
  -- Conditions for iteration
    -- @ErrorMessage IS NOT NULL --null indicates successful execution
    -- @Iteration < @IterationLimit -- do not exceed iteration limit
  WHILE(@ErrorMessage IS NOT NULL AND @Iteration < @IterationLimit)    
  BEGIN
    -- First Increment the iteration counter by 1
        SET @Iteration += 1;

        -- Attempt to execute the transaction
    BEGIN TRANSACTION
    BEGIN TRY
      UPDATE TableA SET Name = 'Anurag From Transaction 1' 
      WHERE Id = 101

      WAITFOR DELAY '00:00:05'

      UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
      WHERE Id = 1001

      -- Capture the error message
      SET @ErrorMessage = ERROR_MESSAGE()

      -- If both the update statements are succeeded.
      -- Then there is no Deadlock. 
      -- So commit the transaction.

      COMMIT TRANSACTION
      SELECT 'Transaction Completed Successfully'  
    END TRY
    BEGIN CATCH
      -- Check if the error is deadlock error
       IF(ERROR_NUMBER() = 1205)
       BEGIN
         -- Notify if iteration limit is reached
         IF @Iteration = @IterationLimit
         BEGIN
           SELECT 'Iteration reached; last error: ' + @ErrorMessage
         END
       END
       -- Rollback the transaction
       ROLLBACK TRANSACTION
    END CATCH
  END  
END

Stored Procedure spTransaction2:

ALTER PROCEDURE spTransaction2
AS
BEGIN
  -- Declare and initialize the required variables
  DECLARE @ErrorMessage NVARCHAR(2000) = '';
  DECLARE @Iteration INT = 0;
  DECLARE @IterationLimit INT = 2;

  -- Begin the iteration using WHILE loop
  -- Conditions for iteration
    -- @ErrorMessage IS NOT NULL --null indicates successful execution
    -- @Iteration < @IterationLimit -- do not exceed iteration limit
  WHILE(@ErrorMessage IS NOT NULL AND @Iteration < @IterationLimit)    
  BEGIN
    -- First Increment the iteration counter by 1
        SET @Iteration += 1;

        -- Attempt to execute the transaction
    BEGIN TRANSACTION
    BEGIN TRY
      UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
      WHERE Id = 1001
    
      WAITFOR DELAY '00:00:05'
  
      UPDATE TableA SET Name = 'Anurag From Transaction 2' 
      WHERE Id = 101

      -- Capture the error message
      SET @ErrorMessage = ERROR_MESSAGE()

      -- If both the update statements are succeeded.
      -- Then there is no Deadlock. 
      -- So commit the transaction.

      COMMIT TRANSACTION
      SELECT 'Transaction Completed Successfully'  
    END TRY
    BEGIN CATCH
      -- Check if the error is deadlock error
       IF(ERROR_NUMBER() = 1205)
       BEGIN
         -- Notify if iteration limit is reached
         IF @Iteration = @IterationLimit
         BEGIN
           SELECT 'Iteration reached; last error: ' + @ErrorMessage
         END
       END
       -- Rollback the transaction
       ROLLBACK TRANSACTION
    END CATCH
  END  
END

Now when you run both the stored procedures then you will notice that both the procedures are executed successfully.

In the next article, I am going to discuss how to find blocking queries in SQL Server. Here, in this article, I try to explain the SQL Server Deadlock Error Handling. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.