Оптимизированная блокировка

Применимо к: SQL Server 2025 (17.x) База данных Azure SQL Управляемый экземпляр Azure SQL База данных SQL в Microsoft Fabric

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

Что такое оптимизированная блокировка?

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

Оптимизированная блокировка состоит из двух основных компонентов: блокировки идентификатора транзакции (TID) и блокировки после квалификации (LAQ).

Идентификатор транзакции (TID) — это уникальный идентификатор транзакции. Каждая строка помечена последним ТИД, изменив его. Вместо потенциально большого количества блокировок идентификатора ключа или строки для защиты всех измененных строк используется одна блокировка на TID. Дополнительные сведения см. в разделе "Блокировка идентификатора транзакции (TID).
Блокировка после квалификации (LAQ) — это оптимизация, которая оценивает предикаты запросов с помощью последней зафиксированной версии строки без получения блокировки, что повышает параллелизм. Для LAQ требуется изоляция моментальных снимков на уровне согласованного чтения (RCSI). Дополнительные сведения см. в разделе "Блокировка после квалификации" (LAQ).

Рассмотрим пример.

Без оптимизированной блокировки, обновление 1000 строк в таблице может потребовать 1000 монопольных (X) блокировок строк, удерживаемых до конца транзакции.
При оптимизированной блокировке обновление 1000 строк в таблице может потребовать 1000 X блокировок строк, но каждая блокировка освобождается сразу после обновления каждой строки, и до конца транзакции удерживается только одна X блокировка TID. Так как блокировки выпускаются быстро, использование памяти блокировки уменьшается, а эскалация блокировки гораздо реже возникает, что повышает параллелизм рабочей нагрузки.

Note

Включение оптимизированной блокировки уменьшает или устраняет блокировки строк и страниц, приобретенные операторами языка изменения данных (DML), например INSERT, UPDATE, DELETE. MERGE Он не влияет на другие виды блокировок базы данных и объектов, таких как блокировки схемы.

Availability

В следующей таблице приведены сведения о доступности и состоянии оптимизированной блокировки на платформах SQL.

Platform	Available	Включен по умолчанию
База данных SQL Azure	Yes	Да (всегда включено)
База данных SQL в Microsoft Fabric	Yes	Да (всегда включено)
Управляемый экземпляр Azure SQL^AUTD	Yes	Да (всегда включено)
Управляемый экземпляр SQL Azure²⁰²⁵	Yes	Да (всегда включено)
Управляемый экземпляр SQL Azure²⁰²²	No	N/A
SQL Server 2025 (17.x)	Yes	Нет (можно включить для каждой базы данных)
SQL Server 2022 (16.x) и более ранние версии	No	N/A

Включение и отключение

Чтобы включить или отключить оптимизированную блокировку для базы данных SQL Server, используйте ALTER DATABASE ... SET OPTIMIZED_LOCKING = ON | OFF команду. Дополнительные сведения см. в статье Параметры ALTER DATABASE SET.

Оптимизированная блокировка основана на других функциях базы данных:

Чтобы включить оптимизированную блокировку, необходимо включить ускоренное восстановление базы данных (ADR ). И наоборот, чтобы отключить ADR, сначала необходимо отключить оптимизированную блокировку, если она включена.
Для наиболее эффективного использования оптимизированной блокировки для базы данных следует включить изоляцию зафиксированных моментальных снимков чтения (RCSI ). Компонент LAQ оптимизированной блокировки действует только в том случае, если включен RCSI.

ADR всегда включается в Базе данных SQL Azure, Управляемом экземпляре SQL Azure и базе данных SQL в Microsoft Fabric. RCSI включен по умолчанию в Базе данных SQL Azure и базе данных SQL в Microsoft Fabric.

Чтобы убедиться, что эти параметры включены для текущей базы данных, подключитесь к базе данных и выполните следующий запрос T-SQL:

SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM sys.databases
WHERE name = DB_NAME();

Включена ли оптимизированная блокировка?

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

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_enabled;

Result	Description
`0`	Оптимизированная блокировка отключена.
`1`	Оптимизированная блокировка включена.
`NULL`	Оптимизированная блокировка недоступна.

Вы также можете использовать представление каталога sys.database . Например, чтобы узнать, включена ли оптимизированная блокировка для всех баз данных, выполните следующий запрос:

SELECT database_id,
       name,
       is_optimized_locking_on
FROM sys.databases;

Обзор блокировки

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

В механизме ядра СУБД блокировка — это механизм, который предотвращает одновременное обновление одних и тех же данных несколькими транзакциями, чтобы гарантировать свойства ACID транзакций.

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

