Find deadlock in SQL Server

Kailash Chandra Behera | Monday, November 02, 2020

Introduction

A common issue with SQL Server is deadlocks. When a process stuck in deadlock, it prevents other process to execute for an infinite time or until forcefully that is killed. Here in this article we will see how to find out the deadlock process, see the details of the process and how to kill it using simple SQL query.

What is SQL Server DeadLock?

A deadlock occurs when two or more processes are competing for exclusive access to a resource but is unable to obtain exclusive access to it, because the other process is preventing it. The results in a standoff where neither process can proceed.

Getting Started

A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward. When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process or processes can move forward.

By default when this occurs, your application may see or handle the error, but there is nothing that is captured in the SQL Server Error Log or the Windows Event Log to let you know this occurred. The error message that SQL Server sends back to the client is like the following:

 Msg 1205, Level 13, State 51, Line 3  
 Transaction (Process ID xx) was deadlocked on {xxx} resources with another process   
 and has been chosen as the deadlock victim. Rerun the transaction.  

Find SQL Server DeadLock

Below are the SQL stuffs which helps to find out the deadlock in SQL, view the view the deadlock process and kill the SQL server deadlock.

SP_WH2

This store procedure provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

When you execute the store procedure, the result set of sp_who2 will give a list of processes which contains a column named BlkBy, this represents the SPID, that is currently stopping the SPID in the row.

If you see a number there in the BlkBy column then that is the process id that is locked, now you can find out more about what it was doing by running and inputbuffer command for the moment we’ll pretend that my locked process is 68 then I can see what it was doing by typing.

sp_who2 example
 SP_WHO2 ACTIVE  

To list out active processes write above SQL query on SQL Query writter and press F5 button. the results will be appearn in reasult window.

dbcc inputbuffer

This provides information about the last statement executed by SPID. This helps us to find out the SQL query which causes the deadlock in SQL.

dbcc inputbuffer example
 dbcc inputbuffer(68)  

Kill Command

Ends a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has much work to undo, the KILL statement may take some time to complete. The process takes longer to complete particularly when the process involves rolling back a long transaction.

KILL ends a normal connection, which internally stops the transactions that are associated with the specified session ID. At times, Microsoft Distributed Transaction Coordinator (MS DTC) might be in use. If MS DTC is in use, you can also use the statement to end orphaned and in-doubt distributed transactions.

Kill command example
 KILL 68  

Summary

In the above of this blog, we learn how to detects and kills deadlock in SQL server. I hope you have enjoyed it a lot.

Thanks