Устранение проблем блокировки, вызванных эскалацией блокировки в SQL Server

  • Статья

Сводка

Эскалация блокировки — это процесс преобразования многих мелких блокировок (таких как блокировки строк или страниц) в блокировки таблиц. Microsoft SQL Server динамически определяет, когда следует выполнять эскалацию блокировки. При принятии этого решения SQL Server учитывает количество блокировок, удерживаемых при конкретном сканировании, количество блокировок, удерживаемых всей транзакцией, и память, используемую для блокировки в системе в целом. Как правило, поведение SQL Server по умолчанию приводит к тому, что эскалация блокировки происходит только в тех случаях, когда это повысит производительность или когда необходимо сократить избыточное количество памяти блокировки системы до более разумного уровня. Однако некоторые приложения или структуры запросов могут активировать эскалацию блокировки в то время, когда это действие нежелательно, а эскалация блокировки таблицы может блокировать других пользователей. В этой статье описывается, как определить, вызывает ли эскалация блокировки блокировку, и как бороться с нежелательной эскалацией блокировки.

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 323630

Определите, вызывает ли эскалация блокировки блокировку

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

Если происходит эскалация блокировки, убедитесь, что эскалация блокировки таблицы блокирует других пользователей.

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

Если блокировка, блокирующая других пользователей, является чем-либо иным, чем блокировка TAB (на уровне таблицы), которая имеет режим блокировки S (общий) или X (монопольный), эскалация блокировки не является проблемой. В частности, если блокировка TAB является блокировкой намерения (например, режим блокировки IS, IU или IX), это не вызвано эскалацией блокировки. Если проблемы с блокировкой не вызваны эскалацией блокировки, см. инструкции по устранению неполадок, связанных с блокировкой, см. в разделе Inf: Understanding and resolveing SQL Server.

Предотвращение эскалации блокировки

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

  • Разделение больших пакетных операций на несколько небольших операций. Например, вы выполните следующий запрос, чтобы удалить более 100 000 старых записей из таблицы аудита, а затем определить, что запрос вызвал эскалацию блокировки, которая заблокировала других пользователей:

    DELETE FROM LogMessages WHERE LogDate < '20020102';

    Удаляя эти записи по несколько сотен за раз, можно значительно уменьшить количество блокировок, накапливающихся на транзакцию. Это предотвратит эскалацию блокировки. Например, выполните следующий запрос:

    DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;

  • Уменьшите объем блокировки запроса, сделав запрос максимально эффективным. Большие проверки или много подстановок закладок могут увеличить вероятность эскалации блокировки. Кроме того, они повышают вероятность взаимоблокировок и негативно влияют на параллелизм и производительность. Определив, что запрос, вызывающий эскалацию блокировки, найдите возможности для создания новых индексов или добавления столбцов в существующий индекс, чтобы удалить сканирование индекса или таблицы и максимально повысить эффективность поиска индекса. Просмотрите план выполнения и, возможно, создайте новые некластеризованные индексы для повышения производительности запросов. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Server.

    Одна из целей этой оптимизации заключается в том, чтобы индексы поиска возвращали как можно меньше строк, чтобы свести к минимуму затраты на поиск закладок (максимизировать избирательность индекса для запроса). Если SQL Server предполагает, что логический оператор Поиска закладок вернет много строк, он может использовать PREFETCH предложение для поиска закладки. Если SQL Server использует PREFETCH для поиска закладок, он должен увеличить уровень изоляции транзакции части запроса до "повторяемого чтения" для части запроса. Это означает, что то, что может выглядеть как SELECT оператор на уровне изоляции "чтение-фиксация", может получить много тысяч ключевых блокировок (как для кластеризованного индекса, так и для одного некластеризованного индекса). Это может привести к тому, что такой запрос превысит пороговые значения эскалации блокировки. Это особенно важно, если вы обнаружите, что эскалация блокировки является общей блокировкой таблицы, хотя обычно они не встречаются на уровне изоляции по умолчанию "read-committed". Если предложение Bookmark Lookup WITH PREFETCH вызывает эскалацию, рассмотрите возможность добавления столбцов в некластеризованный индекс, который отображается в поиске индекса, или логический оператор Index Scan под логическим оператором Поиска закладок в плане запроса. Можно создать покрывающий индекс (индекс, включающий все столбцы таблицы, которые использовались в запросе) или по крайней мере индекс, охватывающий столбцы, которые использовались для условий соединения, или в предложении WHERE, если включать все данные в список "выбрать столбец" нецелесообразно.

    Соединение вложенных циклов также может использовать PREFETCH, и это приводит к тому же поведению блокировки.

  • Эскалация блокировки не может произойти, если другой SPID в настоящее время содержит несовместимую блокировку таблицы. Эскалация блокировки всегда перерастает в блокировку таблицы и никогда не до блокировки страницы. Кроме того, если попытка эскалации блокировки завершается неудачей из-за того, что другой SPID содержит несовместимую блокировку TAB, запрос, который попытался эскалацию, не блокируется во время ожидания блокировки TAB. Вместо этого он продолжает получать блокировки на исходном, более детализированном уровне (строка, ключ или страница), периодически выполняя дополнительные попытки эскалации. Таким образом, одним из способов предотвращения эскалации блокировки для конкретной таблицы является получение и удержание блокировки на другом соединении, несовместимом с типом блокировки с эскалацией. Блокировка IX (исключающее намерение) на уровне таблицы не блокирует строки или страницы, но по-прежнему несовместима с эскалацией S (shared) или X (exclusive) TAB lock. Например, предположим, что необходимо выполнить пакетное задание, изменяющее много строк в таблице mytable и вызвавшее блокировку из-за эскалации блокировки. Если это задание всегда завершается менее чем за час, можно создать задание Transact-SQL, содержащее следующий код, и запланировать запуск нового задания за несколько минут до начала пакетного задания:

    BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

    Этот запрос получает и удерживает блокировку IX для mytable в течение одного часа. Это предотвращает эскалацию блокировки таблицы в течение этого времени. Этот пакет не изменяет данные и не блокирует другие запросы (если другой запрос не принудительно блокирует таблицу с помощью указания TABLOCK или если администратор отключил блокировки страниц или строк с помощью ALTER INDEX).

  • Устранение эскалации блокировки, вызванной отсутствием SARGability— термин реляционной базы данных, используемый для описания того, может ли запрос использовать индексы для предикатов и столбцов соединения. Дополнительные сведения о SARGability см. в разделе Рекомендации по запросам в руководстве по проектированию. Например, довольно простой запрос, который, как представляется, не запрашивает много строк или, возможно, одну строку, по-прежнему может в конечном итоге сканировать всю таблицу или индекс. Это может произойти, если в левой части предложения WHERE есть функция или вычисление. К таким примерам, в которых отсутствует возможность SARGability, относятся неявное или явное преобразование типов данных, системная функция ISNULL(), определяемая пользователем функция со столбцом, переданным в качестве параметра, или вычисление в столбце, например WHERE CONVERT(INT, column1) = @a или WHERE Column1*Column2 = 5. В таких случаях запрос не может ПОЛУЧИТЬ существующий индекс, даже если он содержит соответствующие столбцы, так как все значения столбцов должны быть извлечены сначала и переданы в функцию. Это приводит к сканированию всей таблицы или индекса и к приобретению большого количества блокировок. В таких случаях SQL Server может достичь порогового значения эскалации числа блокировок. Решение заключается в том, чтобы избежать использования функций для столбцов в предложении WHERE, обеспечивая условия SARGable.

