Эскалация блокировок: сначала найдите причину

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

Правильное чтение диагностики

Блокировка намерения IX на объекте нормальна при изменении его строк. Она сообщает о блокировках нижнего уровня, но сама по себе не исключает всех читателей таблицы. У общей или исключительной табличной блокировки другие правила совместимости. Если принять IX за X, расследование легко уходит в неправильную сторону.

Запрос показывает выданные и ожидаемые объектные блокировки текущей базы. Его область специально ограничена: блокировки ключей, страниц, схемы или приложения могут оставаться за пределами результата. Необходимые разрешения диагностики зависят от версии SQL Server и должны назначаться через предусмотренную роль мониторинга.

SELECT request_session_id, resource_type, request_mode,
       request_status, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
  AND resource_type = 'OBJECT';

Сопоставьте номера сеансов с цепочкой блокирования и активными командами. При подозрении на эскалацию собирайте событие lock_escalation в Extended Events. Наблюдаемая сейчас блокировка X не объясняет, возникла ли она из-за эскалации, явной подсказки или другой операции. Событие добавляет историю, которой нет в моментальном снимке DMV.

Не считайте определённое количество изменённых строк гарантированно безопасной границей. Строки не равны блокировкам. На объём влияют индексы, путь доступа, изоляция и давление на память. ROWLOCK не обещает полного отсутствия эскалации. Её отключение способно заменить ожидания исчерпанием ресурсов блокировок.

Настоящие границы транзакций

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

Упражнение создаёт временные данные и изменяет их независимо подтверждаемыми командами. Оно требует отсутствия открытой транзакции и выключенного IMPLICIT_TRANSACTIONS. Размер пакета здесь объясняет механизм, а не задаёт универсальную производственную настройку.

IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2
    THROW 50000, 'Use autocommit with no open transaction.', 1;
CREATE TABLE #Work (Id int PRIMARY KEY, Done bit NOT NULL);
INSERT #Work
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
DECLARE @changed int = 1;
WHILE @changed > 0
BEGIN
    ;WITH batch AS
    (SELECT TOP (500) Id, Done FROM #Work
     WHERE Done = 0 ORDER BY Id)
    UPDATE batch SET Done = 1;
    SET @changed = @@ROWCOUNT;
END;
SELECT COUNT(*) AS Remaining FROM #Work WHERE Done = 0;

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

Проверка параллельной работы и перезапуска

Запускайте обслуживание одновременно с представительными чтениями и изменениями. Сравните самые медленные запросы, события эскалации и объём журнала до и после изменения. Более длительное обслуживание может быть лучшим вариантом, если пользовательские запросы укладываются в целевое время. Однако тысячи слишком маленьких фиксаций тоже создают накладные расходы.

Независимые фиксации меняют поведение при ошибке. Отмена оставляет частично выполненную операцию. Условие отбора должно позволять безопасное продолжение, а отметка прогресса должна фиксироваться вместе с соответствующими изменениями. Если бизнес требует атомарности всего набора, пакетная обработка меняет это обязательство и требует отдельного решения.

Дополнительно исследуйте триггеры, каскадные действия, репликацию и отправку журнала в группу доступности. Они могут многократно увеличить работу небольшой команды. Включите в испытание намеренную остановку между пакетами и убедитесь, что продолжение не пропускает строки и не повторяет внешние действия. Проверьте также, что отменённый пакет не оставил отметку успешного завершения. Полезно сохранить границы обработанных ключей вместе с временем фиксации: это позволяет объяснить частичный результат после сбоя. Хорошее исправление устанавливает исходного блокировщика, уменьшает нужные ресурсы, сохраняет транзакционную семантику и подтверждает приемлемые задержки параллельных запросов. Одного исчезнувшего события эскалации недостаточно для вывода об улучшении.

Техническая документация: Microsoft Learn: Lock escalation · Microsoft Learn: Lock diagnostics.