Blocking Locks

06 Jun 2022 27107 views 0 minutes to read Contributors

Situation

Your query is waiting for another query and the other query is doing nothing.

Blocking Locks

A blocking lock occurs when one lock causes another process to wait until the current process is entirely done with the resources.

Deadlocks

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.

Lock-Avoiding Design Strategies

There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

• Use clustered indexes on high-usage tables.
• Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
• Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions.
• Make sure that UPDATE and DELETE statements use an existing index.
• If you use nested transactions, be sure there are no commit or rollback conflicts.

Problem

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.

Kill Blocking Locks and Deadlocks

The following script can be automated via SQLServer Agent Jobs or executed manually to help you kill connections:

		
			
T-SQL
			
Transact-SQL
			

			
/*
	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
			

				

					

				

					
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
				
						
/*
	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
					
				
			
		

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)   If @showonly = 1 then NO connections will be killed.

Support

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.