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

22
Май
2014
Category: DataBase / Windows   /   Tags: ,

Введение

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

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.

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

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

test_script[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

Помещение информации о блокировках в таблицу

Чтобы сохранить информацию о блокировках в таблицу, используйте скрипт Capture_blocking_info.sql, который использует выражение INSERT…EXEC с sp_locks в @Mode = 2.

Листинг 3. Скрипт Capture_blocking_info.sql

Внутри блокировок

Чтобы получить представление о блокирующих и заблокированных процессах, возвращаемых sp_locks, можно использовать следующий скрипт, детализирующий причины блокировки.

Я использую его при поиске и воспроизведении сценария блокировки. Фильтрация по spid позволяет мне видеть только нужные мне данные и получить картину выделенных ресурсов и состояния их блокировок.

При исполнении скрипта для тестового сценария при фильтрации по имени БД я вижу все вовлеченные процессы. Это четко показывает, что блокирующий процесс, spid 218, удерживает эксклюзивную (Х) блокировку ключа индекса (кластерного индекса в данном случае), и что это блокировка обновляемой строки. Эксклюзивная блокировка строки должна быть также помечена на уровне страницы, что и делается с помощью Intense Exclusive (IX) блокировки. Можно видеть, что это страница 154 в файле данных с номером 1. IX-блокировка страницы должна быть распространена до уровня таблицы, что и делается с помощью IX-блокировки этого объекта.

Листинг 4. Тестовый сценарий

test_script_inside[1]

Все остальные процессы, пытающиеся читать данные, имеют статус WAIT и ожидают освобождения ресурса, заблокированного выражением UPDATE.

Права

DMV (Dynamic Management Views) и DMF (Dynamic Management Function), используемые хранимой процедурой, требуют, чтобы вызывающая их сторона имела права VIEW SERVER STATE и, конечно, право на запуск процедур из БД master (EXECUTE).

www.k-press.ru

Яндекс.Метрика