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.