question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

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][1] [XDL file attachment][2] [1]: /storage/temp/2358-xdl.png [2]: /storage/temp/2350-deadlockgraph.zip
sql-server-2008deadlockdeadlock-graph
xdl.png (47.1 KiB)
deadlockgraph.zip (1.6 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

added XDL as PNG to help readability
0 Likes 0 ·

1 Answer

JohnM avatar image
JohnM answered
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!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.