Отключение эскалации блокировки

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

  • Уровень таблицы: Вы можете отключить эскалацию блокировки на уровне таблицы. См. раздел ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Чтобы определить целевую таблицу, изучите запросы T-SQL. Если это невозможно, используйте расширенные события, включите событие lock_escalation и просмотрите столбец object_id . Кроме того, можно использовать событие Lock:Escalation и проверить ObjectID2 столбец с помощью SQL Profiler.
  • Уровень экземпляра: Вы можете отключить эскалацию блокировки, включив любой из флагов трассировки 1211 или 1224 или оба для экземпляра. Однако эти флаги трассировки отключают все эскалации блокировки глобально в экземпляре SQL Server. Эскалация блокировки служит полезной цели в SQL Server, повышая эффективность запросов, которые в противном случае замедляются из-за затрат на получение и освобождение нескольких тысяч блокировок. Эскалация блокировки также помогает свести к минимуму необходимые объемы памяти для отслеживания блокировок. Память, которую SQL Server может динамически выделять для структур блокировки, ограничена. Таким образом, если отключить эскалацию блокировки и объем памяти блокировки будет достаточно велик, любая попытка выделить дополнительные блокировки для любого запроса может завершиться ошибкой и создать следующую запись об ошибке:

Ошибка: 1204, серьезность: 19, состояние: 1
В настоящее время SQL Server не удается получить ресурс LOCK. Повторно запустите инструкцию при меньшем количестве активных пользователей или попросите системного администратора проверка конфигурацию SQL Server блокировки и памяти.

Примечание.

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

Эти флаги трассировки (-T1211 или -T1224) можно добавить с помощью диспетчер конфигурации SQL Server. Чтобы новый параметр запуска вступил в силу, необходимо перезапустить службу SQL Server. При выполнении DBCC TRACEON (1211, -1) запроса или DBCC TRACEON (1224, -1) флаг трассировки вступает в силу немедленно.
Однако если не добавить параметр -T1211 или -T1224 в качестве параметра запуска, при перезапуске службы SQL Server будет потеряно действие DBCC TRACEON команды. Включение флага трассировки предотвращает любые будущие эскалации блокировки, но не отменяет эскалации блокировки, которые уже имели место в активной транзакции.

Если вы используете подсказку блокировки, например ROWLOCK, это изменяет только первоначальный план блокировки. Указания блокировки не предотвращают эскалацию блокировки.

Пороговые значения эскалации блокировки

Эскалация блокировки может происходить при одном из следующих условий:

  • Достигнуто пороговое значение памяти — достигнуто пороговое значение памяти в 40 процентов от объема памяти блокировки. Если объем памяти блокировки превышает 24 процента буферного пула, можно активировать эскалацию блокировки. Объем памяти блокировки ограничен 60 процентами видимого буферного пула. Пороговое значение эскалации блокировки устанавливается на уровне 40 процентов памяти блокировки. Это 40 процентов из 60 процентов буферного пула, или 24 процента. Если объем памяти блокировки превышает ограничение в 60 процентов (это гораздо более вероятно, если эскалация блокировки отключена), все попытки выделить дополнительные блокировки завершаются сбоем и 1204 возникают ошибки.

  • Достигается пороговое значение блокировки . После проверки порогового значения памяти оценивается количество блокировок, полученных в текущей таблице или индексе. Если число превышает 5000, активируется эскалация блокировки.

Чтобы понять, какое пороговое значение достигнуто, используйте расширенные события, включите событие lock_escalation и просмотрите столбцы escalated_lock_count и escalation_cause . Кроме того, используйте событие Lock:Escalation и проверьте EventSubClass значение, где "0 - LOCK_THRESHOLD" означает, что инструкция превысила пороговое значение блокировки, а "1 - MEMORY_THRESHOLD" указывает, что оператор превысил пороговое значение памяти. Кроме того, изучите столбцы IntegerData и IntegerData2 .

Рекомендации

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

См. также