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

Применимо к: SQL Server 2025 (17.x) Предварительная версия базы данныхSQL Azure в Microsoft Fabric

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

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

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

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

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

Например:

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

Примечание.

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

Доступность

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

Платформа	В наличии	Включен по умолчанию
База данных SQL Azure	Да	Да (всегда включено)
База данных SQL в Microsoft Fabric	Да	Да (всегда включено)
Управляемый экземпляр SQL AzureAUTD	нет	Не применимо
Управляемый экземпляр SQL Azure2022	нет	Не применимо
Предварительная версия SQL Server 2025 (17.x)	Да	Нет (можно включить для каждой базы данных)
SQL Server 2022 (16.x) и более ранние версии	нет	Не применимо

AUTD применяется к управляемому экземпляру SQL Azure, настроенного с помощью политики обновления Always-up-to-date.

²⁰²² Применяется к Управляемому экземпляру SQL Azure, настроенному с политикой обновления SQL Server 2022 .

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

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

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

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

В Базе данных SQL Azure служба ADR всегда включена, а RCSI включена по умолчанию.

Чтобы убедиться, что эти параметры включены для текущей базы данных, подключитесь к базе данных и выполните следующий запрос 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 IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

Результат	Описание
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 IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

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 строки освобождается сразу после завершения обновления строки до конца транзакции.

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

Например:

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;

Эвристика LAQ

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

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

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

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

Изменения поведения запросов с оптимизированной блокировкой и 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

Внимание

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

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

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

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

Рекомендации по оптимизации блокировки

Включение изоляции моментальных снимков с фиксацией чтения (RCSI)

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

ALTER DATABASE [database-name-placeholder] SET READ_COMMITTED_SNAPSHOT ON;

В База данных SQL Azure rcSI включен по умолчанию и READ COMMITTED является уровнем изоляции по умолчанию. С включенным RCSI и при использовании READ COMMITTED уровня изоляции читатели считывают версию строки из моментального снимка, полученного в начале инструкции. При использовании LAQ записи квалифицируют строки для предиката на основе последней зафиксированной версии строки и без получения U блокировок. При использовании LAQ запрос ожидает только в том случае, если строка квалифицируется и на этой строке есть активная транзакция записи. Квалификация на основе последней зафиксированной версии и блокировка только квалифицированных строк уменьшает блокировку и увеличивает параллелизм.

Помимо уменьшения блокировки, требуется уменьшение памяти блокировки. Это связано с тем, что читатели не принимают никаких блокировок, и записи принимают только короткие блокировки, а не блокировки, которые хранятся до конца транзакции. При использовании более строгих уровней изоляции, таких как REPEATABLE READ или SERIALIZABLE, движок базы данных удерживает блокировки строк и страниц до конца транзакции даже при включенной оптимизированной блокировке как для чтения, так и для записи, что приводит к увеличению блокировок и использования памяти.

Избегайте подсказок блокировки

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

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

Указание таблицы для одной таблицы в запросе не отключает оптимизированную блокировку для других таблиц в том же запросе. Кроме того, оптимизированная блокировка влияет только на поведение блокировки таблиц, обновляемых инструкцией DML, например INSERT, , UPDATEDELETEили MERGE. Например:

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

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

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

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

В предыдущем примере запроса только таблица t6 влияет на подсказку блокировки, но t5 по-прежнему может воспользоваться оптимизированной блокировкой.

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

В предыдущем примере запроса только таблица t5 использует REPEATABLE READ уровень изоляции и удерживает блокировки до конца транзакции. Другие обновления, которые t5 по-прежнему могут воспользоваться оптимизированной блокировкой. То же самое относится к подсказке HOLDLOCK .

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

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

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

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

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

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

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

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

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

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

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

Связанный контент

• Руководство по блокировке и управлению версиями строк транзакций
• Изоляция зафиксированных моментальных снимков считывания (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• Ускоренное восстановление баз данных