Блокировки в MS SQL

В ходе выполнения транзакций MS SQL накладывает на объекты БД временные ограничения на определенные операции, называемые блокировками. Это базовый механизм обеспечения целостности данных.

Хотя блокировки являются одним из важнейших механизмов работы MS SQL, распространены ситуации, когда процессы пересекаются не оптимальным образом, в результате чего одновременная работа с данными становится невозможной. О том, как «ДБ-сервис» диагностирует подобные неполадки мы и расскажем в этой статье.

В своем мониторинге MSSQL блокировки мы разделили на следующие категории:

Анализ блокировок, который осуществляет «ДБ-сервис», заключается в поиске причины проблемы и предоставлению по ней полной информации клиенту. Речь идет, в том числе, об информации о внутренних сессиях приложения (как правило, Microsoft Dynamics AX). Для анализа мы используем хранимые процедуры и собственные скрипты, о которых расскажем ниже.

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

Вторая процедура, используемая для диагностики формирует «дерево блокировок», на котором можно легко найти корень проблемы и список блокировок под ним. Пример вывода процедуры представлен на скриншоте ниже.

Как видно из скрина, в выводе процедура есть вся необходимая информация: номер сессии, аккаунт, с которого она была запущена, ожидаемые ресурсы, информация о БД, использованные ресурсы, имя приложения, текст запроса и т. д.

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

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

Иногда возникают критические ситуации, когда все обращения к продуктивной базе, а часто и к системным представлениям блокируются (в том числе и из-за большого количества блокировок). В таких ситуациях для диагностики используется стандартная процедура sp_lock. Собрать подробную информацию о сессии-виновнике, собравшей наибольшее количество блокировок, в таких обстоятельствах, увы, невозможно — остается лишь завершить ее, чтобы вернуть БД работоспособность.

Пример вывода процедуры sp_lock с использованием временной таблицы блокировок SQL представлен на скриншоте ниже.

Важно понимать, что количество блокировок в выводе — это не заблокированное количество сессий. Речь идет о всех блокировках, которые наложила сессия: здесь могут фигурировать и схемы, и базы, отдельные страницы, и диапазон ключей. Посмотреть SQL блокировки можно в примере на скриншоте ниже.

Если блокировок не очень много, в качестве еще одного быстро и эффективного варианта их просмотра можно использовать монитор активности. Пример работы с ним можно увидеть на скриншоте ниже.

Накат изменений (HADR process) на зеркале (вторичной реплике) тоже попадает под блокировки. Часто они возникают из-за большого числа запросов на вторичной реплике, доступной для чтения. В таких ситуациях не всегда получается найти корень проблемы стандартными процедурами, поэтому мы используем собственный скрипт.

Наша практика (в частности опыт работы с крупным интернет-магазином косметики и парфюмерии) показывает, что виновником блокировок обычно является сессия, которая читает данные для корпоративной отчетности со вторичной реплики при настроенном read-only routing). Чтобы прочитать данные, эта сессия накладывает S-блокировку на объекты.

Если в это время на первичной реплике идут процессы, которые эксклюзивно блокируют эти объекты (SCH-M, U, X и т. д.), например, перестроение индексов, либо просто изменение какого либо объекта процессами заказчика, то чтение будет блокировать применение этих изменений.

В этом случае необходимо найти эти сессии либо в истории заданий обслуживания, либо в процессах заказчика через анализ трассировок процедурой sp_trace_events. После этого можно будет определить, какой процесс на первичной реплике вступил в конфликт со вторичной.

Когда корень блокировки найден, как правило, мы предоставляем информацию о нем клиенту в текстовом виде. Однако, если блокировок и данных о них очень много, отчет высылается в виде таблицы Excel.

Если речь идет о Microsoft Dynamics AX (Аксапта), то стандартная информация (хост, логин, запрос и т.д.) дополняется информацией по внутренней сессии приложения, т. к. без нее прочая информация не будет иметь для заказчика никакого смысла.

Иногда при просмотре подробной информации по сессии в поле ProgramName можно увидеть сообщение о работе заданий MS SQL вида «SQLAgent - TSQL JobStep (Job ''0x989F53FA6415FA45AB8954A2612BF64D'' : Step 2)».

В «ДБ-сервис» мы используем скрипты, позволяющие посмотреть подробную информацию о работе таких заданий, либо вывести все сессии, в которых эти задания выполняются. Пример такого вывода представлен на скрине ниже.

Перед тем, как передавать клиенту информацию о сессии-виновнике блокировок, мы уточняем, кто ответственен за ее запуск и анализируем уровни блокировок SQL.

Также бывают случаи, когда виновником является не верхняя сессия, а какая-то из заблокированных. Например, сессия производит чтение из таблицы, накладывая S-блокировку, в то время как другая сессия (например ребилд индекса) ждет U-блокировку на таблицу или индекс (и не может ее получить из-за несовместимости), в результате чего блокируется еще несколько сессий, которые просто читают данные. В этой ситуации для полного рассасывания блокировок достаточно завершить вторую сессию.

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

В рамках администрирования и поддержки MS SQL Server «ДБ-сервис» предлагает услуги по анализу журналов и файлов трассировки. Наши инженеры специализируются на устранении проблем с производительностью в случае возникновения блокировок, незапланированным изменением планов выполнения, а также по причине возникновения ошибок внутри ПО СУБД.

В данной статье были рассмотрены основные виды аварий, возникающих из-за блокировок в MS SQL. Также мы подробно рассказали, как диагностика блокировок осуществляется процедурами и скриптами «ДБ-сервис», а также стандартной процедурой MS SQL и монитором активности. Была затронута тема блокировок наката вторичной реплики на AlwaysOn. Также мы рассказали о том, в каком виде предоставляем отчеты о блокировках своим клиентам и как постфактум анализируем блокировки и взаимоблокировки, возникающие из-за конфликта блокировок Microsoft SQL Server.