Skip Navigation LinksHome > Categories > Code from a Category

Understanding and handling SQL Server Transaction Deadlocks.

User Name: codelecturer
Name: Mike Chauhan
Contact Me:
Home Page:
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 3520
Add Date: 01/02/2012
Deadlock occurs 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 take 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 lock that transaction Y needs to complete its tasks and vice versa. So in this scenario neither a transaction can complete until the other transaction release

Transaction deadlock situation:


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 in two different query window and run then simultaneously. Consequently what happen 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: User can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process. But it is recommended that we should provide solution for handling deadlocks is to find the problem in our query code and then modify our processing to avoid deadlock situations.

Let us rewrite our transaction query

TRY /CATCH can be used for error handing 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 for other transaction to complete within 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 below result.

Tips to avoid deadlocks:

1. Minimize the size of transaction and transaction times.
2. Always access server objects in the same order each time in application.
3. Avoid cursors, while loops or process which requires 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 deadlock, we have to manually configure sql server to track deadlock in sql server error log. So when lets us see below what information we need to provide sql server for this.

Hope you enjoyed reading

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.