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
SQL Server Head Blocker
Blocking in SQL servers happens when a connection to SQL server blocks one or more query, and another connection to SQL server requires a conflicting lock type on query, or query locked by the primary connection which leads to the another connection waiting until the primary connection releases its locks. One connection can block another connection, A few seconds of blocking is normal and avoidable. But an excessive amount of blocking can cause connections (applications or user) to attend extensive periods of your time, no matter whether or not they are from identical application or separate applications on different client computers.
The sp_who and sp_who2 commands are older commands to show all current sessions. The DMV sys.dm_exec_requests returns more data in a result set that is easier to query to check blocking in sql server. Run this sample query to find blocking sessions in sql server.
SELECT
session_id,
start_time,
[status],
command,
blocking_session_id,
wait_type,
wait_time,
open_transaction_count,
transaction_id,
total_elapsed_time,
Definition = CAST(text AS VARCHAR(MAX))
FROM
SYS.DM_EXEC_REQUESTS
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
Refer to the sys.dm_exec_requests and reference the blocking_session_id column. When blocking_session_id = 0, a session isn't being blocked. While sys.dm_exec_requests lists only requests currently executing, any connection (active or not) will be listed in sys.dm_exec_sessions. Execute the below query to know the session details of blockings. Keep in mind to be returned by sys.dm_exec_requests, the query must be actively executing with SQL Server.
SELECT * FROM SYS.dm_exec_sessions WHERE session_id IN
(
SELECT session_id
FROM SYS.DM_EXEC_REQUESTS
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id != 0
)
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