- Docs
- /
06 Jun 2022 30787 views 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:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152 /*Functionality=============Allocate and (optionally) kill blocking locksApplications============MSSQLPRERequisites=============- Set parameter @who_to_kill (can also be empty)VERSION HISTORYVersion Date Author Reason------------------------------------------------------------------4 18 dec 17 ak kill statement replaced with select5 25 apr 18 dr changed from sp_lock to sys.dm_tran_locksParameters=======@wait_duration_threshold INT: (Milliseconds that a process is waiting)from -2,147,483,648 to 2,147,483,647only 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 forhow long either process is waiting for the other@showonly BIT: (show or show and kill the processes identified)1 - do not execute KILL0 (default) - only show processes that match @who_to_kill.Logging=======NonePermissions=======if @showonly = 0ALTER ANY CONNECTION (inherited via sysadmin or processadmin)ANDVIEW SERVER STATE (for sp_lock and sys.dm_os_waiting_tasks)Results=======result set with blocking locksexecuted KILL command if @showonly = 0*/SET NOCOUNT ONDECLARE @who_to_kill VARCHAR(10) = 'NONE'DECLARE @wait_duration_threshold INT = 1DECLARE @showonly BIT = 0IF (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 sessionsIF (ISNULL(OBJECT_ID('tempdb.dbo.#locked_sessions'), 0) != 0)DROP TABLE #locked_sessionsSELECTIDENTITY(int, 1, 1) AS rownum,*INTO#locked_sessionsFROMsys.dm_os_waiting_tasks AS wtRIGHT JOIN(SELECTl1.request_session_id AS blocking_spid,l2.request_session_id AS waiting_spidFROMsys.dm_tran_locks AS l1INNER JOINsys.dm_tran_locks AS l2 ONl1.resource_database_id = l2.resource_database_id ANDl1.resource_associated_entity_id = l2.resource_associated_entity_id ANDl1.request_type = l2.request_type ANDl1.request_session_id != l2.request_session_id ANDl1.request_status = 'GRANT' ANDl2.request_status IN ('WAIT', 'CONVERT')) AS ws ONwt.blocking_session_id = ws.blocking_spid ANDwt.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_FORWARDFORSELECTl1.blocking_spid,l1.waiting_spid,l1.wait_duration_ms AS wait_duration_ms_blocking,l2.wait_duration_ms AS wait_duration_ms_waitingFROM#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)WHEREl1.wait_duration_ms > @wait_duration_threshold ORl2.wait_duration_ms > @wait_duration_threshold-- If we found any deadlocks, we start processing those by killing them bothOPEN sessions_cursorFETCH NEXT FROM sessions_cursor INTO @blocking_spid, @waiting_spid, @wait_duration_ms_blocking, @wait_duration_ms_waitingWHILE @@FETCH_STATUS = 0BEGINSELECT @sql_text = ISNULL('KILL ' +CASE @who_to_killWHEN '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 NULLEND,CONCAT('/* Blocking SPID: ',@blocking_spid,' for ',@wait_duration_ms_blocking,'ms*/') )IF( @showonly = 0 )EXEC(@sql_text)SELECT @sql_textFETCH NEXT FROM sessions_cursor INTO @blocking_spid, @waiting_spid, @wait_duration_ms_blocking, @wait_duration_ms_waitingENDCLOSE sessions_cursorDEALLOCATE 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 servicedesk@sqltreeo.com. We deliver 24x7x365 managed services and support.