DEADLOCK_PRIORITY is used to specify the priority of a session when deadlocked by other session.
The session that has lower DEADLOCK_PRIORITY will be chosen as a victim for deadlock.
Syntax:
[sql]
SET DEADLOCK_PRIORITY value
[/sql]
Value Can Be:
LOW
NORMAL
HIGH
Numeric value from -10 to 10
Example:
To set HIGH DEADLOCK_PRIORITY to a connection:
[sql]
SET DEADLOCK_PRIORITY HIGH
[/sql]
OR
[sql]
SET DEADLOCK_PRIORITY 5
[/sql]
OR
[sql]
DECLARE @A VARCHAR(10)
SET @A = ‘HIGH’
SET DEADLOCK_PRIORITY @A
[/sql]
OR
[sql]
DECLARE @A INT
SET @A = 5
SET DEADLOCK_PRIORITY @A
[/sql]
Note:
Numeric equivalent values of DEADLOCK_PRIORITY.
HIGH = 5
NORMAL = 0
LOW = -5
Applicable to SQL Server 2000,SQL Server 2005,SQL Server 2008,SQL Server 2008 r2,SQL Server 2012