Технология Клиент-Сервер 2010'4

Home Поиск Software Издания Работа k-press.ru


Продолжается подписка на наши издания! Вы не забыли подписаться?

Поиск цепочек блокировок на SQL Server с помощью процедуры sp_locks

Автор: Yaniv Etrogi
Опубликовано: 29.12.2010
Версия текста: 1.1
Введение
sp_locks
Помещение информации о блокировках в таблицу
Внутри блокировок
Права

Введение

Параллелизм в отношении БД можно определить как число пользователей, способных работать с данной БД, не мешая и не пересекаясь друг с другом. Чем больше число одновременно работающих пользователей, тем выше параллелизм.

SQL Server блокирует данные для обеспечения их целостности. Это необходимо в любой БД, где данные подвергаются изменениям. Чтобы подчеркнуть это, скажу, что если БД находится в состоянии READ_ONLY, SQL Server не создает блокировок считываемых данных, поскольку они не могут быть изменены и, следовательно, защищать данные не нужно. Поэтому SQL Server только помечает объекты, к которым происходит обращение, блокировкой Intense Share (IS), чтобы исключить выполнение таких DDL-выражений, как удаление таблицы, из которой производится чтение.

Блокировки данных существуют в любой среде, где модифицируются данные (то есть БД находится в состоянии READ_WRITE, в противоположность предыдущему примеру), и это совершенно нормально. SQL Server удерживает необходимые блокировки для защиты целостности данных и освобождает данные, как только отпадает необходимость в блокировке.

Краткосрочные блокировки не оказывают негативного воздействия, они – часть нормальной работы SQL Ser­ver. Проблемы возникают, когда продолжительность блокировок становится большой, то есть в несколько секунд, и в этом случае в игру вступает 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 (http://blogs.microsoft.co.il/blogs/yaniv_etrogi/archive/2010/04/11/sp-helpindex2.aspx), выбирающей информацию, связанную с контекстом текущей БД, 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, позволит всем блокированным (ожидающим) процессам продолжить работу и разрушит цепочку блокировок.

Заметьте, что если прервать любой другой процесс, сценарий эскалации блокировок не будет прерван и цепочка блокировок сохранится.


 Рисунок 1.

Второй набор результатов содержит информацию о блокирующих и блокированных процессах, обращаясь к трем ключевым 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];


Рисунок 2. 

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

Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.

Ваши предложения и комментарии мы ожидаем по адресу: mag@rsdn.ru
Copyright © 1994-2002 Оптим.ру