Using TRY/CATCH to handle deadlocks

TRY…CATCH can be used to handle deadlocks. The 1205 deadlock victim error can be caught by the CATCH block and the transaction can be rolled back until the threads become unlocked.

Example:

DECLARE @retry INT;
SET @retry = 5;

WHILE (@retry > 0)
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 1;

        WAITFOR DELAY '00:00:13';
    
        UPDATE my_sales
        SET sales = sales + 1
        WHERE itemid = 2;

        SET @retry = 0;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH 
        IF (ERROR_NUMBER() = 1205)
            SET @retry = @retry - 1;
        ELSE
            SET @retry = -1;
  
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH;
END;

Reference: Using TRY...CATCH in Transact-SQL.