Как увеличить таймаут блокировок sql

Слишком долгое выполнение SQL запроса (таймаут)

Используется MS SQL Server (Microsoft Azure), запрос выполняется из ASP .NET Core приложения (EF Core). Таблица с 16 колонками, в том числе колонкой ID ( Guid , PK) и колонкой CreationDate ( DateTime , есть Index). В таблице обычно очень много записей, но сейчас я сократил их количество до 23 тысяч (тестовая БД). Мне нужно удалять старые записи из таблицы. Если я пытаюсь удалить даже одну запись через EF Core (методом .Remove() и затем SaveChangesAsync() ), срабатывает таймаут. Пробовал делать удаление через .Database.ExecuteSqlRawAsync(«DELETE FROM MyTableName WHERE CreationDate < DATEADD(day, -1, GETDATE())") - всё равно таймаут. Выполнил этот же самый SQL запрос через SQL Server Management Studio - он выполнился, но это заняло 33 минуты 58 секунд (23294 записи было удалено). После того как в базе осталось меньше чем 200 записей, то SQL запрос из EF Core тоже стал работать (правда удалять нечего, так что по факту 0 записей удаляет на данный момент, но таймаута нет). БД в Azure имеет Tier S1 (20 DTUs) - может этого действительно мало? Или всё таки тут закралась какая-то ошибка? Я раньше работал только с небольшими базами, так что может упускаю что-то очевидное.

• sql-server
• entity-framework-core

Отслеживать
задан 9 апр 2021 в 16:51
111 7 7 бронзовых знаков
за 33 минуты можно в экселе вручную таблицу просмотреть )
10 апр 2021 в 14:22
Индекс по CreationDate есть?
13 апр 2021 в 18:53

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

14 апр 2021 в 8:07

0

Сортировка: Сброс на вариант по умолчанию

Знаете кого-то, кто может ответить? Поделитесь ссылкой на этот вопрос по почте, через Твиттер или Facebook.

• sql-server
• entity-framework-core

Важное на Мете

Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2024 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2024.4.30.8412

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

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

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 323630

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

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

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

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

Если блокировка, блокирующая других пользователей, является чем-либо иным, чем блокировка TAB (на уровне таблицы), которая имеет режим блокировки S (общий) или X (монопольный), эскалация блокировки не является проблемой. В частности, если блокировка TAB является блокировкой намерения (например, режим блокировки IS, IU или IX), это не вызвано эскалацией блокировки. Если проблемы с блокировкой не вызваны эскалацией блокировки, см. инструкции по устранению неполадок, связанных с блокировкой, см. в разделе Inf: Understanding and resolveing 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; 

BEGIN TRAN; SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0; WAITFOR DELAY '1:00:00'; COMMIT TRAN; 

Отключение эскалации блокировки

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

• Уровень таблицы: Вы можете отключить эскалацию блокировки на уровне таблицы. См. раздел ALTER TABLE . SET (LOCK_ESCALATION = DISABLE) . Чтобы определить целевую таблицу, изучите запросы T-SQL. Если это невозможно, используйте расширенные события, включите событие lock_escalation и просмотрите столбец object_id . Кроме того, можно использовать событие Lock:Escalation и проверить 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 в качестве параметра запуска, при перезапуске службы SQL Server будет потеряно действие DBCC TRACEON команды. Включение флага трассировки предотвращает любые будущие эскалации блокировки, но не отменяет эскалации блокировки, которые уже имели место в активной транзакции.

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

Пороговые значения эскалации блокировки

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

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

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

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

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