Если несколько транзакций пытаются получить доступ к одним и тем же данным одновременно, ядро СУБД должно разрешать потенциально сложные конфликты с одновременными операциями чтения и записи. Блокировка — это один из механизмов, с помощью которых подсистема может обеспечить семантику для уровней изоляции транзакций ANSI SQL. Хотя блокировка баз данных является важной, снижение параллелизма, взаимоблокировки, сложность и накладные расходы на блокировки могут повлиять на производительность и масштабируемость.

Блокировка идентификатора транзакции (TID)

Если уровни изоляции на основе строк используются или когда включен ADR, каждая строка в базе данных внутренне содержит идентификатор транзакции (TID). TID сохраняется со строкой. Каждая транзакция, изменяющая строку, помечает строку её TID.

При блокировке TID вместо того, чтобы взять блокировку на ключ строки, блокировка берется на TID строки. Изменяющаяся транзакция содержит блокировку X на его TID. Другие транзакции получают блокировку S на TID, чтобы ждать завершения первой транзакции. При блокировке TID блокировки страницы и строк продолжают приниматься для изменений, но каждая страница и блокировка строк освобождается сразу после изменения каждой строки. Единственная блокировка, удерживаемая до конца транзакции, — это одна X блокировка ресурса TID, заменяющая несколько блокировок страницы и строки (ключа).

Рассмотрим следующий пример, показывающий блокировки текущего сеанса во время активной транзакции записи:

/* Is optimized locking is enabled? */
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_enabled;

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Если оптимизированная блокировка включена, запрос содержит только одну X блокировку ресурса XACT (транзакция).

Если оптимизированная блокировка не включена, один и тот же запрос содержит четыре блокировки — одну блокировку IX (намерение к эксклюзивной блокировке) на странице, содержащей строки, и три ключевых блокировки X для каждой строки.

sys.dm_tran_locks динамическое управляемое представление (DMV) полезно для изучения или устранения проблем с блокировками. Здесь он используется для наблюдения за оптимизированной блокировкой в действии.

Блокировка после квалификации (LAQ)

Опираясь на инфраструктуру TID, компонент LAQ оптимизированной блокировки изменяет способ, которым инструкции DML, такие как INSERT, UPDATE, и DELETE, получают блокировки.

Без оптимизированной блокировки предикаты запросов проверяются по строкам в сканировании, сначала принимая блокировку строки обновления (U). Если предикат удовлетворен, блокировка монопольной (X) строки принимается перед обновлением строки и удерживается до конца транзакции.

При оптимизированной блокировке и READ COMMITTED включении уровня изоляции моментальных снимков (RCSI) предикаты можно оптимистично проверять на последней зафиксированной версии строки без каких-либо блокировок. Если предикат не удовлетворяет, запрос переходит к следующей строке в сканировании. Если предикат удовлетворен, X блокировка строки принимается для обновления строки.

Другими словами, блокировка принимается после квалификации строки для изменения. Блокировка X строки освобождается сразу после завершения обновления строки до конца транзакции.

Так как оценка предиката выполняется без получения блокировок, одновременные запросы, изменяющие разные строки, не блокируют друг друга.

Рассмотрим пример.

/* Confirm that optimized locking and read committed snapshot isolation (RCSI) are both enabled on this database. */
SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_optimized_locking_on,
       is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

Сеанс 1	Сеанс 2
`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Без оптимизированной блокировки сеанс 2 блокируется, так как сеанс 1 содержит блокировку U сеанса строки 2 необходимо обновить. Однако при оптимизированной блокировке сеанс 2 не блокируется, так как блокировки U не применяются, и поскольку в последней зафиксированной версии строки 1 столбец a равен 1, что не соответствует предикату сеанса 2.

LAQ выполняется оптимистично при предположении, что строка не изменяется после проверки предиката. Если предикат удовлетворен и строка не была изменена после проверки предиката, она изменяется текущей транзакцией.

U Так как блокировки не принимаются, параллельная транзакция может изменить строку после вычисления предиката. Если активная транзакция содержит блокировку X TID в строке, ядро СУБД ожидает завершения. Если строка изменилась после оценки предиката ранее, ядро СУБД переоценивает (переквалифицирует) предикат еще раз перед изменением строки. Если предикат по-прежнему удовлетворен, строка изменяется.

Переквалификация предиката поддерживается подмножеством операторов обработчика запросов. Если требуется повторное вычисление предиката, но план запроса использует оператор, который не поддерживает переквалификацию предиката, ядро СУБД внутренне прерывает обработку инструкций и перезапускает его без laQ. При возникновении такого прерывания срабатывает расширенное событие lock_after_qual_stmt_abort.

Некоторые инструкции, например UPDATE инструкции с присваиванием переменных и инструкции с предложением OUTPUT, не могут быть прерваны и перезапущены, не изменяя их семантику. Для таких заявлений LAQ не используется.

В следующем примере предикат вычисляется повторно, так как другая транзакция изменила строку:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

Сеанс 1	Сеанс 2
`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Пропуск блокировок индекса (SIL)

