THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

The Anatomy of a Deadlock

A common question of the forums is "How do I stop these deadlocks?"  I wrote a small reference article on the SQL Examples Site for the forums for how to trap deadlocks using trace flags and Madhu Nair wrote an article for how to trap them with SQL Profiler:

Troubleshoot Deadlocking in SQL Server.
Troubleshoot Deadlocking in SQL Server 2005 using Profiler.

Most people seem to understand a UPDATE/INSERT, UPDATE/DELETE, INSERT/INSERT, DELETE/DELETE deadlocks pretty well, and the books online cover them pretty good.  However, commonly I see where deadlocks occur with a INSERT/SELECT, UPDATE/SELECT, DELETE/SELECT process that is a bit more complex.  The following image should provide a bit more explanation as to what is happening with this kind of deadlock.

 

This is a specific kind of deadlock and is caused by a nonclustered index that forces a key lookup to the clustered index.  To demonstrate this kind of structure consider the following:

use tempdb
go
create table KeyLookupDeadlock
(rowid int identity primary key,
firstname varchar(30),
lastname varchar(30),
dateofbirth datetime,
favoritecolor varchar(10))
go
create nonclustered index ix_KeyLookupDeadlock_firstname
on KeyLookupDeadLock (firstname)
go
insert into keylookupdeadlock select 'jon', 'kehayias', '07/29/2008', 'blue'
insert into keylookupdeadlock select 'mickey', 'mouse', '07/28/2008', 'green'
insert into keylookupdeadlock select 'minnie', 'mouse', '07/24/2008', 'yellow'
insert into keylookupdeadlock select 'donald', 'duck', '07/23/2008', 'fusia'
go 150000
insert into keylookupdeadlock select 'jim', 'fisher', '07/28/2008', 'red'
go
alter index all on keylookupdeadlock rebuild
go
This will create a table that has enough rows on most systems to cause a key lookup for the following query:
select firstname, lastname, dateofbirth, favoritecolor
from
keylookupdeadlock
where
firstname = 'jim'

image

Keeping in mind that this is an overly simplistic example, what will happen to cause the deadlock is that two SPIDS will start within microseconds of each other, one issuing the SELECT, and the other attempting to INSERT a new Row.  The SELECT immediately takes a shared lock on the NonClustered Index and the INSERT immediately takes a Exclusive Lock on the Clustered Index.  For the SELECT to complete, it will need to take a shared lock on the clustered index to get the additional columns in the SELECT list, but it will be blocked.  At the same time, to complete the INSERT operation will require an Exclusive lock on the nonclustered indexes on the table, which is incompatible with the existing shared lock held by the SELECT operation, so both processes will be blocked waiting on the other to release its lock.  This is where the deadlock occurs.

To resolve this, the nonclustered index can be dropped and replaced with a covering index that uses the INCLUDE option to cover the query completely:

create nonclustered index ix_KeyLookupDeadlock_firstname_included
on
KeyLookupDeadLock (firstname)
include
(lastname, dateofbirth, favoritecolor)

The resulting execution plan shows that the Key Lookup no longer happens, which will revent the deadlock from occuring:

 

For further information on Lock compatibilities see:

Lock Compatibility (Database Engine)

Published Wednesday, July 30, 2008 1:50 PM by Jonathan Kehayias

Comments

 

stephen_or_doug said:

Hi there - a quick favour to ask. Your article "The Anatomy of a Deadlock" looks good to me, and may help to explain some of the deadlocks that we see very infrequently on one of our production servers. The post has moved to "http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx" - but unfortunately the images don't seem to have gone with it! Any chance you could see if you can repost it with images intact.

Thanks a lot!

June 1, 2009 11:06 AM
 

Jonathan Kehayias said:

I've updated the image pointers on the post.  Let me know if it fails to render properly for you.

June 1, 2009 11:33 AM
 

stephen_or_doug said:

