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

PostgreSQL 40P01: обнаружена взаимоблокировка

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

SQLSTATE
40P01
Имя
deadlock_detected
Категория
Откат транзакции
Сообщение об ошибке
deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.
Process 4321 waits for ShareLock on transaction 8765; blocked by process 1234.

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

Взаимная блокировка — это цикл ожидания блокировок: транзакция A удерживает блокировку, которая нужна B, и при этом ожидает блокировку, удерживаемую B. Ни одна из них не может продолжить, поэтому, когда ожидание блокировки длится deadlock_timeout (по умолчанию 1 секунда) PostgreSQL проверяет, нет ли цикла, и, найдя его, прерывает одну из транзакций с кодом 40P01.

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

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

  • Две транзакции обновляют одни и те же строки в разном порядке, например, одна переводит деньги со счёта 1 на счёт 2, а другая — со счёта 2 на счёт 1.
  • Внешние ключи: вставка дочерних строк берёт FOR KEY SHARE блокировку родительской строки, что может конфликтовать с другой транзакцией, обновляющей ту же родительскую строку.
  • Долгие транзакции, затрагивающие многие строки, расширяют окно для перекрывающегося порядка блокировок.
  • Явные LOCK TABLE или SELECT ... FOR UPDATE захватываемые в разном порядке в разных ветках кода.

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

  1. Повторите неудавшуюся транзакцию. Взаимная блокировка — нормальный итог конкурентного выполнения, и приложение должно перехватывать SQLSTATE 40P01 (и 40001) и запустить всю транзакцию заново.

  2. Захватывайте блокировки в согласованном порядке везде, например, всегда сначала блокируйте меньший id.

    BEGIN;
    SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
  3. Держите транзакции короткими: сначала чтение и вычисления, затем запись — и сразу фиксируйте.

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

    ALTER SYSTEM SET log_lock_waits = on;
    SELECT pg_reload_conf();

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

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

-- Session 1:
CREATE TABLE dl (id int PRIMARY KEY, v int);
INSERT INTO dl VALUES (1, 0), (2, 0);
BEGIN; UPDATE dl SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN; UPDATE dl SET v = 2 WHERE id = 2;
UPDATE dl SET v = 2 WHERE id = 1;   -- waits
-- Session 1:
UPDATE dl SET v = 1 WHERE id = 2;   -- one session gets ERROR: deadlock detected

Вопросы

Какую транзакцию PostgreSQL прерывает при взаимоблокировке?

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

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

Код и текст сообщения взяты из Документация PostgreSQL, приложение A: коды ошибок PostgreSQL. Смотрите весь Список ошибок PostgreSQL или поиск по всем ошибкам баз данных.

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

Работа с PostgreSQL в SQLly

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