Дедлоки в SQL Server— часть 2: Обнаружение противника
Диагностика взаимоблокировок
Мы продолжаем серию публикаций о блокировках в БД. В предыдущей части мы рассмотрели виды блокировок, с которыми столкнулись при разработке ПО и рассказали что такое взаимоблокировки.
Важной задачей является обнаружение и диагностика блокировок, для дальнейшего их анализа и своевременного принятия мер по их устранению. В данной статье рассмотрим, какими средствами мы пользуемся для решения данной задачи.
Журналы приложений (логи)
Основными средствами для обнаружения проблем в разрабатываемых нами приложениях являются журналы приложения. Взаимоблокировки не являются исключением. Вот так примерно они выглядят в наших логах:
1) текстовые логи:
2) журнал событий Windows (Eventlog):
Счетчики производительности
При разработке программных продуктов, в нашей компании используются счетчики производительности для подсчета количественных и средних значений различных показателей, в том числе взаимоблокировок. В мониторе счетчиков производительности, мы имеем возможность наблюдать возникновение взаимоблокировок.
Подробнее о сборе метрик взаимоблокировок будет сказано в следующей части.
MS SQL profiler
Для обнаружения, какие транзакции взаимно блокируются, мы пользуемся инструментом SQL Profiler, который позволяет наглядно увидеть следующую информацию:
– какие запросы привели к взаимной блокировке,
– какие ресурсы были заблокированы в транзакциях,
– какой тип блокировки был наложен на ресурсы,
– какая транзакция стала жертвой менеджера блокировок.
Для запуска мониторинга взаимоблокировок нужно в SQL Profiler, в свойствах трассировки выбрать события блокировок Locks и запустить саму трассировку.
Если во время трассировки произойдут события взаимной блокировки, они зарегистрируются в созданной нами трассе. Подробности зарегистрированных в трассе взаимных блокировок можно просмотреть, выделив его в списке событий (см. Рис. 4).
В подробностях видно какие транзакции взаимно заблокировались и какие ресурсы были запрошены. Рассмотрим пример на рис.7. По рисунку видно, что заблокировались 2 транзакции, которые запросили уже заблокированные записи друг у друга. Транзакция на рисунке слева (ProcessId = 79) запросила на блокировку обновления ресурс, занятый транзакцией справа (ProcessId=67). И наоборот, транзакция на рисунке справа (ProcessId=67) запросила на блокировку обновления ресурс, занятый транзакцией слева (ProcessId = 79). Общим ресурсом в обоих случаях является первичный ключ записи в таблицах Match и AdjudicationResult. Стрелки на графе показывают направление блокировки, а над ними отображается тип запрошенной блокировки или тип наложенной блокировки. Если подвести мышкой на транзакцию, то во всплывающей подсказке будет отображен запрос выполняемый в рамках конкретной транзакции. Проанализировав какие запросы вызывают взаимную блокировку, можно их модифицировать, чтобы разрешить взаимную блокировку.
Регистрация блокировок в журнале сервера MS SQL Server
MS SQL Server предоставляет возможность вывода возникающих ошибок блокировки в свой журнал трассировки с помощью флагов трассировки. Это полезно, например, если необходим сбор трассы в течение длительного периода времени (дни, недели). Для вывода ошибок блокировок в журнал сервера, необходимо включить флаги Trace Flag 1204 и Trace Flag 1222:
После этого в журнале MS SQL Server можно увидеть возникающие события блокировок:
Системные хранимые процедуры
Наложенные блокировки также можно посмотреть через системные хранимые процедуры/функции SQL Server. Для просмотра текущих блокировок используется системная хранимая функция sp_lock, которая возвращает следующую информацию:
Имя колонки
Описание
spid -Идентификатор процесса SQL Server.
dbid -Идентификатор базы данных.
ObjId -Идентификатор объекта, на который установлена блокировка.
IndId -Идентификатор индекса.
Type -Тип объекта. Может принимать значения: DB, EXT, TAB, PAG, RID, KEY.
Resource — Содержимое колонки syslocksinfo.restext. Обычно это идентификатор строки (для типа RID) или идентификатор страницы (для типа PAG).
Mode -Тип блокировки. Может принимать значения: Sch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeIn-Null, RangeIn-S, RangeIn-U, RangeIn-X, RangeX-S, RangeX-U, RangeX-X.
Status -Статус процесса SQL Server. Может принимать значения: GRANT, WAIT, CNVRT.
На рисунке 7 представлен пример использования функции sp_lock. На нем видно, что на три записи наложена совмещаемая блокировка типа KEY, это ключи выбираемых записей. Если вызывать функцию sp_lock без параметров, то она вернет абсолютно все блокировки всех процессов. Можно вывести блокировки только определенных процессов, передав идентификаторы процессов через запятую. Например, вызвав exec sp_lock 55, Мы выведем блокировки только процесса с идентификатором 55 (см. Рис. 7).
SQL Server имеет также функции sp_who и sp_who2 для просмотра активных в данный момент процессов. Разница между этими функциями только в составе выдаваемых данных, sp_who2 выдает больше информации. С помощью этой функции мы можем увидеть какие процессы в данный момент активны и в каком они состоянии. Нас прежде всего интересует состояние блокировки процессов, которые можно увидеть с помощью этой функции.
Sp_who2 показывает, все блокировки на том экземпляре SQL Server, где имеются проблемы. Запуск sp_who2 на проблемном сервере показывает, что там действительно есть заблокированные процессы, как следует из поля BlkBy в результатах процедуры, см. Рис. 8.
Можно с первого взгляда наглядно определить, что SPID 56 заблокирован SPID 54.
sp_who2 возвращает результирующий набор со следующими сведениями:
Имя колонки
Описание
SPID -Идентификатор процесса SQL Server.
Status - Состояние процесса
Login -Имя входа процесса
HostName -Имя хоста, который инициировал процесс
BlkBy -Идентификатор процесса, заблокировавший текущий процесс
DBName -Имя БД, к которому обратился процесс
Command -Исполняемая процессом команда или имя системного процесса ядра СУБД
CPUTime -Время выполнения процесса
DiskIO -Количество операций чтения/записи с диска
LastBatch -Время последнего вызова удаленной хранимой процедуры или инструкции EXECUTE клиентским процессом.
ProgramName -Имя приложения
REQUESTID -Идентификатор запроса. Применяется для идентификаций запросов, выполняемых в текущем сеансе.
Заключение
Итак, в данной статье мы рассмотрели следующие способы обнаружения и диагностики взаимоблокировок:
– журналы приложений (логи);
– счетчики производительности;
– штатные средства MS SQL Server.
Также кратко затронули, как пользоваться инструментами диагностики взаимоблокировок:
– MS SQL Profiler;
– регистрация блокировок в журнале MS SQL Server;
– использование системных хранимых процедур sp_lock, sp_who2.
Для быстрого обнаружения лучше всего использовать журналы приложения и счетчики производительности.
Штатные средства MS SQL Server лучше использовать при диагностике и исправлении проблем с взаимоблокировками.
Запись в журнал MS SQL Server диагностической информации может понадобиться в случае, если взаимоблокировка возникает очень редко и профайлером здесь не обойтись.
В следующей части статьи рассмотрим, какие виды взаимоблокировок бывают и как с ними бороться.
Автор статьи: Николай Иванов, Старший Разработчик, ITA Labs