Thanks very much for your speedy reply... The images still don't seem to work for me, but in the meantime, I found an alternative copy at:

"http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DeadlockAnatomy" which does work.

My situation is very similar but different... The production database  application has been running for about 8 years now - started on sql 6.5, then 7.0 then 2000 and now 2005. (Now on it's 4th hardware platfomr!) We've only seen this issue in the last 6 months or so - symptomatic of the increased DB size, an inevitable transaction load increase. We've only seen it a couple times, and although we have tried, seem to be unable to reproduce it in a test environment.

I have a select statement from a view (which inner joins two tables - primary key clustered index on the "parent" table, non-clustered index on the "Child" table.) The Select statement searches non-clustered indexed columns from both tables, and returns some columns from each table (this is of course via the view).

This results in a execution plan which creates an index scan on the non-clustered index on the "child" table, a key lookup on the clustered PK on the "child" table, and an index seek on the "parent" table.

The select query is deadlocking with an update statement on the "child" table...

Your article considered a similar scenario with only one table, and the fix that you posted in your article is to avoid the key lookup altogether by creating an alternative non-clustered index that included the data to be returned in the select statement.

Do you have any guidance for what sort of approach to take in this case? One problem that we have is that some of our customers still run on SQL 2000 - so we've avoided the use of the INCLUDE option for indexes up until now. Indexed views scare me a little - caused us a bit of grief in the past.

If you have read this far - then thank you very much for your time! Any ideas as to where to check for more information on this sort of thing would would be great!

June 1, 2009 12:16 PM
 

stephen_or_doug said:

Oops I tell a lie - I can see the images now - took a ctrl-f5 in Google Chrome to refresh them. Thanks!

June 1, 2009 12:24 PM
 

Jonathan Kehayias said:

Stephen,

You can still produce an appropriate covering index for SQL 2000 to prevent the deadlock, but without a whole lot of additional information like the deadlock graph, contributing query statements and table structures, I couldn't begin to offer information as to how.

June 3, 2009 10:06 AM
 

Vikas Kalra said:

Hi Jonathan,

Apologies for the double posting (here and the MS site as well), just wanted to get your attention.

The post is very informative, thanks for the same. But I have run into some trouble.

I have three versions of SQL servers -

1. SQL 2005 SP1 Developer Edition

2. SQL 2005 (64 bit) Enterprise Edition SP2

3. SQL 2008 (64 bit) Developer Edition SP1

I am able to simulate the above on - "SQL 2005 SP1 Developer Edition" alone. In the other environments, I am getting a totally different query plan when I do an Insert and Update in different windows (both running under different Transactions as well). Don't see any deadlock.

I was hoping to see similar results on the other versions as well, but this is a bit strange. Any ideas why?

Thanks for your time,

Cheers,

Vikas

November 12, 2009 4:09 AM
 

Jonathan Kehayias said:

Vikas,

Send me a contact by clicking the Email link above on the right hand side and I'll look at it.  The above deadlock is reproducible on any of the versions of SQL Server, so it is likely that you aren't getting the necessary timing to cause it to trigger.  You can't run the two statements manually, generally you have to run them in loops on separate connections similar to the example code in my article on SQLServerCentral.com:

http://www.sqlservercentral.com/articles/deadlock/65614/

November 12, 2009 12:10 PM
 

Prasad said:

Jonathan,

This is a great article, very clear and to the point. Knowing the internal workings of SQL server is very interesting, but does the developer need to be aware of these idiosyncrasies of the DB engine that are causing the deadlocks? I feel that SQL server should be able to do better. I am new to SQL Server env and am somehow forced to understand the internal workings of the SQL server. Where as in all my previous projects where we mostly used Oracle, we just had to deal with transactions/isolation levels and never had to bother about different types of locks and other internals.

Thanks for sharing your insights.

Prasad.

January 4, 2011 12:59 PM
Anonymous comments are disabled

This Blog

Syndication

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