x

[Closed] TIP: How to read a deadlock graph?

Hello,

There was one deadlock instance on Production and upon request our DBA provided us with the XDL file. But I am not aware on how to read and understand it.

I require tips in how to read a deadlock graph. Please help me with links, documentation and handy advice on what to lookout for when reading such XDL files.

I have zipped and attached the XDL file with this post. Many thaks in advance.

alt text XDL file attachment

xdl.png (48.3 kB)
more ▼

asked Apr 10, 2015 at 11:31 AM in Default

avatar image

nidheesh.r.pillai
9.5k 23 59 49

added XDL as PNG to help readability

Apr 14, 2015 at 02:04 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

The question has been closed May 04, 2015 at 11:55 AM by nidheesh.r.pillai for the following reason:

The question is answered, right answer was accepted


1 answer: sort voted first

In your particular deadlock,

The victim was process 251 (on the right with the big X) and had an intent-exclusive lock (IX) (bottom middle) on a page in database 11, file 3, table OptionNavigator.dbo.OptionGrant. Process 251 was looking to obtain an update lock (U) on a key on the PK_AccountMobilityATBCustomAllocation index. (top middle).

Process 141 has an exclusive (X) lock on the HoBT ID for the PK_AccountMobilityATBCustomAllocation index. This process was requesting a shared lock (S) on the page in database 11, file 3.

I would imagine that process 141 is doing a data modification of some type to that particular index. Process 141 has an exclusive lock on a resource that process 251 wants. Process 251 has an intent exclusive lock (IX) on a resource that process 141 wants. Since neither can finish their transaction without the desired resource, one is chosen as the deadlock victim.

Unless specified otherwise (adjusting the dead lock priority), SQL Server will choose the least cost of the two as the victim.

I'd start with these blogs:

Gail Shaw: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

Jonathan Kehayias: http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx

Microsoft (Lock Compabilibilyt) http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx

Hope that helps!

more ▼

answered Apr 10, 2015 at 12:08 PM

avatar image

JohnM
14.2k 3 7 14

(comments are locked)
10|1200 characters needed characters left

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2198
x64
x3

asked: Apr 10, 2015 at 11:31 AM

Seen: 3551 times

Last Updated: Apr 14, 2015 at 02:04 PM

Copyright 2018 Redgate Software. Privacy Policy