// справочник ошибок SQL Server

Ошибка SQL Server 1205: взаимная блокировка, транзакция выбрана жертвой

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

Номер ошибки
1205
Уровень серьёзности
13
Категория
Транзакции и блокировки
Сообщение об ошибке
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Что это значит

Взаимная блокировка — это цикл ожидания блокировок: сеанс A удерживает блокировку, которая нужна B, в то время как B удерживает блокировку, которая нужна A. Монитор взаимных блокировок SQL Server обнаруживает цикл и завершает его, выбирая жертву, откатывая всю транзакцию этого сеанса и вызывая ошибку 1205. Жертвой становится сеанс с наименьшим DEADLOCK_PRIORITY, а среди сеансов с равными приоритетами — тот, откат которого будет наиболее экономичным.

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

Распространённые причины

  • Два пути выполнения кода обновляют одни и те же таблицы или строки в противоположном порядке.
  • Чтение при уровне изоляции по умолчанию read committed, ожидающее процесс записи, который, в свою очередь, ожидает общую блокировку читателя (часто встречается при поиске через некластеризованные индексы).
  • Отсутствие индексов, из-за которых обновления и удаления сканируют и блокируют гораздо больше строк, чем изменяют.
  • Долгие транзакции или взаимодействие пользователя в середине открытой транзакции.
  • Более высокие уровни изоляции, такие как SERIALIZABLE удерживающие блокировки диапазонов.

Как исправить

  1. Повторите транзакцию в приложении, когда она завершится ошибкой 1205. В T-SQL цикл повторных попыток выглядит так:

    DECLARE @tries int = 0;
    WHILE @tries < 3
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            UPDATE dbo.Accounts SET Balance -= 100 WHERE AccountId = 1;
            UPDATE dbo.Accounts SET Balance += 100 WHERE AccountId = 2;
            COMMIT;
            BREAK;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            IF ERROR_NUMBER() <> 1205 OR @tries = 2 THROW;
            SET @tries += 1;
            WAITFOR DELAY '00:00:00.100';
        END CATCH
    END;
  2. Прочитайте граф взаимоблокировок. Встроенная system_health сеанс «Расширенных событий» записывает каждую взаимоблокировку.

    SELECT xed.value('@timestamp', 'datetime2') AS occurred_at, xed.query('.') AS deadlock_graph
    FROM (SELECT CAST(target_data AS xml) AS target_data
          FROM sys.dm_xe_session_targets AS t
          JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
          WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') AS data
    CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed);
  3. Обращайтесь к таблицам и строкам в одном и том же порядке в каждой транзакции и делайте транзакции короткими.

  4. Добавьте индексы, чтобы записи затрагивали только те строки, которые они изменяют; граф взаимоблокировок показывает, на каком индексе была каждая блокировка.

  5. Подумайте об изоляции READ COMMITTED SNAPSHOT: читающие транзакции будут использовать версии строк вместо общих блокировок и перестанут вставать во взаимоблокировку с пишущими.

    ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  6. Отмечайте менее важную работу как предпочтительную жертву.

    SET DEADLOCK_PRIORITY LOW;

Безопасное воспроизведение

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

-- Setup (once):
CREATE TABLE dbo.DL (id int PRIMARY KEY, v int);
INSERT INTO dbo.DL VALUES (1, 0), (2, 0);
-- Session 1:
BEGIN TRAN; UPDATE dbo.DL SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN TRAN; UPDATE dbo.DL SET v = 2 WHERE id = 2;
UPDATE dbo.DL SET v = 2 WHERE id = 1;  -- waits
-- Session 1:
UPDATE dbo.DL SET v = 1 WHERE id = 2;  -- one session gets Msg 1205

Вопросы

Пропадут ли мои данные после взаимоблокировки?

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

Исправляет ли NOLOCK взаимоблокировки?

Он может помешать читателям брать общие блокировки, но допускает «грязное» и неконсистентное чтение (пропущенные или продублированные строки). Изоляция read committed snapshot устраняет блокировки читателей и писателей без этих рисков.

Связанные ошибки

Больше ошибок транзакций и блокировок

Код и текст сообщения взяты из Справочник по событиям и ошибкам Microsoft Database Engine. Смотрите весь Список ошибок SQL Server или поиск по всем ошибкам баз данных.

// меньше ошибок, быстрее исправления

Работа с SQL Server в SQLly

Нативный SQL-клиент для macOS, Windows и Linux с IntelliSense, который знает вашу схему, предохранителями для рискованных запросов и понятными сообщениями об ошибках — для SQL Server и других баз данных, которые вы используете.