By: Jeremy Kadlec | Updated: 2007-03-27 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Locking and Blocking
Problem
In specific SQL Servers databases, I have deadlock problems that often result in significant performance degradation. When the deadlocks occur frequently in my environment, the deadlocks often become a performance killer for the overall application. If the deadlocks become severe enough, I need to drop everything I am doing to manage the issue in order for the users to stay productive in the business application. If I could manage the deadlocks in an easier manner, that would be good motivation to move to SQL Server 2005. Does SQL Server 2005 have any new deadlock related features?
Solution
Let's first define a deadlock. A deadlock is a situation where 2 spids have data locked and cannot release their lock until the opposing spid releases their lock. Depending on the severity of the deadlock, meaning the amount of data that is locked and the number of spids that are trying to access the same data, an entire chain of spids can have locks and cause a number of deadlocks, resulting in a performance issue.
From SQL Server 2000 to 2005, the DEADLOCK_PRIORITY option has slightly changed offering some additional functionality. The DEADLOCK_PRIORITY option dictates how the spids are handled when a deadlock occurs. Before we jump into that new functionality, a few simple steps should be taken to address common deadlock issues:
- Understand which tables are involved in the deadlocks
- Understand which pieces of code correspond to the deadlock and which users execute those pieces of code
- Understand how the application is affected when a deadlock occurs
- Determine the length of the transaction (where an explicit BEGIN TRAN and COMMIT or ROLLBACK occurs) and if it can be shortened
- Determine if the tables are highly fragmented and causing unnecessary page reads to complete the transaction
- Determine if the tables can be redesigned to prevent deadlocking
- At times, denormalizing tables can reduce the number of deadlocks
- Determine if additional or less indexes would improve the deadlocking
- Additional indexes could be needed if table scanning is occurring
- Less indexes could be needed if unneeded indexes (not used in the query plan for any queries) are updated during INSERT, UPDATE and DELETE statements
- Determine if the code accesses the tables in the same order and with a similar query plan
- Determine if the code can be changed to lessen the number of locks or the type of lock used against the tables
- For example, see if the code can be modified to minimize the lock level SQL Server selects (i.e. row vs. page, page vs. extent, extent vs. table)
- In addition, ensure unnecessary locking hints are not included in the code
The DEADLOCK_PRIORITY Option
As indicated earlier, the DEADLOCK_PRIORITY option dictates how the spids are handled when a deadlock occurs. The default deadlock priority in SQL Server 2000 and 2005 is NORMAL. SQL Server 2000 has two other settings of LOW and HIGH, whereas SQL Server 2005 has 21 settings based on a numeric priority. With this being said, SQL Server scans for deadlocks on a regular basis (i.e. every 5 seconds in SQL Server 2005) and the following logic is used to determine the victim of a deadlock:
- If the DEADLOCK_PRIORITY for one of the spids is lower than the other, the lower priority spid is chosen as the victim
- If the DEADLOCK_PRIORITY is the same for each spid involved, the spid that is less expensive/costly to rollback is chosen as the victim.
During this scanning process is where the DEADLOCK_PRIORITY comes in handy. This configuration is set at run time, not at parse time. So once you have done the analysis for your application you will know which portions of code will either succeed or fail based on the DEADLOCK_PRIORITY. Below is an example of setting the DEADLOCK_PRIORITY in SQL Server 2005 for two different pieces of code to ensure you are able to control the outcome of the deadlock by ensuring the INSERT completes and the SELECT is the deadlock victim:
| SET DEADLOCK_PRIORITY LOW GO EXEC dbo.spGetAllOrders GO |
SET DEADLOCK_PRIORITY HIGH GO EXEC dbo.spInsertOrders GO |
Next Steps
- As you are faced with deadlock issues, take the time to analyze the issue and determine how the DEADLOCK_PRIORITY option can assist particular pieces of code.
- Check out these additional resources:
- Check out these related MSSQLTips.com:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-03-27
