Recently, I ran into a series of deadlocks that had multiple victims in each deadlock. In other words, SQL Server chose more than one spid to terminate in resolving a deadlock.
Maybe I need to brush up my web search skills, but I have not found much information on multi-victim deadlocks. About the only information I could find is an excellent blog post by SQL Server MVP Jonathan Kehayias from more than two years ago. And in his post, Jonathan provided a demo to create a multi-victim deadlock.
The multi-victim deadlocks that I ran into appear to be a simpler case because it involves only a single table and does not require setting the deadlock priority to different levels. In addition, I think this is a rather prototypical multi-victim deadlock in that more than one victim must be killed in order for the deadlock to be removed. Here are the steps to create such a multi-victim deadlock.
First, create a table. Any table will do. Let’s make it real simple:
create table junk(i int)
Then, open three connections to the SQL Server 2008 instance and run the following in each of the three connections in tempdb:
select * from junk with (tablock, holdlock)
Note that it is tablock, not tablockx, in the hint.
Now, in each of the three connections, run this UPDATE statement:
set i = 1
You must run the above statement in all three connections fast enough to see a multi-victim deadlock. If you run it in two connections and wait, you’ll get a deadlock between those two connections before you have a chance to run the UPDATE statement in the third connection.
Essentially, you need to run the UPDATE statement in the third connection before SQL Server’s deadlock detection mechanism has a chance to run. And by the time the deadlock detection mechanism runs, it’ll see that two connections must be killed to get rid of the deadlock. Killing any one of the connections would still leave the deadlock in place.