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.
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
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:
Microsoft (Lock Compabilibilyt) http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Deadlock/default.aspx
Hope that helps!
answered Apr 10, 2015 at 12:08 PM