Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Deadlock Priority Configuration


By:   |   Last Updated: 2007-03-27   |   Comments (1)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > 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:

  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:

SET DEADLOCK_PRIORITY LOW
GO
EXEC dbo.spGetAllOrders
GO
SET DEADLOCK_PRIORITY HIGH
GO
EXEC dbo.spInsertOrders
GO

Next Steps



Last Updated: 2007-03-27


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 06, 2015 - 11:12:50 AM - sachin waghmare Back To Top

Nice article......


Learn more about SQL Server tools