Create a Deadlock and Set the Deadlock Victim

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

-- 1) Create Objects for Deadlock Example
USE TEMPDB
CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1
CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1
-- 2) Run in first connection and in my server this connection was SPID 59
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1
-- 3) Run in second connection and in my server this connection was SPID 62
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1
-- 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1

When I ran this, 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 tempdb.dbo.bar SET col1 = 1

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

 DEADLOCK_PRIORITY

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.

For more information on this please refer to http://msdn.microsoft.com/en-us/library/ms186736.aspx

Credits : To create the deadlock I used the script at WardyIt.com : http://wardyit.com/blog/blog/archive/2005/12/12/65.aspx

]]>