При блокировке TID для изменения строк используются краткосрочные исключительные (X) блокировки строк и блокировки страниц с монопольным намерением (IX). Если используются RCSI и LAQ, эти блокировки необходимы только в том случае, если могут быть другие запросы, обращающиеся к строке и ожидающие, что она будет стабильной. Примеры таких запросов включают те, которые выполняются под уровнями изоляции REPEATABLE READ или SERIALIZABLE, или с использованием соответствующих подсказок блокировки. Такие запросы называются запросами блокировки строк (RLQ).

Если нет запросов RLQ, обращаюющихся к строке, ядро СУБД может пропускать блокировку строк и страниц при изменении строки и использовать только монопольную блокировку страницы. Эта оптимизация снижает затраты на блокировку при сохранении семантики транзакций ACID. Пропуск блокировок строк и страниц особенно выгоден для транзакций, модифицирующих значительное количество строк.

В настоящее время оптимизация SIL используется только в следующих случаях:

INSERT выражения на кучах.
- IX Блокировки страниц пропускаются.
UPDATE инструкции для кластеризованных индексов, некластеризованных индексов и heaps.
- IX Блокировки страниц и X блокировки строк не применяются.

Оптимизация SIL в настоящее время не используется в следующих случаях:

ИнструкцииDELETE .
UPDATE операторы кучи, если строка содержит существующие указатели пересылки или если новые указатели пересылки добавляются обновлением.
Если измененная строка содержит столбцы с использованием типов данных LOB, таких как varchar(max), nvarchar(max), varbinary(max), и json.
Для строк на страницах, разделенных в одной транзакции.

Эвристика LAQ

Как описано в разделе "Блокировка после квалификации" (LAQ), выражения, использующие операторы запросов, которые не поддерживают переквалификацию предиката, могут быть внутренне перезапущены и обработаны без LAQ. Если это происходит часто, затраты на повторную обработку могут стать значительными. Чтобы свести к минимуму затраты, оптимизированная блокировка использует механизм обратной связи на основе эвристики, который отключает LAQ, если затраты превышают пороговые значения.

В целях механизма обратной связи работа, выполняемая утверждением, измеряется в количестве логических чтений. Если ядро СУБД изменяет строку, которая была изменена другой транзакцией после начала обработки инструкции, то работа, выполняемая инструкцией, обрабатывается как потенциально потраченная, так как инструкция может потребоваться повторно обработать.

При выполнении инструкций ядро СУБД сохраняет данные обратной связи LAQ, которые отслеживают потенциально потерянную работу, случаи повторной обработки инструкций и общую работу, выполняемую потенциально повторно обрабатываемыми инструкциями.

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

Данные обратной связи LAQ отслеживаются на двух уровнях:

Для плана запроса.
- Движок баз данных начинает отслеживать обратную связь LAQ для плана при первом повторном выполнении запроса.
- Если запрос фиксируется в хранилище запросов, то обратная связь LAQ также записывается в хранилище запросов. Движок базы данных использует эту обратную связь, чтобы оставлять LAQ включённым или отключённым для плана, если база данных перезапускается.
- Планы запросов с записанной обратной связью LAQ имеют строку с соответствующим plan_id значением в каталоге представления sys.query_store_plan_feedback. Столбцы feature_id и feature_desc установлены на 4 и LAQ Feedback соответственно.
Для базы данных.
- Обратная связь агрегируется для всех выражений, не имеющих данных обратной связи на уровне плана запросов, например, если запрос не сохраняется в хранилище запросов.
- Отслеживание обратной связи осуществляется с момента запуска базы данных и восстанавливается после каждого запуска.

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

Ограничения LAQ

Блокировка после квалификации не используется в следующих сценариях:

При отключении эвристики LAQ.
При конфликтующих подсказках блокировки, таких как UPDLOCK, READCOMMITTEDLOCKXLOCKили HOLDLOCK используются.
Если уровень изоляции транзакций отличается от параметра READ COMMITTED, или если параметр базы данных READ_COMMITTED_SNAPSHOT отключен.
При изменении таблицы имеется индекс columnstore.
Если инструкция DML включает назначение переменной.
Если инструкция DML содержит OUTPUT условие.
Если оператор DML использует несколько операторов поиска или сканирования индекса для чтения измененных строк.
В MERGE инструкциях.

Изменения поведения запросов с оптимизированной блокировкой и RCSI

Одновременные рабочие нагрузки в режиме изоляции моментальных снимков чтения (RCSI), основанные на строгом порядке выполнения транзакций, могут столкнуться с различиями в поведении запросов при включенной оптимизированной блокировке.

