How to interpret deadlock xml

Question

Here are four questions. There is no relevant information in the official documents, so I am testing and summarizing the law by myself, but I am not sure whether it is always correct, so I have come to consult you. If you have detailed information, you are more than welcome to share with me.

1. What is sql in inputbuf? It appears to be the full text of the <frame>sqlhandle at the bottom
2. What is the bottom <frame>? It appears to be the source of the owner lock
3. What is the top <frame>? It appears to be the source of the waiter lock
4. What is the middle <frame>? It seems to be the task that takes place between the top and the bottom, but it is incomplete

This is an example of a deadlock I tested6.xml

Answer 1

Hi @博雄 胡

What is sql in inputbuf?

When analyzing a SQL Server deadlock, the inputbuf element in the deadlock XML provides crucial information. It describes the event type and the exact SQL script that was being executed at the time of the deadlock. Essentially, it shows the SQL statement that each process involved in the deadlock was running.

What is the bottom <frame>

When analyzing a SQL Server deadlock, the <frame> element in the deadlock XML provides information about the call stack of the process involved in the deadlock. Each <frame> element represents a single call in the call stack, and the bottom <frame> is the initial call that led to the deadlock situation.

The bottom <frame> is crucial because it shows the starting point of the execution path that eventually resulted in the deadlock. By examining this frame, you can identify the specific SQL statement or stored procedure that initiated the process, which can help you understand the root cause of the deadlock and take appropriate action to resolve it.

What is the top <frame>?

The top <frame> is the most recent call that led to the deadlock situation.

The top <frame> is crucial because it shows the last executed SQL statement or stored procedure before the deadlock occurred. By examining this frame, you can identify the specific SQL statement or stored procedure that was running at the time of the deadlock, which can help you understand the immediate cause of the deadlock and take appropriate action to resolve it

What is the middle <frame> 

The middle <frame> is one of the intermediate calls that occurred between the initial call (bottom frame) and the most recent call (top frame).

The middle <frame> is important because it shows the sequence of calls that led to the deadlock situation. By examining this frame, you can understand the progression of the execution path and identify any patterns or issues that contributed to the deadlock

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.