Deadlock recovery using TRY-CATCH in SQL Server stored procedures

Deadlocks occur in SQL Server when two separate transactions require exclusive access to the same resources, but each transaction blocks a portion of what is required by the other. In this situation, SQL Server chooses one of them as the 'deadlock victim', and rolls back the victim's transaction while letting the other one continue through.

The 'victim' will receive error ID 1205, along with a message saying that the transaction was deadlocked on resources with another process and has been chosen as the deadlock victim, and recommending that you rerun the transaction.

Deadlocking recovery can be handled gracefully within the stored proc, to retry a few times using a TRY-CATCH block. This will first try the transaction, and in case it fails, then loop around to retry it again. Here's a sample code:

-- Specify how many times to retry this task

-- in case it is selected as the deadlock victim
DECLARE @retry INT;
SET @retry = 5;

WHILE (@retry > 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        -- The body of the stored procedure, containing
        -- various INSERT/UPDATE/DELETE/SELECT statements
           
        -- Statements
        -- More statements

        -- If we reach here without problems, we are good to go

        -- Set the loop variable to 0 so we can exit out, and COMMIT
        SET @retry = 0;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    
        -- Retry again if this is the deadlock victim
        IF (ERROR_NUMBER() = 1205)
            SET @retry = @retry - 1;
        ELSE
            SET @retry = -1;
       
        -- If not retrying again, log the error
        -- (Removing the IF statement below will cause each retry to be logged as well)
        IF @retry <= 0
        BEGIN
            INSERT INTO ErrorLog
                (SourceProgram, SourceLine, ErrorNumber, ErrorMessage)
            VALUES
                (ERROR_PROCEDURE(), 'Line: ' + CONVERT(varchar, ERROR_LINE()),
                 ERROR_NUMBER(), ERROR_MESSAGE());
        END
    END CATCH
END -- End WHILE loop.

Happy coding!