SET DEADLOCK_PRIORITY – SQL Server

Problem & Introduction:

When an instance of the Microsoft SQL Server Database Engine chooses a transaction as a deadlock victim, it terminates the current batch, rolls back the transaction, and returns error message 1205 to the application.

Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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.

One more time .. 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.

Solution:

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 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

Syntax of “DEADLOCK_PRIORITY”:

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }

<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

Arguments:

• LOW

Specifies that the current session will be the deadlock victim if it is involved in a deadlock and other sessions involved in the deadlock chain have deadlock priority set to either NORMAL or HIGH or to an integer value greater than -5. The current session will not be the deadlock victim if the other sessions have deadlock priority set to an integer value less than -5. It also specifies that the current session is eligible to be the deadlock victim if another session has set deadlock priority set to LOW or to an integer value equal to -5.

• NORMAL

Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to HIGH or to an integer value greater than 0, but will not be the deadlock victim if the other sessions have deadlock priority set to LOW or to an integer value less than 0. It also specifies that the current session is eligible to be the deadlock victim if another other session has set deadlock priority to NORMAL or to an integer value equal to 0. NORMAL is the default priority.

• HIGH

Specifies that the current session will be the deadlock victim if other sessions involved in the deadlock chain have deadlock priority set to an integer value greater than 5, or is eligible to be the deadlock victim if another session has also set deadlock priority to HIGH or to an integer value equal to 5.

• <numeric-priority>

Is an integer value range (-10 to 10) to provide 21 levels of deadlock priority. It specifies that the current session will be the deadlock victim if other sessions in the deadlock chain are running at a higher deadlock priority value, but will not be the deadlock victim if the other sessions are running at a deadlock priority value lower than the value of the current session. It also specifies that the current session is eligible to be the deadlock victim if another session is running with a deadlock priority value that is the same as the current session. LOW maps to -5, NORMAL to 0, and HIGH to 5.

• @deadlock_var

Is a character variable specifying the deadlock priority. The variable must be set to a value of ‘LOW’, ‘NORMAL’ or ‘HIGH’. The variable must be large enough to hold the entire string.

• @deadlock_intvar

Is an integer variable specifying the deadlock priority. The variable must be set to an integer value in the range (-10 to 10).