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

Применимо к: База данных SQL Azure базе данных SQL в Microsoft Fabric

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

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

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

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

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

Например:

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

Примечание.

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

Availability

Оптимизированная блокировка доступна только в База данных SQL Azure и базе данных SQL Fabric во всех уровнях служб и размерах вычислений.

Оптимизированная блокировка в настоящее время недоступна в Управляемый экземпляр SQL Azure или в SQL Server.

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

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

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

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

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

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

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

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

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

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

В ядро СУБД блокировка — это механизм, который предотвращает одновременное обновление нескольких транзакций для обеспечения свойств 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 (транзакция).

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

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

Оптимизированная блокировка и блокировка после квалификации (LAQ)

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

Без оптимизированной блокировки предикаты запросов проверяются по строкам в сканировании, сначала принимая блокировку строки обновления (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), строка изменяется. Если есть активная транзакция, ядро СУБД ожидает завершения и повторно вычисляет предикат во время изменения, так как другая транзакция могла изменить строку. Если предикат по-прежнему удовлетворен, строка изменяется.

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

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;

Изменения поведения запросов с оптимизированной блокировкой и 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 — происходит, когда задача ожидает общей блокировки типа XACT wait_resource с намерением прочитать.
    • LCK_M_S_XACT_MODIFY — возникает, когда задача ожидает общей блокировки типа XACT wait_resource с намерением изменить.
    • LCK_M_S_XACT — Происходит, когда задача ожидает общей блокировки типа XACT wait_resource , где намерение не может быть выведено. Такой подход используется нечасто.
• Блокировка видимости ресурсов
  • XACT блокировка ресурсов. Дополнительные сведения см. в sys.dm_tran_locks resource_description (Transact-SQL).
• Видимость ресурса ожидания
  • XACT дождитесь ресурсов. Дополнительные сведения см. в sys.dm_exec_requests wait_resource (Transact-SQL).
• Граф взаимоблокировки
  • В каждом ресурсе в отчете <resource-list>взаимоблокировки каждый <xactlock> элемент сообщает базовые ресурсы и конкретную информацию о блокировках каждого элемента взаимоблокировки. Дополнительные сведения и пример см. в статье "Оптимизированная блокировка и взаимоблокировка".

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

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

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

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

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

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

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

Хотя табличные и запросы, такие как UPDLOCK, XLOCKREADCOMMITTEDLOCK, 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 да.

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

См . статью "Оптимизировано блокировка"?.

Что происходит при отключении ускоренного восстановления базы данных (ADR) в базе данных?

Если ADR отключен, оптимизированная блокировка также отключена.

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

Для клиентов, использующих RCSI, для принудительной блокировки между двумя запросами при включении оптимизированной READCOMMITTEDLOCK блокировки используйте указание запроса.

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

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

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

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

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

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