How to interpreting the deadlock graph file generated by the SQL Server cartridge?
A deadlock is a situation where one request is waiting for another resource to complete the process, meantime the requested resource is with another request which is waiting for some resources with the first request. Both the requests waiting for the completion of the other one. After a specified time, the system will kill one request and process the second request.
When SQL Server determines that a deadlock has occurred, the first event that denotes this, is the "Lock:Deadlock Chain" event.
Once SQL Server detects a deadlock, it picks a winning and losing transaction. The "SQL:BatchCompleted" event that immediately follows the "Lock:Deadlock" event is the transaction killed and rolled back. The following "SQL:BatchCompleted" event is the event picked as the winner and successfully runs.
The deadlock code can be opened in any XML editor. By looking at the XML code, you can determine which statement is terminated as deadlock victim, all statements involved in this deadlock, and if it is in a procedure, what the name of procedures involved are, etc.
The left oval on the deadlock graph, with the blue cross, represents the transaction chosen as the deadlock victim. Moving the mouse pointer over the oval, a tooltip appears. This oval is also known as a "Process Node" as it represents a process performing a specific task, such as an INSERT, DELETE or UPDATE.
The right oval represents the successful transaction. Moving the mouse pointer over the oval also, a tooltip appears. This oval is also known as a "Process Node".
The two middle rectangular boxes are called "Resource Nodes", representing a database object, such as a table, row or an index. These are the two resources that the two processes were conflicting over. In this case, both "Resource Nodes" represent indexes which each process was trying to get an exclusive lock on.
The arrows pointing to and from the ovals and rectangles are called "Edges". "Edges" represent a relationships between processes and resources.
There are a number of terms listed inside the "Resource Node".
The winning transaction does not have a blue cross through it and provides the exact Transact-SQL code that was running to cause the deadlock. This is very useful information in that it allows us to trace the event to specific problematic code. It also tells us that this "Process Node" has an exclusive lock on the top "Resource Node" (the X represents an exclusive lock) and it tells us that it has requested another exclusive lock on the bottom "Resource Node".
On the left side of a deadlock graph is the other "Process Node". Like the winning "Process Node", this node tells us:
The "Resource Nodes" tell us the resources and the transactions were conflicting over and contain the following information.