SQL Server Deadlock Priority Configuration

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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:

1. If the DEADLOCK_PRIORITY  for one of the spids is lower than the other, the lower priority spid is chosen as the victim
2. 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:

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:
  • SQL Server 2005
    • SET DEADLOCK_PRIORITY (Transact-SQL)
    • Detecting and Ending Deadlocks
    • Customizing the Lock Time-Out
  • SQL Server 2000
    • SET DEADLOCK_PRIORITY
    • Detecting and Ending Deadlocks
    • Customizing the Lock Time-out
• Check out these related MSSQLTips.com:
  • Finding and troubleshooting SQL Server deadlocks

Related Tips: More | Become a paid author

Last Update: 3/27/2007

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" Write, edit, and explore SQL effortlessly with SQL Prompt. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check consulting services. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....