THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Multi-victim deadlocks – a simple example

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:

use tempdb

go
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:

 

begin tran

  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:

update junk

   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.

Published Sunday, October 02, 2011 2:33 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

ManishKumar1980 said:

But Why here deadlock occurs as first Select has already taken Sharelock and second while trying to set updlock found no compatible lock exists and so wait. SO WHY There's DEADLOCKING?

October 24, 2011 12:05 PM
 

Jonathan Kehayias said:

The above example is for SQL blocking - not for deadlock!

December 21, 2012 1:07 PM
 

WayneS said:

Actually, this does generate a multi-victim deadlock. The easiest way to run those three statements fast enough to capture this is to open a New Query from a group of registered servers, where they all connect to the same server. In this way, running the commands will run them concurrently in all of the multiple connections simultaneously.

February 6, 2013 9:59 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement