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