SQL Server Deadlock Resolution : Transaction Retry

Transaction retry is one of the easiest way to get control of SQL Server Deadlock Resolution, however it might get tedious depending on number of deadlock an application is experiencing.

The Deadlock occurs because of incompatible locks. The locks are being acquired and released so fast that same locking situation might not exist the other minute. Transaction retry takes advantage of this and reruns the deadlocked transaction. The number of retries can be specified in the code. Let’s apply the above approach on the deadlock example I shared in my earlier blog on cyclic deadlock;

The below transactions when run in parallel in two different connections create a deadlock situation.

Use AdventureWorks2008R2
GO  
-- Run the below script in connection 1
BEGIN TRAN
 
UPDATE Sales.SalesOrderDetail 
SET OrderQty=10 
WHERE SalesOrderID=57024 
AND SalesOrderDetailID=60591 
 
WAITFOR DELAY '00:00:02'
 
UPDATE Person.Person SET Suffix='Dr.' WHERE BusinessEntityID=1 
 
COMMIT TRAN
 
--Run the below script in connection 2
Use AdventureWorks2008R2
GO
BEGIN TRAN
 
UPDATE Person.Person SET Suffix='Mr.' WHERE BusinessEntityID=1 
 
WAITFOR DELAY '00:00:02'
 
UPDATE Sales.SalesOrderDetail 
SET OrderQty=20
WHERE SalesOrderID=57024 
AND SalesOrderDetailID=60591 
 
COMMIT TRAN

Let’s now rewrite one of the transactions in a way that when a deadlock is detected, the transaction is rerun.

DECLARE @deadlockretries INT
-- specify number of retries
SET @deadlockretries = 5
-- run the code untill all retries are done
WHILE ( @deadlockretries > 0 ) 
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION
            -- place Deadlock (victim) code here
            UPDATE Person.Person SET Suffix='Mr.' WHERE BusinessEntityID=1 
            WAITFOR DELAY '00:00:02'
            UPDATE Sales.SalesOrderDetail 
            SET OrderQty=20
            WHERE SalesOrderID=57024 
            AND SalesOrderDetailID=60591 
             
            -- If no deadlock, get out of the loop 
            SET @deadlockretries = 0 
            COMMIT TRANSACTION
        END TRY
        BEGIN CATCH 
        -- it's a deadlock exception - 1205 specifies deadlock exception.
            IF ( ERROR_NUMBER() = 1205 ) 
                BEGIN
                    Print 'Deadlock Occured - Retrying the transaction!!!'
                    SET @deadlockretries = @deadlockretries - 1 
                END
        -- not a deadlock exception
            ELSE
                BEGIN
                    DECLARE @ErrorMessage NVARCHAR(4000) 
                    DECLARE @ErrorSeverity INT
                    DECLARE @ErrorState INT
 
                    SELECT  @ErrorMessage = ERROR_MESSAGE() ,
                            @ErrorSeverity = ERROR_SEVERITY() ,
                            @ErrorState = ERROR_STATE() 
                    SET @deadlockretries = 0 
                END
   
            IF XACT_STATE() <> 0 
                ROLLBACK TRANSACTION
        END CATCH 
    END

It’s pretty simple. At first we specify number of retries needed and start a while loop for the retries.  Begin a transaction and put in the deadlock code. If the transactions runs successfully first time without any error, set @deadlockretries = 0  and get out of the loop. If the transaction errors out, it will get into the catch statement. In their, if the error number is 1205 which specifies a deadlock the transaction will run again else if it’s some other error stop the loop, output the error and rollback the transaction. Replace the query in connection 2 with the one specified above and re run the queries in connection 1 and 2 in parallel. It will give result as shown below.

The above result shows that the transaction in connection 1 first gets into a deadlock situation; however it runs in the second try because the transaction in connection 2 has already finished execution.

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook