SQL Server Deadlock Priority Configuration

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

   Syntax:      

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | | @deadlock_var | @deadlock_intvar }

::= { -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.

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

Remarks:

Deadlocks arise when two sessions are both waiting for access to resources locked by the other. When an instance of SQL Server detects that two sessions are deadlocked, it resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the victim is rolled back and deadlock error message 1205 is returned to the client. This releases all of the locks held by that session, allowing the other session to proceed.

Which session is chosen as the deadlock victim depends on each session's deadlock priority:

·         If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim. For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll back.

·         If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.

SET DEADLOCK_PRIORITY is set at execute or run time and not at parse time.

Permission:

Require membership in the public role.

Examples:

The following example uses a variable to set the deadlock priority to LOW.

DECLARE @deadlock_var NCHAR(3);

SET @deadlock_var = N'LOW';

SET DEADLOCK_PRIORITY @deadlock_var;

GO

The following example sets the deadlock priority to NORMAL.

SET DEADLOCK_PRIORITY NORMAL;

GO