См. также

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

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

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

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

    Включение изоляции моментальных снимков с фиксацией чтения (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
    • Ускоренное восстановление баз данных

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

    Были ли сведения на этой странице полезными?

    Типичные взаимные блокировки в MS SQL и способы борьбы с ними

    Чаще всего deadlock описывают примерно следующим образом:
    Процесс 1 блокирует ресурс А.
    Процесс 2 блокирует ресурс Б.
    Процесс 1 пытается получить доступ к ресурсу Б.
    Процесс 2 пытается получить доступ к ресурсу А.
    В итоге один из процессов должен быть прерван, чтобы другой мог продолжить выполнение.
    Но это простейший вариант взаимной блокировки, в реальности приходится сталкиваться с более сложными случаями. В этой статье мы расскажем с какими взаимными блокировками в MS SQL нам приходилось встречаться и как мы с ними боремся.

    

    Немного теории

    • Гранулярность блокировок и иерархии блокировок
    • Режимы блокировки
    • Блокировка диапазона ключей
    • Уровни изоляции транзакций

    Выбор уровня изоляции транзакции

    При использовании транзакций с уровнем изоляции serializable могут происходить любые взаимные блокировки. При использовании уровня изоляции repeatable read некоторые из описанных ниже взаимных блокировок не могут произойти. У транзакций с уровнем изоляции read committed могут возникнуть только простейшие взаимные блокировки. Транзакция с уровнем изоляции read uncommitted практически не влияет на скорость работы других транзакций и в ней не могут возникнуть взаимные блокировки из-за чтения, так как она не накладывает shared блокировки (правда могут быть взаимные блокировки с транзакциями изменяющими схему БД).

    • Если транзакция изменяет данные в БД и при этом проверяет, чтобы эти данные не противоречили уже существующим записям в БД, то для нее скорее всего нужен уровень изоляции serializable. Но если вставка новых записей в параллельных транзакциях никак не может повлиять на результат текущей транзакции то можно использовать уровень изоляции repeatable read.
    • Для чтения данных обычно достаточно использовать уровень изоляции по умолчанию (read committed) без какой либо транзакции. Однако при чтении агрегатов, части которых могут быть изменены во время чтения, может понадобится использовать транзакцию с уровнем изоляции repeatable read или даже serializable, иначе можно получить из базы агрегат в некорректном состоянии, в котором он может быть только в процессе выполнения транзакции изменения.
    • Если необходимо отображать real time статистику по постоянно изменяющимся данным, то зачастую лучше использовать уровень изоляции read uncommitted. В этом случае в статистике будет некоторое количество грязных данных (хотя вряд ли это будет заметно), но зато построение отчетов практически не будет влиять на скорость работы системы.

    Retry on deadlock

    В достаточно сложной системе, насчитывающей десятки разнообразных типов бизнес транзакций, вряд ли получится спроектировать все транзакции таким образом, чтобы deadlock не мог возникнуть ни при каких условиях. Не стоит тратить время на предотвращение взаимных блокировок, вероятность возникновения которых крайне мала. Но, чтобы не портить user experience, в случае, когда операция прерывается из-за взаимной блокировки, ее нужно повторить. Для того, чтобы операцию можно было безопасно повторить, она не должна изменять входные данные и должна быть обернута в одну транзакцию (либо вместо всей операции, надо оборачивать в свой RetryOnDeadlock каждую SQL транзакцию в операции).

    Вот пример функции RetryOnDeadlock на C#:

     private const int DefaultRetryCount = 6; private const int DeadlockErrorNumber = 1205; private const int LockingErrorNumber = 1222; private const int UpdateConflictErrorNumber = 3960; private void RetryOnDeadlock( Action action, int retryCount = DefaultRetryCount) < if (action == null) throw new ArgumentNullException("action"); var attemptNumber = 1; while (true) < var dataContext = CreateDataContext(); try < action(dataContext); break; >catch (SqlException exception) < if(!exception.Errors.Cast().Any(error => (error.Number == DeadlockErrorNumber) || (error.Number == LockingErrorNumber) || (error.Number == UpdateConflictErrorNumber))) < throw; >else if (attemptNumber == retryCount + 1) < throw; >> finally < dataContext.Dispose(); >attemptNumber++; > > 

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

    Борьба с простейшими взаимными блокировками

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

    В применении к MS SQL этот совет, немного упрощая, можно выразить следующим образом: в разных транзакциях, изменяющих несколько таблиц, первой должна изменяться одна и та же таблица.

    Shared->Exclusive lock escalation

    1. Транзакция 1 читает запись (накладывается S-блокировка).
    2. Транзакция 2 читает эту же запись (накладывается вторая S-блокировка).
    3. Транзакция 1 пытается изменить запись и ждет, когда транзакция 2 закончится и отпустит свою S-блокировку.
    4. Транзакция 2 пытается изменить эту же запись и ждет, когда транзакция 1 закончится и отпустит свою S-блокировку

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

    SELECT * FROM MyTable WITH (UPDLOCK) WHERE > Если вы используете ORM и не можете управлять тем, как запрашивается сущность из БД, то вам придется выполнить отдельный запрос на чистом SQL для блокировки записи прежде чем запрашивать ее из БД. Важно, что накладывающий update блокировку запрос должен быть первым запросом, обращающимся к этой записи в данной транзакции, иначе будет возникать все та же взаимная блокировка, но при попытке наложить update блокировку, а не при изменении записи. 
    Накладывая update блокировку мы заставляем все транзакции, обращающиеся к одному ресурсу, выполняться по очереди, но обычно транзакции изменяющие один и тот же ресурс в принципе нельзя делать параллельно, так что это нормально. 
    Такая взаимная блокировка может возникнуть в любой транзакции, которая проверяет данные перед их изменением, но для редко изменяющихся сущностей, можно использовать RetryOnDeadlock. Подход с предварительной update блокировкой достаточно использовать только для сущностей, которые часто меняются разными процессами параллельно.
    

    Пример
    Пользователи заказывают призы за баллы. Количество призов каждого вида ограниченно. Система не должна позволить заказать больше призов, чем есть в наличии. Из-за особенностей промоакции периодически происходят набеги пользователей, желающих заказать один и тот же приз. Если использовать RetryOnDeadlock в данной ситуации, то во время набега пользователей заказ приза в большинстве случаев будет падать по web таймауту.

    1. Получаем запись о виде приза, накладывая update блокировку.
    2. Проверяем количество оставшихся призов. Если оно равно 0, завершаем транзакцию и возвращаем соответствующий ответ пользователю.
    3. Если призы еще есть, уменьшаем количество оставшихся призов на 1.
    4. Добавляем запись о заказанном призе.

    SELECT count(*) FROM OrderedPrizes WITH (UPDLOCK) WHERE PrizeId = @PrizeId

    Большинство взаимных блокировок, описанных далее, происходят похожим образом — мы пытаемся изменить данные после того как наложили на них Shared блокировку. Но в каждом из этих случаев есть свои нюансы.

    Выборки по неиндексируемым полям

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

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

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

    Проверка на наличие перед вставкой

    Пример
    Нам необходимо проверить, есть ли в БД пользователь с таким Id в Facebook, перед тем как его добавлять. Так как мы работаем с одной строчкой в БД, создается ощущение, что будет блокироваться только она и вероятность взаимной блокировки невелика. Однако если в транзакции с уровнем изоляции Serializable попытаться выбрать несуществующее значение (и эта колонка входит в индекс), то будет наложена shared блокировка на все ключи между двумя ближайшими значениям, которые есть в таблице. Например, если в базе есть Id 15 и Id 1025, и нет ни одного значения между ними, то при выполнении SELECT * FROM Users WHERE FacebookId = 500 будет наложена Shared блокировка на ключи с 15 до 1025. Если до вставки другая транзакция проверит есть ли пользователь с FacebookId = 600 и попытается его вставить, то произойдёт взаимная блокировка. Если в БД уже много потребителей, у которых заполнен FacebookId, то вероятность взаимной блокировки будет невелика и нам достаточно использовать RetryOnDeadlock. Но если выполнять множество таких транзакций на почти пустой базе, то взаимные блокировки будут возникать достаточно часто, чтобы это сильно сказалось на производительности.

    У нас эта проблема возникла при параллельном импорте потребителей от новых клиентов (для каждого клиента мы создаем новую пустую БД). Так как нас на данный момент устраивает скорость однопоточного импорта, мы просто отключили параллелизм. Но в принципе проблема решается также как и в выше описанном примере, надо использовать update блокировку:

    SELECT * FROM Users WITH(UPDLOCK) WHERE FacebookId = 500

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

    Взаимные блокировки на сложных агрегатах

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

    Пример
    В БД хранится персональные данные потребителя, его идентификаторы в соц сетях, заказы в интернет магазине, записи об отправленных ему письмах.

    

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

    Для избежания взаимных блокировок нужно начинать любую транзакцию со следующего запроса:

    SELECT * FROM Customers WITH (UPDLOCK) WHERE > В этом случае в один момент только одна транзакция сможет изменять данные, относящиеся к конкретному потребителю и взаимные блокировки не будут возникать в независимости от того насколько сложен агрегат потребителя. 
    Можно попробовать изменить схему хранения данных так, чтобы транзакции, отправляющие письма и регистрирующие покупки не меняли технические пометки в потребителе. Тогда информацию о заказах и отправленных письмах можно будет изменять параллельно с изменением потребителя. В этом случае мы фактически выносим эти данные за рамки агрегата «потребитель».
     

    • надо стремится к тому, чтобы любая транзакция в системе не изменяла более одного агрегата
    • в любой транзакции, изменяющей агрегат, первый запрос, обращающийся к данным агрегата, должен накладывать exclusive или update блокировку на корень агрегата
    • для увеличения степени параллелизма системы надо делать агрегаты настолько маленькими насколько это возможно

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

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

    

    Пример

    1. DirectCRM добавляет действия о факте отправки с Id N, N+1, N+2 и т. д.
    2. Email-шлюз параллельно обрабатывает отправку всех этих писем.
    3. В момент выдачи действий об отправке, максимальное значение RootCustomerActionId равняется N-1.
    4. При проверке нет ли записи об успешной отправке письма N, N+1, N+2 и т.д., накладывается shared блокировка на записи с RootCustomerActionId больше или равные N-1.
    5. Каждая из транзакций пытается вставить свою запись об отправке и ждет когда другая транзакция освободит shared блокировку.
    6. В итоге из всех параллельно выполняющихся транзакций выполнится только одна, а остальные будут откачены.
    • Отказаться от identity и генерить Id действия так, чтобы новый Id в большинстве случаев оказывался между двумя существующими. Тогда вероятность взаимной блокировки будет невелика.
    • При выдачи действия о факте отправки также вставлять запись в HierarchicalCustomerActions (у этой записи RootCustomerActionId будет равен CustomerActionId). Тогда при запросе записей с RootCustomerActionId = N будет накладываться shared блокировка на значения N и N+1, так как записи с такими значениями уже есть. Чтобы не возникали взаимные блокировки при параллельной вставке действий об успешной отправке, ссылающихся на действия о факте отправки с Id N и с Id N+1, надо накладывать update блокировку при запросе к HierarchicalCustomerActions. В итоге при параллельной вставке нескольких записей происходит следующее:
      1. Транзакция 1 запрашивает записи с RootCustomerActionId = N. При этом накладывает update блокировка на значения ключа N и N+1.
      2. Транзакция 2 запрашивает записи с RootCustomerActionId = N+1. При этом он пытается наложить update блокировку на значения N+1 и N+2, поэтому ждет завершения транзакции 1.
      3. Транзакция 3 запрашивает записи с RootCustomerActionId = N+2. При этом накладывает update блокировка на значения ключа N+2 и N+3. Теперь транзакция 2 должна также дождаться завершения транзакции 3.
      4. Транзакции 1 и 3 отрабатывают параллельно.
      5. Выполняется транзакция 2.

    • Блог компании Mindbox
    • Программирование
    • SQL
    • Проектирование и рефакторинг
    • Microsoft SQL Server
    Похожие публикации:

    1. Как проверить sas диск
    2. Как проверить делится ли число на 4
    3. Как сохранить файл в рабочей директории питон
    4. Как убрать вопросительный знак