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

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

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

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

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

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

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

Например:

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

В этой статье подробно рассматриваются два основных понятия оптимизированной блокировки.

Доступность

В настоящее время оптимизированная блокировка доступна только в База данных SQL Azure. Дополнительные сведения см. в разделе "Где оптимизирована блокировка сейчас"?

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

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

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

Если вы не подключены к базе данных, указанной в DATABASEPROPERTYEX, результат будет NULL. Необходимо получить 0 (оптимизированная блокировка отключена) или 1 (включена).

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

• Оптимизированная блокировка требует включения ускоренного восстановления базы данных (ADR) в базе данных.
• Для наиболее эффективного использования оптимизированной блокировки для базы данных следует включить изоляцию зафиксированных моментальных снимков чтения (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();

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

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

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

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

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

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

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

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

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

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

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

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

BEGIN TRAN
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 TRAN
GO
DROP TABLE IF EXISTS t0;

Тот же запрос без преимущества оптимизированной блокировки создает четыре блокировки:

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

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

Опираясь на инфраструктуру TID, оптимизированная блокировка изменяет способ защиты блокировки запросов.

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

При оптимизированной блокировке и включении уровня изоляции моментальных снимков чтения (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 TRAN UPDATE t1 SET b=b+10 WHERE a=1;
	BEGIN TRAN UPDATE t1 SET b=b+10 WHERE a=2;
COMMIT TRAN
	COMMIT TRAN

Поведение блокирующих изменений с оптимизированной блокировкой в предыдущем примере. Без оптимизированной блокировки сеанс 2 будет заблокирован.

Однако при оптимизированной блокировке сеанс 2 не будет заблокирован, так как последняя зафиксированная версия строки 1 содержит a=1, которая не удовлетворяет предикату сеанса 2.

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

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

CREATE TABLE t2
(a int not null
,b int null);

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

Сеанс 1	Сеанс 2
BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1;
	BEGIN TRAN UPDATE t2 SET b=b+10 WHERE a=1;
COMMIT TRAN
	COMMIT TRAN

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

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

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

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO

Сеанс 1	Сеанс 2
BEGIN TRAN T1 UPDATE t1 SET b=2 WHERE a=1;
	BEGIN TRAN T2 UPDATE t1 SET b=3 WHERE b=2;
COMMIT TRAN
	COMMIT TRAN

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

Без LAQ

Без LAQ транзакция T2 будет заблокирована и дождитесь завершения транзакции T1.

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

 a | b
 1 | 3

С LAQ

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

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

 a | b
 1 | 2

Важно!

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

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

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

• Типы ожидания оптимизированной блокировки
  • XACT Типы ожиданий и описания ресурсов в 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) в базе данных и использовать изоляцию с фиксацией чтения в качестве уровня изоляции по умолчанию. Если параметр не включен, включите RCSI с помощью следующего примера:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

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

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

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

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

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

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

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

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

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

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

Часто задаваемые вопросы

Где сейчас доступна оптимизированная блокировка?

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

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

• все уровни служб DTU
• все уровни служб виртуальных ядер, включая подготовленные и бессерверные

Оптимизированная блокировка в настоящее время недоступна в:

• Управляемый экземпляр SQL Azure
• SQL Server 2022 (16.x)

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

В База данных SQL Azure да.

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

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

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

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

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

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

Можно ли отключить оптимизированную блокировку?

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

Выполните следующие действия, чтобы создать запрос на поддержку из портал Azure для База данных SQL Azure.

1. Сначала убедитесь, что оптимизированная блокировка включена для базы данных.

2. В главном меню на портале Azure выберите Справка и поддержка.

   

3. В разделе Справка и поддержка выберите Создать запрос на поддержку.

   

4. В качестве типа проблемы укажите Техническая.

5. Для подписки, службы и ресурса выберите нужный База данных SQL.

6. Введите "Отключить оптимизированную блокировку".

7. В поле "Тип проблемы" выберите "Производительность" и "Выполнение запросов".

8. Для подтипа проблемы выберите "Блокировка и взаимоблокировка".

9. В дополнительных сведениях укажите как можно больше сведений о том, почему вы хотите отключить оптимизированную блокировку. Мы заинтересованы в просмотре причин и вариантов использования для отключения оптимизированной блокировки с вами.

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

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