Поделиться через

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

  • Статья

Итоги

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

Исходная версия продукта: SQL Server
Исходный номер базы знаний: 323630

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

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

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

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

Если блокировка, которая блокирует других пользователей, не является блокировкой, отличной от блокировки (табличного уровня), которая имеет режим блокировки S (общий) или X (эксклюзивный), эскалация блокировки не является проблемой. В частности, если блокировка TAB является блокировкой намерений (например, режимом блокировки IS, IU или IX), это не вызвано эскалацией блокировки. Если проблемы с блокировкой не вызваны эскалацией блокировки, см. в разделе INF: Общие сведения и устранение проблем , связанных с блокировкой 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, может получить много тысяч ключей (как на кластеризованном индексе, так и на одном некластеризованном индексе). Это может привести к превышению пороговых значений эскалации блокировки. Это особенно важно, если вы обнаружите, что эскалация блокировки является общей блокировкой таблицы, хотя они обычно не отображаются на уровне изоляции по умолчанию "зафиксировано на чтение". Если предложение Lookup Lookup WITH PREFETCH закладок вызывает эскалацию, рассмотрите возможность добавления столбцов в некластеризованный индекс, который отображается в поиске индекса, или логический оператор проверки индекса под логическим оператором Поиска закладок в плане запроса. Возможно, можно создать охватывающий индекс (индекс, содержащий все столбцы в таблице, которая использовалась в запросе), или по крайней мере индекс, охватывающий столбцы, используемые для условий соединения или в предложении WHERE, если это нецелесообразно, чтобы включить все в список "выбор столбца".

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

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

    BEGIN TRAN;
SELECT * FROM mytable WITH (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:Эскалация и проверьте 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 в качестве параметра запуска, эффект DBCC TRACEON команды теряется при перезапуске службы SQL Server. Включение флага трассировки предотвращает любые будущие эскалации блокировки, но не отменяет эскалации блокировки, которые уже произошли в активной транзакции.

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

Пороги укрупнения блокировок

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

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

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

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

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

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

См. также