20 Aug 2019 0 minutes to read Contributors
Your query is waiting for another query and the other query is doing nothing.
A blocking lock occurs when one lock causes another process to wait until the current process is entirely done with the resources.
Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions.
There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:
What most users do is, they kill the query that is blocking your query. Ofcourse this is very bad practice but we also understand that it is not an uncommon thing to do. It would be better to look at your indexes, optimize the resource usage of your query and to automatically avoid this situation from happening.
The following script can be automated via SQLServer Agent Jobs or executed manually to help you kill connections:
/* Functionality ============= Allocate and (optionally) kill blocking locks Applications ============ MSSQL PRERequisites ============= - Set parameter @who_to_kill (can also be empty) VERSION HISTORY Version Date Author Reason ------------------------------------------------------------------ 4 18 dec 17 ak kill statement replaced with select 5 25 apr 18 dr changed from sp_lock to sys.dm_tran_locks Parameters ======= @wait_duration_threshold INT: (Milliseconds that a process is waiting) from -2,147,483,648 to 2,147,483,647 only used in combination with @who_to_kill = 'DEADLOCK' @who_to_kill VARCHAR(10): (identify what process to analyze) 'BLOCKER' - the process that blocks another process 'WAITING' - the process that is waiting until the blocker finishes 'BOTH' - both 'NONE' (default) - do nothing 'DEADLOCK' - Not a real deadlock but 2 processes who are waiting for eachother. it takes into account the parameter @wait_duration_threshold to check for how long either process is waiting for the other @showonly BIT: (show or show and kill the processes identified) 1 - do not execute KILL 0 (default) - only show processes that match @who_to_kill. Logging ======= None Permissions ======= if @showonly = 0 ALTER ANY CONNECTION (inherited via sysadmin or processadmin) AND VIEW SERVER STATE (for sp_lock and sys.dm_os_waiting_tasks) Results ======= result set with blocking locks executed KILL command if @showonly = 0 */ SET NOCOUNT ON DECLARE @who_to_kill VARCHAR(10) = 'NONE' DECLARE @wait_duration_threshold INT = 1 DECLARE @showonly BIT = 0 IF (UPPER(ISNULL(@who_to_kill, '')) NOT IN ('BLOCKER', 'WAITING', 'BOTH', 'NONE', 'DEADLOCK')) RAISERROR('Wrong victim specification. Only ''BLOCKER'', ''WAITING'', ''BOTH'', ''NONE'', or ''DEADLOCK'' are allowed!', 16, 1) -- Waiting sessions IF (ISNULL(OBJECT_ID('tempdb.dbo.#locked_sessions'), 0) != 0) DROP TABLE #locked_sessions SELECT IDENTITY(int, 1, 1) AS rownum, * INTO #locked_sessions FROM sys.dm_os_waiting_tasks AS wt RIGHT JOIN ( SELECT l1.request_session_id AS blocking_spid, l2.request_session_id AS waiting_spid FROM sys.dm_tran_locks AS l1 INNER JOIN sys.dm_tran_locks AS l2 ON l1.resource_database_id = l2.resource_database_id AND l1.resource_associated_entity_id = l2.resource_associated_entity_id AND l1.request_type = l2.request_type AND l1.request_session_id != l2.request_session_id AND l1.request_status = 'GRANT' AND l2.request_status IN ('WAIT', 'CONVERT') ) AS ws ON wt.blocking_session_id = ws.blocking_spid AND wt.session_id = ws.waiting_spid -- Cursor to process dead-locked sessions -- DECLARE @blocking_spid INT, @waiting_spid INT, @wait_duration_ms_blocking INT, @wait_duration_ms_waiting INT, @sql_text VARCHAR(MAX) = '' DECLARE sessions_cursor CURSOR FAST_FORWARD FOR SELECT l1.blocking_spid, l1.waiting_spid, l1.wait_duration_ms AS wait_duration_ms_blocking, l2.wait_duration_ms AS wait_duration_ms_waiting FROM #locked_sessions AS l1 -- This join is the filter which actually checks there is some mutual locking. -- If we remove it we will be processing "long held locks" which are not necessarily "dead". INNER JOIN #locked_sessions AS l2 ON (l1.blocking_spid = l2.waiting_spid AND l1.waiting_spid = l2.blocking_spid AND l1.wait_duration_ms > l2.wait_duration_ms AND @who_to_kill = 'DEADLOCK') OR (@who_to_kill != 'DEADLOCK' AND 1 = 1) WHERE l1.wait_duration_ms > @wait_duration_threshold OR l2.wait_duration_ms > @wait_duration_threshold -- If we found any deadlocks, we start processing those by killing them both OPEN sessions_cursor FETCH NEXT FROM sessions_cursor INTO @blocking_spid, @waiting_spid, @wait_duration_ms_blocking, @wait_duration_ms_waiting WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sql_text = ISNULL('KILL ' + CASE @who_to_kill WHEN 'BLOCKER' THEN CAST(@blocking_spid AS varchar(10)) WHEN 'WAITING' THEN CAST(@waiting_spid AS varchar(10)) WHEN 'BOTH' THEN CAST(@blocking_spid AS varchar(10)) + CHAR(10) + 'KILL ' + CAST(@waiting_spid AS varchar(10)) WHEN 'DEADLOCK' THEN CAST(@blocking_spid AS varchar(10)) WHEN 'NONE' THEN NULL END, CONCAT('/* Blocking SPID: ',@blocking_spid,' for ',@wait_duration_ms_blocking,'ms*/') ) IF( @showonly = 0 ) EXEC(@sql_text) SELECT @sql_text FETCH NEXT FROM sessions_cursor INTO @blocking_spid, @waiting_spid, @wait_duration_ms_blocking, @wait_duration_ms_waiting END CLOSE sessions_cursor DEALLOCATE sessions_cursor
if @showonly = 0 ALTER ANY CONNECTION (inherited via sysadmin or processadmin) and VIEW SERVER STATE (for sp_lock and sys.dm_os_waiting_tasks) If @showonly = 1 then NO connections will be killed.
In case you are in need of SQL Server support on this subject or others, you can contact us at SQLTreeo via online chat or e-mail email@example.com. We deliver 24x7x365 managed services and support.