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.

1_SQL_Server_Deadlock_Resolution_Transaction_Retry

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

5 Comments on “SQL Server Deadlock Resolution : Transaction Retry”

  1. This is a very Informative and useful article.

    This will allow give me a way to avoid deadlock situation.

    But how could we figureout open transaction and kill them if any deadlock occur due to transaction missmatch?

  2. The code
    IF XACT_STATE() 0
    42
    ROLLBACK TRANSACTION

    will rollback the transaction if it remains open after deadlock. Also you can use dbcc opentran to find out open transactions and kill them using kill command.

  3. Ahmad,

    I have 1 confusion,
    I am running below sample code in which I open 1 transaction but not commit/Rollback it.

    BEGIN TRANSACTION
    SELECT 1/1
    dbcc OPENTRAN

    It Gives Me following information:-
    Oldest active transaction:
    SPID (server process ID): 107
    UID (user ID) : -1
    Name : user_transaction
    LSN : (169703:66493:6)

    Start time : Mar 20 2013 6:35:35:597PM
    SID : 0x77e6d54ca44d7c469d07dc87eb012e95
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    In the information in bold returns SPID =107.
    but when i run following query

    SELECT * FROM sys.sysprocesses WHERE dbid= AND hostname=’VIVEK-Host’ AND open_tran != 0

    It is returning me SPID=53
    which these two SPIDs different?
    Where I am doing wrong?

  4. dbcc opentran returns the oldes Active transaction not all active transactions. there might be more than open transactions present.

    SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0

    use above query to find out all open tran

Leave a Reply

Your email address will not be published. Required fields are marked *