Рассмотрим следующий пример, когда транзакция T2 обновляет таблицу t4 на основе столбца b , который был обновлен во время транзакции T1.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Сеанс 1	Сеанс 2
`BEGIN TRANSACTION T1;` `UPDATE t4` `SET b = 2` `WHERE a = 1;`
	`BEGIN TRANSACTION T2;` `UPDATE t4` `SET b = 3` `WHERE b = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

Давайте оценим результаты предыдущего сценария с применением блокировки и без неё после квалификации (LAQ).

Без LAQ

Без LAQ инструкция в транзакции T2 блокируется, UPDATE ожидая завершения транзакции T1. После завершения T1 T2 обновляет столбец b параметров строки, так 3 как его предикат удовлетворен.

После фиксации обоих транзакций таблица t4 содержит следующие строки:

 a | b
 1 | 3

С LAQ

При использовании LAQ транзакция T2 использует последнюю зафиксированную версию строки, где столбец b равен 1 для оценки предиката (b = 2). Строка не квалифицируется; следовательно, он пропускается, и инструкция завершается без блокировки транзакцией T1. В этом примере LAQ удаляет блокировку, но приводит к разным результатам.

После фиксации обоих транзакций таблица t4 содержит следующие строки:

 a | b
 1 | 2

Important

Даже без LAQ приложения не должны предполагать, что движок базы данных гарантирует строгую упорядоченность без использования предупреждений блокировки, когда применяются версионные уровни изоляции на основе строк. Наша общая рекомендация для клиентов, выполняющих одновременные рабочие нагрузки в rcSI, которые зависят от строгого порядка выполнения транзакций (как показано в предыдущем примере), — использовать более строгие уровни изоляции, такие как REPEATABLE READ и SERIALIZABLE.

Дополнения диагностики для оптимизированной блокировки

Следующие улучшения помогают отслеживать и устранять неполадки блокировки и взаимоблокировки при включении оптимизированной блокировки:

Типы ожидания оптимизированной блокировки
- Типы ожидания для блокировки по TID и описание ресурсов в XACT:
  - LCK_M_S_XACT_READ — происходит, когда задача ожидает общей блокировки типа XACTwait_resource с намерением прочитать.
  - LCK_M_S_XACT_MODIFY — возникает, когда задача ожидает общей блокировки типа XACTwait_resource с намерением изменить.
  - LCK_M_S_XACT – Это происходит, когда задача ожидает общей блокировки в типе XACTwait_resource, где намерение не может быть выведено. Этот сценарий не распространен.
Блокировка видимости ресурсов
- XACT блокировка ресурсов. Дополнительные сведения см. resource_description в sys.dm_tran_locks.
Видимость ресурса ожидания
- XACT дождитесь ресурсов. Дополнительные сведения см. в wait_resource.
Граф взаимоблокировки
- В каждом ресурсе в отчете <resource-list>взаимоблокировки каждый <xactlock> элемент сообщает базовые ресурсы и конкретную информацию о блокировках каждого элемента взаимоблокировки. Дополнительные сведения и пример см. в статье "Оптимизированная блокировка и взаимоблокировка".
Расширенные события
- Событие lock_after_qual_stmt_abort запускается при внутренней повторной обработке инструкции из-за конфликта с другой транзакцией. Дополнительные сведения см. в разделе "Блокировка после квалификации" (LAQ).
- Событие locking_stats запускается для каждой базы данных каждые несколько минут и предоставляет статистические статистические данные блокировки для интервала времени, например количество эскалаций блокировки, включение блокировки TID и компонентов LAQ оптимизированной блокировки, а также количество запросов, по которым LAQ не использовался по различным причинам. Это событие запускается, даже если оптимизированная блокировка отключена.
- В SQL Server и Управляемом экземпляре Azure SQL событие locking_stats2 запускается для каждой базы данных каждые несколько минут и предоставляет статистику о пропусках блокировок индексов и эвристике LAQ за этот интервал времени.

Вопросы и ответы

Оптимизирована блокировка по умолчанию как в новых, так и в существующих базах данных?

В Базе данных SQL Azure, Управляемом экземпляре SQL Azure^AUTD и базе данных SQL в Microsoft Fabric да. В SQL Server 2025 (17.x) оптимизированная блокировка отключена по умолчанию, но ее можно включить в любой пользовательской базе данных, которая включила ускоренное восстановление базы данных.

Как определить, включена ли оптимизированная блокировка?

См. включена ли оптимизированная блокировка?

Что делать, если требуется принудительно заблокировать запросы, несмотря на оптимизированную блокировку?

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

Оптимизирована блокировка для вторичных реплик только для чтения?

Нет, так как инструкции DML не могут запускаться на репликах только для чтения, а соответствующие блокировки строк и страниц не применяются.

Оптимизирована блокировка при изменении данных в tempdb и временных таблицах?

В настоящее время нет.

Обратная связь

Была ли эта страница полезна?

Last updated on 2025-11-24