SET DEADLOCK_PRIORITY

This is the third post if the SET statement series.

You can set the DEADLOCK_PRIORITY from labels or from numeric values. Numeric ranges are from -10 to 10 while labels are HIGH, LOW or NORMAL and LOW maps to -5, NORMAL to 0, and HIGH to 5 of the numeric scale.

This option is available from SQL Server 2005.

To create a deadlock for testing purposes you can execute the below TSQL script:

-- 1) Create Objects for Deadlock Example
USE TempDB
GO
CREATE TABLE foo (col1 INT)
INSERT foo SELECT 1
CREATE TABLE bar (col1 INT)
INSERT bar SELECT 1
-- 2) Run in first connection and in my server this connection was SPID 59

BEGIN TRAN
UPDATE foo SET col1 = 1

-- 3) Run in second connection and in my server this connection was SPID 62
BEGIN TRAN
UPDATE bar SET col1 = 1
UPDATE foo SET col1 = 1
-- 4) Run in first connection
UPDATE bar SET col1 = 1

When the above query is executed, as expected a deadlock occurred and it happened to be the SPID 59 :

Msg 1205, Level 13, State 45, Line 1 Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Let us assume that I do not want 59 to become the victim and instead have another connection (62) become the victim. I will add the following SET command to my SPID 59 :

SET DEADLOCK_PRIORITY HIGH;
GO
UPDATE bar SET col1 = 1

Now when I run the first TSQL script SPID 62 will become a victim while SPID 59 will be completed:

Msg 1205, Level 13, State 45, Line 3 Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Previous articles of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT