Взаимоблокировка SQL Server: создайте её сами

Автор: Pinal Dave, SQL Server Deadlock: Build One With Your Own Hands

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

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

SQL Server разрешает цикл, выбирая одну транзакцию в качестве жертвы, откатывая её и возвращая ошибку 1205. Выжившая транзакция затем может продолжить.

Две транзакции

Совместимость демонстрации: эта демонстрация предназначена для версий SQL Server или баз данных, где оптимизированная блокировка отключена. На платформах SQL Server 2025 и Azure SQL оптимизированная блокировка может помешать воспроизведению этого шаблона.

Выполните это в тестовой базе данных, никогда в продакшене.

-- Выполнить один раз в одноразовой тестовой базе данных.
CREATE TABLE dbo.DeadlockDemoAccounts
(
    AccountID int NOT NULL PRIMARY KEY,
    Balance int NOT NULL
);

CREATE TABLE dbo.DeadlockDemoOrders
(
    OrderID int NOT NULL PRIMARY KEY,
    Status varchar(20) NOT NULL
);

INSERT dbo.DeadlockDemoAccounts (AccountID, Balance)
VALUES (1, 1000);

INSERT dbo.DeadlockDemoOrders (OrderID, Status)
VALUES (1, 'Open');

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

-- Шаг 1, в сеансе 1.
BEGIN TRAN;
UPDATE dbo.DeadlockDemoAccounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Шаг 2, в сеансе 2.
BEGIN TRAN;
UPDATE dbo.DeadlockDemoOrders
SET Status = 'Cancelled'
WHERE OrderID = 1;

-- Шаг 3, обратно в сеансе 1. Этот пакет блокируется.
UPDATE dbo.DeadlockDemoOrders
SET Status = 'Paid'
WHERE OrderID = 1;
IF XACT_STATE() = 1 COMMIT;
IF XACT_STATE() = -1 ROLLBACK;

-- Шаг 4, обратно в сеансе 2. Это замыкает цикл.
UPDATE dbo.DeadlockDemoAccounts
SET Balance = Balance + 100
WHERE AccountID = 1;
IF XACT_STATE() = 1 COMMIT;
IF XACT_STATE() = -1 ROLLBACK;

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

После того как оба сеанса завершились, удалите два демонстрационных объекта.

DROP TABLE dbo.DeadlockDemoOrders;
DROP TABLE dbo.DeadlockDemoAccounts;

Каждое UPDATE защищает своё незафиксированное изменение от конфликтующих писателей. Без оптимизированной блокировки это обычно проявляется как X-блокировка ключа или строки, удерживаемая до COMMIT или ROLLBACK. С оптимизированной блокировкой блокировки строк и страниц могут освобождаться раньше, тогда как блокировка идентификатора транзакции защищает незафиксированное изменение. Логический результат всё тот же: другой писатель не может изменить ту же строку, пока транзакция не завершится, хотя поведение взаимоблокировки и граф могут отличаться.

Постройте взаимоблокировку

Выполните по одному шагу из каждого сеанса, затем запросите вторую строку из обоих. Три картинки охватывают всё это.

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

Состояние 1. Каждый сеанс защищает одну строку. Сеанс 1 обновил Accounts. Сеанс 2 обновил Orders. Оба держат блокировку, ни один не ждёт, и пока ничего неwrong.

Состояние 2. Каждый сеанс запрашивает строку, которую держит другой. Сеанс 1 запрашивает Orders. Сеанс 2 запрашивает Accounts. Оба теперь заблокированы, и каждый заблокирован другим.

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

Состояние 3. Монитор блокировок находит цикл и разрывает его. SQL Server выбирает жертву, откатывает её и возвращает ошибку 1205. Выживший получает обе строки и продолжает.

Посмотрите внимательно на жертву. Её первый UPDATE уже завершился успешно. Он всё равно отменяется, потому что откат отменяет всю транзакцию, а не только заблокированный оператор.

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

Измените одну вещь, и цикл не сможет сформироваться

Оба сеанса берут строки в одном порядке. Сначала Accounts, затем Orders. Сеанс 2 всё равно блокируется, но посмотрите на граф.

Одна стрелка. Циклу нужно, чтобы стрелка вернулась, а она никогда не возвращается. Сеанс 1 завершается, освобождает обе строки, и сеанс 2 продолжает.

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

Что только что произошло

