Как разбирать deadlock detected при параллельных UPDATE в PostgreSQL

#Что означает ошибка

Ошибка SQLSTATE 40P01 означает взаимную блокировку: две или более транзакции ждут ресурсы друг друга, поэтому PostgreSQL прерывает одну из них. Частый сценарий — запросы обновляют одни и те же таблицы или строки в разном порядке. Перезапуск сервера и отключение блокировок не решают причину; нужен порядок обращения к ресурсам и обработка отменённой транзакции.

#Как различить причины

#Пошаговая диагностика и устранение

1. Запишите текст 40P01 и обстоятельстваОткройте журнал PostgreSQL или ошибки драйвера приложения и найдите deadlock detected, код 40P01, детали ожиданий и названия операций. Запишите, какие кнопки или фоновые задания выполнялись одновременно. Если процесс уже получил ошибку внутри psql, сначала выполните ROLLBACK;: после неё транзакция прервана и её нельзя безопасно продолжать отдельным запросом.
2. Найдите две конкурирующие операции в кодеВ редакторе откройте обработчики действий, указанных в журнале. Выпишите последовательность SQL-запросов каждой транзакции от BEGIN до COMMIT; учитывайте не только названия таблиц, но и строки и внешние ключи. Сравните, в каком порядке выполняются UPDATE/SELECT FOR UPDATE. Не запускайте этот опыт на живой базе под нагрузкой.
3. Проверьте доступность диагностических сведенийВ psql или pgAdmin → Query Tool выполните SELECT current_database(), version(); SHOW deadlock_timeout;. Обычных прав достаточно. Уточните у администратора, сохраняется ли журнал взаимных блокировок и включена ли нужная детализация. Не повышайте объём журналирования и не меняйте конфигурацию сервера самостоятельно.
4. Воспроизведите ситуацию в отдельной тестовой базеПодготовьте собственную пустую тестовую базу, создайте таблицу и две строки из примера ниже. Затем откройте два окна psql, А и Б, к этой же тестовой базе. Выполняйте команды строго в указанном в описании порядке; один из запросов будет ждать. Не делайте этот опыт с таблицами пользователей: он специально создаёт взаимную блокировку.
5. Сравните порядок блокировок в двух окнахВ окне А: BEGIN; UPDATE whelp_lock_demo SET n=n+1 WHERE id=1;. В окне Б: BEGIN; UPDATE whelp_lock_demo SET n=n+1 WHERE id=2;. В окне А начните UPDATE id=2 (зависнет в ожидании); затем в окне Б начните UPDATE id=1. PostgreSQL обнаружит 40P01 и откатит одну транзакцию. В окне с ошибкой выполните ROLLBACK, в другом завершите тест, затем проверьте состояние строк.
6. Установите единый порядок обновленийВ обоих обработчиках измените последовательность так, чтобы одинаковые строки всегда блокировались по возрастанию ID, а таблицы — в едином согласованном порядке. Если набор строк определяется на ходу, сначала получите отсортированные идентификаторы, затем выполняйте блокирующие действия по одному и тому же правилу. Перед правкой сохраните исходный код и создайте отдельный тест.
7. Добавьте осторожный повтор всей транзакцииВ обработчике ошибок различайте SQLSTATE 40P01 и другие коды. При 40P01 завершите неудавшуюся транзакцию ROLLBACK, затем запустите всю логическую операцию заново с новыми данными, ограничив число попыток и добавив небольшую случайную задержку. Не повторяйте только последнюю строку UPDATE: ранние чтения и расчёты могли устареть. Внешние отправки и платежи делайте идемпотентными.
8. Проверьте исправление и верните тестовую средуПрогоните два конкурирующих действия в тестовом приложении и проверьте итоговые значения строк, отсутствие 40P01 и журнал ошибок. Если блокировки остались, изучите внешний ключ, триггеры и другие запросы в той же транзакции. Удалите временную таблицу только из собственной тестовой базы после проверки владельца, либо удалите целиком специально выделенную тестовую базу через ответственного администратора.
9. Команды для отдельных ситуацийВыберите только подходящий вариант. Это не одна последовательность: часть команд диагностирует проблему, а часть меняет настройки или данные.
   Выберите подходящий вариант — это не команды для последовательного выполнения

   Вариант 1psql или Query Tool, только чтение.

   SQLКомандаКопировать всёВыделить

   psql или Query Tool, только чтение. Доступ к чужим деталям может быть ограничен; отсутствие WAIT не доказывает отсутствия дедлока.

   SHOW deadlock_timeout;

   SELECT pid, state, wait_event_type, wait_event FROM pg_stat_activity WHERE datname=current_database();

   Вариант 2ТОЛЬКО в новой пустой тестовой базе после проверки имени подключения; создаёт реальную таблицу, требует пра…

   SQLС осторожностьюКопировать всёВыделить

   ТОЛЬКО в новой пустой тестовой базе после проверки имени подключения; создаёт реальную таблицу, требует права CREATE. Не выполняйте на рабочей базе.

   CREATE TABLE whelp_lock_demo(id integer PRIMARY KEY, n integer NOT NULL);

   INSERT INTO whelp_lock_demo VALUES (1,0),(2,0);

   Команда может изменить состояние системы или её настройки. Проверьте параметры и последствия перед запуском.

#Воспроизведение и проверка только в отдельной тестовой базе

-- Окно А, тестовая база
BEGIN;
UPDATE whelp_lock_demo SET n=n+1 WHERE id=1;
-- Затем в окне Б выполните BEGIN и UPDATE id=2.
UPDATE whelp_lock_demo SET n=n+1 WHERE id=2;
-- Эта команда будет ждать Б. Дальше в Б запустите UPDATE id=1.
-- В окне, получившем 40P01, выполните ROLLBACK;
-- в другом окне завершите тест COMMIT или ROLLBACK.

SELECT * FROM whelp_lock_demo ORDER BY id;

#Как убедиться в исправлении и что делать дальше

Ожидаемый результат: два одновременных действия завершаются корректно, ни одно изменение не дублируется, а для временного 40P01 предусмотрен ограниченный повтор транзакции целиком. Если ошибка остаётся, передайте два полных SQL-сценария, время возникновения, журнал DETAIL и схему таблиц специалисту по БД.