Поиск цепочек блокировок на SQL Server с помощью процедуры sp_locks
Введение
Параллелизм в отношении БД можно определить как число пользователей, способных работать с данной БД, не мешая и не пересекаясь друг с другом. Чем больше число одновременно работающих пользователей, тем выше параллелизм.
SQL Server блокирует данные для обеспечения их целостности. Это необходимо в любой БД, где данные подвергаются изменениям. Чтобы подчеркнуть это, скажу, что если БД находится в состоянии READ_ONLY, SQL Server не создает блокировок считываемых данных, поскольку они не могут быть изменены и, следовательно, защищать данные не нужно. Поэтому SQL Server только помечает объекты, к которым происходит обращение, блокировкой Intense Share (IS), чтобы исключить выполнение таких DDL-выражений, как удаление таблицы, из которой производится чтение.
Блокировки данных существуют в любой среде, где модифицируются данные (то есть БД находится в состоянии READ_WRITE, в противоположность предыдущему примеру), и это совершенно нормально. SQL Server удерживает необходимые блокировки для защиты целостности данных и освобождает данные, как только отпадает необходимость в блокировке.
Краткосрочные блокировки не оказывают негативного воздействия, они — часть нормальной работы SQL Server. Проблемы возникают, когда продолжительность блокировок становится большой, то есть в несколько секунд, и в этом случае в игру вступает sp_locks.
sp_locks
sp_locks — это полезное средство, помогающее в поиске и устранении проблем в сценариях с блокировками и параллелизмом.
Листинг 1. Процедура sp_locks.
USE [master];
SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON;
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[sp_Locks]')
AND type IN (N'P', N'PC')
)
DROP PROCEDURE dbo.sp_Locks;
GO
CREATE PROCEDURE dbo.sp_Locks
(
@Mode int = 2,
@Wait_Duration_ms int = 1000 /* 1 seconds */
)
/*
19/04/2008 Yaniv Etrogi
http://www.sqlserverutilities.com
*/
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000
/* return the one result set */
IF @Mode = 1
BEGIN;
SELECT
t.blocking_session_id AS blocking,
t.session_id AS blocked,
p2.[program_name] AS program_blocking,
p1.[program_name] AS program_blocked,
DB_NAME(l.resource_database_id) AS [database],
p2.[hostname] AS host_blocking,
p1.[hostname] AS host_blocked,
t.wait_duration_ms,
l.request_mode,
l.resource_type,
t.wait_type,
(SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset END
- r.statement_start_offset) /2 ) + 1)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id = l.request_session_id) AS statement_blocked,
CASE WHEN t.blocking_session_id > 0 THEN
(SELECT st.text
FROM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
WHERE p.spid = t.blocking_session_id)
ELSE NULL END AS statement_blocking
--,t.resource_description AS blocking_resource_description
--,l.resource_associated_entity_id
FROM sys.dm_os_waiting_tasks AS t
INNER JOIN sys.dm_tran_locks AS l
ON t.resource_address = l.lock_owner_address
INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
END;
/* return the first two result sets */
IF @Mode = 2
BEGIN;
SELECT
spid,
[status],
CONVERT(CHAR(3), blocked) AS blocked,
loginame,
SUBSTRING([program_name] ,1,25) AS program,
SUBSTRING(DB_NAME(p.dbid),1,10) AS [database],
SUBSTRING(hostname, 1, 12) AS host,
cmd,
waittype,
t.[text]
FROM sys.sysprocesses p
CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked <> 0)
AND blocked = 0;
SELECT
t.blocking_session_id AS blocking,
t.session_id AS blocked,
p2.[program_name] AS program_blocking,
p1.[program_name] AS program_blocked,
DB_NAME(l.resource_database_id) AS [database],
p2.[hostname] AS host_blocking,
p1.[hostname] AS host_blocked,
t.wait_duration_ms,
l.request_mode,
l.resource_type,
t.wait_type,
(SELECT SUBSTRING(st.text, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset END
- r.statement_start_offset) / 2) + 1
)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id = l.request_session_id) AS statement_blocked,
CASE WHEN t.blocking_session_id > 0 THEN
(SELECT st.text
FROM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
WHERE p.spid = t.blocking_session_id) ELSE NULL
END AS statement_blocking
FROM sys.dm_os_waiting_tasks AS t
INNER JOIN sys.dm_tran_locks AS l
ON t.resource_address = l.lock_owner_address
INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
END;
/* return all three result sets */
IF @Mode = 3
BEGIN;
SELECT
spid,
[status],
CONVERT(CHAR(3), blocked) AS blocked,
loginame,
SUBSTRING([program_name], 1, 25) AS program,
SUBSTRING(DB_NAME(p.dbid), 1, 10) AS [database],
SUBSTRING(hostname, 1, 12) AS host,
cmd,
waittype,
t.[text]
FROM sys.sysprocesses p
CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t
WHERE spid IN (SELECT blocked FROM sys.sysprocesses WHERE blocked <> 0)
AND blocked = 0;
SELECT
t.blocking_session_id AS blocking,
t.session_id AS blocked,
SUBSTRING(p2.[program_name], 1, 25) AS program_blocking,
SUBSTRING(p1.[program_name], 1, 25) AS program_blocked,
DB_NAME(l.resource_database_id) AS [database],
p2.[hostname] AS host_blocking,
p1.[hostname] AS host_blocked,
t.wait_duration_ms,
l.request_mode,
l.resource_type,
t.wait_type,
(SELECT SUBSTRING(st.text, (r.statement_start_offset/2) + 1,
((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id = l.request_session_id) AS statement_blocked,
CASE WHEN t.blocking_session_id > 0 THEN
(SELECT st.text
FROM sys.sysprocesses AS p
CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
WHERE p.spid = t.blocking_session_id) ELSE NULL
END AS statement_blocking
--,t.resource_description AS blocking_resource_description
--,l.resource_associated_entity_id
FROM sys.dm_os_waiting_tasks AS t
INNER JOIN sys.dm_tran_locks AS l
ON t.resource_address = l.lock_owner_address
INNER JOIN sys.sysprocesses p1 ON p1.spid = t.session_id
INNER JOIN sys.sysprocesses p2 ON p2.spid = t.blocking_session_id
WHERE t.session_id > 50 AND t.wait_duration_ms > @Wait_Duration_ms;
SELECT DISTINCT
r.session_id AS spid,
r.percent_complete AS [percent],
r.open_transaction_count AS open_trans,
r.[status],
r.reads,
r.logical_reads,
r.writes,
s.cpu,
DB_NAME(r.database_id) AS [db_name],
s.[hostname],
s.[program_name],
--s.loginame,
--s.login_time,
r.start_time,
--r.wait_type,
r.wait_time,
r.last_wait_type,
r.blocking_session_id AS blocking,
r.command,
(SELECT SUBSTRING(text, statement_start_offset / 2 + 1,
(CASE WHEN statement_end_offset = -1 THEN
LEN(CONVERT(NVARCHAR(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset) / 2)
FROM sys.dm_exec_sql_text(r.sql_handle)) AS [statement],
t.[text]
--,query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.sysprocesses s ON s.spid = r.session_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) t
--CROSS APPLY sys.dm_exec_query_plan (r.plan_handle)
WHERE r.session_id > 50 AND r.session_id <> @@spid
AND s.[program_name] NOT LIKE 'SQL Server Profiler%'
--AND db_name(r.database_id) NOT LIKE N'distribution'
--AND r.wait_type IN ('SQLTRACE_LOCK', 'IO_COMPLETION', 'TRACEWRITE')
ORDER BY s.CPU DESC;
END;
GOВ отличие от хранимой процедуры sp_helpindex2, выбирающей информацию, связанную с контекстом текущей БД, sp_locks выбирает информацию уровня сервера, и поэтому она не должна быть помечена как системная хранимая процедура. Однако использование префикса sp_ позволяется воспользоваться тем, что SQL Server сперва ищет все объекты с таким префиксом в БД master. Это дает возможность вызвать процедуру из любой БД, не используя имя из трех частей (например, database.schema.object), что удобно.
Процедура возвращает до трех наборов результатов, что регулируется входным параметром @Mode.
Первый набор результатов возвращает информацию о Lead Blocker-процессе, если таковой существует, и нужен в ситуациях наличия высокой активности блокировок, приводящей к появлению цепочек блокировок.
Цепочка блокировок — это ситуация, когда есть много процессов, блокирующих другие процессы, которые, в свою очередь, блокируют данные или другие процессы. Во многих случаях это требует ручного вмешательства для разрешения конфликта, обычно это вмешательство принимает форму команды, завершающей исполнение процесса, выступающего в роли Lead Blocker.
Lead Blocker — это кличка для процесса, являющегося главной причиной, инициатором, с которого начинается цепочка блокировок — то есть этот процесс блокирует другой процесс, который, в свою очередь, будучи заблокированным, не может снять наложенные им блокировки, и тем самым сам становится блокирующим процессом. Это может продолжаться и продолжаться, вовлекая другие процессы, и порождая цепочки блокировок.
В таком сценарии страдает параллелизм работы с БД, и это, конечно, отражается на времени отклика приложений. Так что на момент, когда вы узнаете о наличии такой ситуации, вы, конечно, хотите как можно быстрее справиться с ней, чтобы минимизировать ее влияние на систему.
Этот набор результатов чаще всего содержит одну строку, но в сценарии эскалации блокировок вы можете увидеть и несколько строк (что означает, что имеется несколько блокирующих процессов и несколько цепочек блокировок).
На рисунке 1, где показано исполнение тестового сценария, блокирующим является процесс, выдавший команду UPDATE (spid 218). Этот процесс блокирует первый процесс, (spid 193), выдающий команду SELECT (/* connection 1 */), и любой последующий SELECT блокируется этим первым выражением SELECT.
Завершение подключения, выдавшего команду UPDATE, позволит всем блокированным (ожидающим) процессам продолжить работу и разрушит цепочку блокировок.
Заметьте, что если прервать любой другой процесс, сценарий эскалации блокировок не будет прерван и цепочка блокировок сохранится.
Второй набор результатов содержит информацию о блокирующих и блокированных процессах, обращаясь к трем ключевым DMV: sys.dm_os_waiting_tasks, sys.dm_tran_locks и sys.sysprocesses.
Мы выполняем запрос sys.dm_os_waiting_tasks, поскольку именно это нас и интересует — задачи (процессы), находящиеся в состоянии ожидания, в данном случае — ожидающие возможности получить блокировку ресурсов. Приятно то, что это DMV можно соединить с sys.dm_tran_locks по колонке resource_address, которая содержит адрес в памяти ресурса, которого ожидает задача, а с другой стороны условия объединения имеется колонка lock_owner_address из sys.dm_tran_locks, являющаяся адресом в памяти внутренней структуры данных, используемой для отслеживания запросов на блокировки модулем LOCK_MANAGER SQL Server-а.
Эти два DMV дают ценную информацию о блокировках. Далее мы добавляем sys.sysprocesses, чтобы получить информацию более высокого уровня, о блокировках программ и хостов. Чтобы получить информацию о блокирующих и заблокированных процессах, мы дважды выполняем соединение с sys.sysprocesses — один экземпляр для блокирующих, а другой — для заблокированных процессов.
Это достигается соединением одного экземпляра sys.sysprocesses как p1 с sys.dm_os_waiting_tasks по колонке session_id, и второго экземпляра как p2 по колонке blocking_session_id из sys.dm_os_waiting_tasks.
Входной параметр @Wait_Duration_ms позволяет ограничить число выводимых строк теми процессами, чье время ожидание превышает число (время в миллисекундах), указанное в этом параметре. Это очень полезно, поскольку обычно вас не интересуют краткосрочные (в 2-3 секунды) ожидания (блокировки), не оказывающие отрицательного влияния на систему.
Третий набор результатов содержит информацию из sys.dm_exec_requests об активно исполняемых процессах. Этот набор результатов может быть полезным, когда нет активных блокировок, и первые два набора не содержат никаких строк, но вам все-таки нужно увидеть, что исполняется в данное время на сервере.
Листинг 2. Использование sp_lock
-- Возвратить только второй набор результатов
EXEC sp_Locks @Mode = 1, @Wait_Duration_ms = 1000;
-- Возвратить первый и второй наборы результатов
EXEC sp_Locks @Mode = 2, @Wait_Duration_ms = 1000;
-- Возвратить все три набора результатов
EXEC sp_Locks @Mode = 3, @Wait_Duration_ms = 1000;Помещение информации о блокировках в таблицу
Чтобы сохранить информацию о блокировках в таблицу, используйте скрипт Capture_blocking_info.sql, который использует выражение INSERT…EXEC с sp_locks в @Mode = 2.
Листинг 3. Скрипт Capture_blocking_info.sql
/*
Сохранение информации о блокировках в таблицу
19/04/2008 Yaniv Etrogi
http://www.sqlserverutilities.com
*/
USE [tempdb];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Blocks]')
AND type in (N'U'))
DROP TABLE [dbo].[Blocks];
GO
CREATE TABLE [dbo].[Blocks](
[Id] [int] identity(1,1) NOT NULL CONSTRAINT
[PK_Blocks] PRIMARY KEY NONCLUSTERED ([Id]),
[InsertTime] datetime CONSTRAINT
[DF_Blocks_InsertTime] DEFAULT (getdate()) NOT NULL,
[blocking] smallint NULL,
[blocked] smallint NULL,
[program_blocking] varchar(128) NULL,
[program_blocked] varchar(128) NULL,
[database] varchar(128) NULL,
[host_blocking] varchar(128) NOT NULL,
[host_blocked] varchar(128) NOT NULL,
[wait_duration_ms] bigint NULL,
[request_mode] varchar(60) NOT NULL,
[resource_type] varchar(60) NOT NULL,
[wait_type] varchar(60) NULL,
[statement_blocked] varchar(max) NULL,
[statement_blocking] varchar(max) NULL );
GO
CREATE CLUSTERED INDEX IXC_Blocks_InsertTime ON dbo.Blocks (InsertTime);
-- Бесконечный цикл до ручного прекращения исполнения
-- или изменения условия WHEN
SET NOCOUNT ON;
DECLARE @i int; SELECT @i = 0;
WHILE (@i < 1000000)
BEGIN;
INSERT INTO [dbo].[Blocks]
([blocking],
[blocked],
[program_blocking],
[program_blocked],
[database],
[host_blocking],
[host_blocked],
[wait_duration_ms],
[request_mode],
[resource_type],
[wait_type],
[statement_blocked],
[statement_blocking])
EXEC sp_Locks @Wait_Duration_ms = 1000, @Mode = 1;
SELECT @i = @i + 1;
IF @i % 100 = 0 PRINT @i; --print every 100 iterations
WAITFOR DELAY '00:00:10'; --10 seconds sleep
END;
-- Выборка полученных данных о блокировках.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT TOP 100
[Id],
[InsertTime],
[blocking],
[blocked],
LEFT([program_blocking], 20) AS [program_blocking],
LEFT([program_blocked], 20) AS [program_blocked],
[database],
[host_blocking],
[host_blocked],
[wait_duration_ms],
[request_mode],
[resource_type],
[wait_type],
[statement_blocked],
[statement_blocking]
FROM [dbo].[Blocks]
ORDER BY [InsertTime] DESC;Внутри блокировок
Чтобы получить представление о блокирующих и заблокированных процессах, возвращаемых sp_locks, можно использовать следующий скрипт, детализирующий причины блокировки.
Я использую его при поиске и воспроизведении сценария блокировки. Фильтрация по spid позволяет мне видеть только нужные мне данные и получить картину выделенных ресурсов и состояния их блокировок.
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT --TOP 100
l.request_session_id AS spid,
DB_NAME(l.resource_database_id) AS [database],
CASE WHEN l.resource_type = 'OBJECT'
THEN OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id)
WHEN l.resource_associated_entity_id = 0
THEN 'NA'
ELSE OBJECT_NAME(p.object_id, l.resource_database_id) END AS [object],
p.index_id,
l.resource_type AS [resource],
l.resource_description AS [description],
l.request_mode AS [mode],
l.request_status AS [status],
l.resource_associated_entity_id
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
WHERE resource_type NOT LIKE 'DATABASE'
--AND l.request_session_id = @@SPID -- <---- edit spid here
--AND DB_NAME(l.resource_database_id) NOT LIKE 'distribution'При исполнении скрипта для тестового сценария при фильтрации по имени БД я вижу все вовлеченные процессы. Это четко показывает, что блокирующий процесс, spid 218, удерживает эксклюзивную (Х) блокировку ключа индекса (кластерного индекса в данном случае), и что это блокировка обновляемой строки. Эксклюзивная блокировка строки должна быть также помечена на уровне страницы, что и делается с помощью Intense Exclusive (IX) блокировки. Можно видеть, что это страница 154 в файле данных с номером 1. IX-блокировка страницы должна быть распространена до уровня таблицы, что и делается с помощью IX-блокировки этого объекта.
Листинг 4. Тестовый сценарий
USE [master];
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST')
CREATE DATABASE TEST;
GO
USE [TEST];
IF EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[T1]')
AND type in (N'U'))
DROP TABLE [dbo].[T1];
GO
USE [TEST];
IF EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[T1]')
AND type in (N'U'))
DROP TABLE [dbo].[T1];
GO
CREATE TABLE [dbo].[T1]
(
[c1] [int] IDENTITY(1,1) NOT NULL,
[c2] [int] NOT NULL, PRIMARY KEY CLUSTERED([c1] )
);
INSERT T1 (c2) VALUES (10);
INSERT T1 (c2) VALUES (20);
INSERT T1 (c2) VALUES (30);
INSERT T1 (c2) VALUES (40);
INSERT T1 (c2) VALUES (50);
INSERT T1 (c2) VALUES (60);
/* Blocker */
-- Выполняет UPDATE и оставляет транзакцию открытой, так что эксклюзивная (X)
-- блокировка, получаемая процессом, не освобождается.
USE [TEST];
BEGIN TRAN; UPDATE T1 SET c2 = 100 WHERE c1 = 5;
--ROLLBACK
/* Blocked */
USE [TEST]; SELECT * FROM TEST..T1 /* connection 1 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 2 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 3 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 4 */ ;
USE [TEST]; SELECT * FROM TEST..T1 /* connection 5 */ ;Все остальные процессы, пытающиеся читать данные, имеют статус WAIT и ожидают освобождения ресурса, заблокированного выражением UPDATE.
Права
DMV (Dynamic Management Views) и DMF (Dynamic Management Function), используемые хранимой процедурой, требуют, чтобы вызывающая их сторона имела права VIEW SERVER STATE и, конечно, право на запуск процедур из БД master (EXECUTE).
/*
USE [master];
GRANT VIEW SERVER STATE TO Paul;
GRANT EXECUTE ON sp_locks TO Paul;
*/(c) www.k-press.ru