Сеанс 1 защищает своё изменение Accounts и запрашивает Orders. Сеанс 2 защищает своё изменение Orders и запрашивает Accounts. Каждый сеанс ждёт ресурс, защищённый другим.

Монитор блокировок SQL Server ищет циклы. Обычный интервал обнаружения — пять секунд, но он может снижаться до 100 миллисекунд, когда взаимоблокировки происходят часто.

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

Почему понижение уровня изоляции не исправляет этот пример

READ UNCOMMITTED уменьшает разделяемую блокировку чтения, допуская грязные чтения. READ COMMITTED SNAPSHOT и SNAPSHOT используют версии строк для квалифицирующих чтений. Эти параметры могут уменьшить некоторые взаимоблокировки между читателем и писателем, но они не устраняют эту зависимость писатель-писатель.

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

Почему каждая транзакция выглядит правильной в одиночку

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

Используйте согласованный порядок доступа

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

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

Повторите всю транзакцию

SQL Server откатывает всю транзакцию жертвы, включая операторы, которые завершились до взаимоблокировки. Обрабатывайте ошибку 1205 с ограниченным числом повторов и задержкой. Повторяйте всю транзакцию и убедитесь, что бизнес-операция безопасна для повтора.

Прочитайте граф взаимоблокировки

В SQL Server и Azure SQL Managed Instance встроенный сеанс Extended Events system_health запускается автоматически и записывает обнаруженные взаимоблокировки. Его файлы событий перезаписываются, поэтому он сохраняет недавнюю историю, а не безграничный архив. Azure SQL Database не включает этот встроенный сеанс.

SELECT CAST(event_data AS xml) AS deadlock_graph
FROM   sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE  object_name = 'xml_deadlock_report';

Каждое возвращённое событие содержит XML взаимоблокировки, включая участвующие процессы, запрошенные и принадлежащие ресурсы, контекст выполнения и информацию о жертве. В SQL Server 2019 и ранее для чтения файлов требуется VIEW SERVER STATE. В SQL Server 2022 и новее может потребоваться VIEW SERVER PERFORMANCE STATE или VIEW DATABASE PERFORMANCE STATE.

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

Что демонстрация опускает

Строки показаны просто как занятые или свободные. Реальная блокировка имеет несколько режимов и типов ресурсов, а также блокировки намерения и возможное повышение уровня. С оптимизированной блокировкой также могут появляться блокировки идентификатора транзакции.
Обнаружение здесь мгновенное. SQL Server обычно начинает с пятисекундного интервала обнаружения, но может обнаруживать гораздо раньше, когда взаимоблокировки происходят часто.
Демонстрация оценивает стоимость отката по числу завершённых шагов. SQL Server сначала проверяет DEADLOCK_PRIORITY, затем оценочную стоимость отката и может выбрать случайно, когда обе равны.
Реальные взаимоблокировки не ограничиваются двумя сеансами или строками. Три или более сеансов могут образовать более длинное кольцо, а ресурсами могут быть страницы, ключи или выделения памяти.

Контрольный список для продакшена

1. Задокументируйте порядок ресурсов. Применяйте его в каждой транзакции, которая затрагивает одни и те же объекты.
2. Держите транзакции короткими. Не удерживайте блокировки базы данных, ожидая ввода пользователя, сетевых вызовов или несвязанной работы.
3. Перехватывайте 1205 и используйте ограниченный повтор с задержкой и джиттером. Повторяйте всю транзакцию, регистрируйте сбой и останавливайтесь после разумного предела.
4. Проверьте сбор и хранение. system_health активен в SQL Server и Azure SQL Managed Instance, но перезапись удаляет старые события. Используйте выделенный сеанс Extended Events, когда требуется более длительное хранение.
5. Не предполагайте, что более низкий уровень изоляции исправит цикл писатель-писатель. Версионирование строк может уменьшить блокировки и взаимоблокировки между читателем и писателем. Оно также меняет семантику чтения и не устраняет зависимость писателя, показанную здесь.
6. Если повторяющиеся взаимоблокировки требуют более широкого анализа, рассмотрите комплексную проверку производительности базы данных.
7. Взаимоблокировка — это дефект параллелизма на уровне рабочей нагрузки. Две транзакции могут выглядеть правильными изолированно и всё равно завершиться неудачей, когда они получают общие ресурсы в несогласованном порядке.