Post
Ask Question

Understanding and handling SQL Server Transaction Deadlocks

Introduction

 
Deadlocks occur when two users have locks on separate objects and each user wants a lock on the other's object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
 

Deadlock due to transaction

 
Let us use a scenario where a transaction X attempts to update table 1 and subsequently read/update data from table 2. At the same time there is another transaction Y which is trying to update table 2, and subsequently read /update data from table 1. In this scenario, transaction X holds a lock that transaction Y needs to complete its tasks and vice versa. So in this scenario neither transaction can complete until the other transaction is release.
 

Transaction deadlock situation


Transaction X
  1. BEGIN TRAN    
  2. UPDATE EMPLOYEE SET EMPLOYEENAME='NAYAN VISHAL' WHERE EMPLOYEEID=111    
  3. WAITFOR DELAY '00:00:05'    
  4. UPDATE SALARY SET BASIC= 5000 WHERE EMPLOYEEID=111    
  5.     
  6. COMMIT TRAN   
Transaction Y
  1. BEGIN TRAN  
  2.   
  3. UPDATE SALARY SET HRA=8500 WHERE EMPLOYEEID=111  
  4. WAITFOR DELAY '00:00:05'  
  5. UPDATE EMPLOYEE SET EMPLOYEENAME='BUMPY' WHERE EMPLOYEEID=111  
  6.   
  7. COMMIT TRAN  
Result
 
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 
 
Reason for above deadlock
 
What we did is we copied these two transactions to two different query windows and run them simultaneously. Consequently what happened is that Transaction X locks and updates Employee table whereas transaction X locks and updates Salary table. After a delay of 20 ms, transaction X looks for the lock on Salary table which is already held by transaction Y and transaction Y looks for lock on Employee table which is held by transaction X. So both the transactions cannot proceed further; the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.
 

How deadlock is resolved

 
The user can choose which process should stop to allow another process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometimes, it chooses the process running for a shorter period than another process. But it is recommended that we should provide a solution for handling deadlocks by finding the problem in our query code and then modify our processing to avoid deadlock situations.
 
Let us rewrite our transaction query.
 
Transaction X
  1. RETRY:  
  2. BEGIN TRAN  
  3. BEGIN TRY  
  4.       UPDATE EMPLOYEE SET EMPLOYEENAME='NAYAN VISHAL' WHERE EMPLOYEEID=111  
  5.       WAITFOR DELAY '00:00:10'  
  6.       UPDATE SALARY SET BASIC= 5000 WHERE EMPLOYEEID=111  
  7.       COMMIT TRAN  
  8. END TRY  
  9. BEGIN CATCH  
  10.       PRINT 'Rollback Transaction'  
  11.       ROLLBACK TRANSACTION  
  12.       IF ERROR_NUMBER() =1205 -- DEADLOCK NUMBER  
  13.       BEGIN  
  14.             WAITFOR DELAY '00:00:00.05'  
  15.             GOTO RETRY  
  16.       END CATCH  
Transaction Y
  1. RETRY:  
  2. BEGIN TRAN  
  3. BEGIN TRY  
  4.       UPDATE SALARY SET HRA=8500 WHERE EMPLOYEEID=111  
  5.       WAITFOR DELAY '00:00:10'  
  6.       UPDATE EMPLOYEE SET EMPLOYEENAME='BUMPY' WHERE EMPLOYEEID=111  
  7.       COMMIT TRAN  
  8. END TRY   
  9. BEGIN CATCH  
  10.       PRINT 'Rollback Transaction'  
  11.       ROLLBACK TRANSACTION  
  12.       IF ERROR_NUMBER()=1205  
  13.       BEGIN  
  14.             WAITFOR DELAY '00:00:00.05'  
  15.             GOTO RETRY  
  16.       END  
  17. END CATCH  
Explanation
 
TRY /CATCH can be used for error handling within the transaction. If there is code failure with TRY block, the control comes in CATCH statement which ultimately rollbacks the transaction. Here we are checking if the error code is 1205 which corresponds to transaction, then transaction waits for 5 ms. This delay is necessary to allow another transaction to complete within the delay duration and release the lock from the table. So after the delay, the transaction starts executing from the beginning using RETRY statement.
 
Result:
 
If we run these two Trans statement at the same time, we will get the result below.
 
(1 row(s) affected)
Rollback Transaction
 
(1 row(s) affected)
 
(1 row(s) affected)
 

Tips to avoid deadlocks

  1. Minimize the size of transactions and transaction times.
  2. Always access server objects in the same order each time in an application.
  3. Avoid cursors, while loops or processes that require user input while it is running.
  4. Reduce lock time in application.
  5. Use query hints to prevent locking if possible (NoLock, RowLock)
  6. Select deadlock victim by using SET DEADLOCK_PRIORITY.
How to keep track of DEADLOCK in SQL Server: By Default SQL Server doesn't keep track of DEAD LOCKs, we as a DBA need to request SQL Server to keep track of DEAD LOCKs in SQL Server Error Log.
 
SQL Server by default doesn't keep track of deadlocks; we have to manually configure SQL Server to track deadlocks in the SQL Server error log. So let us see below what information we need to provide SQL Server for this.
  • What to capture?
  • Where to capture?
  • Define scope, from where you want to capture?
  1. DBCC TRACEON (3605,1204,1222,-1)  
  2. /*  
  3. -- where   
  4. -- What to Capture?  
  5. --------------------  
  6.   -- (1204) = Capture Deadlock Events.   
  7.   -- (1222) = Capture Deadlock Events with detailed Information (works only with SQL 2005 and higher version)   
  8. -- Where to capture ?  
  9. -------------------  
  10.    -- (3605) = Capture the selected Events in the SQL Server error log.   
  11. -- What is the Scope of Capture   
  12. --------------------------------  
  13.     -- (-1) = Switches on the specified trace flags globally, that means do it globally, for all users/sessions  
  14. */  
This won't work if an instance of SQL Server is not working, so when we restart SQL Server, we need to do this again. To configure this permanently follow the instructions below:
  • Create a SQL Agent job and put this code into and schedule that job to when the Agent starts
  • You need to enable this trace flags at SQL Server startup parameter, by specifying -T1222; -T3605; in the startup parameter.

    1. SQL Server Configuration Manager -> SQL Server Services -> right-click SQL Server () -> Properties -> Advanced TAB -> Startup Parameters box -> type the parameters separated by semicolons (;).
Hope you enjoyed reading.
 
Cheers!


Similar Articles