Facing Deadlocks

Question

There are deadlocks happening. As i enabled TF 1222, I can see the XML format. How to resolve it, please help

Answer 1

Hi ChaitanyaKiranBuduguru-7986,

Use the SQL Server system stored procedures sp_who and sp_lock, you can view the lock situation in the current database, and then you can view which resource is locked according to the objectID.
Check that the spid is in the wait state, and then use "kill spid". Of course, this is only a temporary solution. We cannot always troubleshoot deadlocks and Kill sp in the user's production environment when we encounter deadlocks. We should consider how to avoid deadlocks.
Note: You can also use other system management views, the purpose is to find out the blocked process.
Then kill this process.

As for XML file, It is very easy to find out what action is currently being performed, which can be used to analyze the real cause of deadlock.
Please refer to this blog:
https://www.sqlshack.com/understanding-the-xml-description-of-the-deadlock-graph-in-sql-server/
The XML description of the deadlock provides a ton of additional information which makes troubleshooting deadlocks a lot easier. So instead of just using the deadlock graph, be sure to also look at the XML description of the graph.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.