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.

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

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.

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook