Как исправить serialization_failure 40001 при параллельном UPDATE

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

SQLSTATE 40001 could not serialize access due to concurrent update появляется, когда транзакция с уровнем REPEATABLE READ или SERIALIZABLE столкнулась с изменением данных другой транзакцией и не может безопасно продолжиться в прежнем снимке. PostgreSQL отменяет транзакцию намеренно; правильная реакция приложения — ROLLBACK и повтор всей бизнес-операции с повторным чтением данных, а не повтор одной строки SQL.

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

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

1. Уточните код и не продолжайте прерванную транзакциюВ журнале приложения скопируйте could not serialize access due to concurrent update, SQLSTATE 40001 и идентификатор операции. В psql после ошибки выполните ROLLBACK; — до этого следующие команды дадут current transaction is aborted. Если журнал показывает 40P01 deadlock detected, это иной код, хотя ограниченный повтор транзакции тоже может понадобиться.
2. Проверьте используемый уровень изоляцииОткройте psql либо pgAdmin → нужная база → Tools → Query Tool. В отдельном соединении выполните SHOW default_transaction_isolation; и внутри проверяемой транзакции SHOW transaction_isolation;. Если приложение явно указывает уровень при BEGIN, значение может отличаться от значения по умолчанию — ищите его в конфигурации драйвера. Не меняйте уровень изоляции глобально.
3. Найдите конфликтующую бизнес-операциюВ исходном коде найдите весь участок между BEGIN и COMMIT, где меняется одна и та же запись. Запишите, какие значения были прочитаны, какие проверки сделаны и что пересчитывается перед UPDATE. Если несколько работников одновременно списывают остаток, повторять старый расчёт нельзя: во второй попытке надо ещё раз получить остаток и заново проверить условия.
4. Воспроизведите ошибку на собственной тестовой таблицеВ отдельной пустой тестовой базе создайте таблицу из примера и откройте два окна psql, подключённые к ней. В каждом выполните BEGIN ISOLATION LEVEL REPEATABLE READ и SELECT строки id=1, чтобы зафиксировать снимок. В А измените строку и COMMIT. В Б попытайтесь UPDATE той же строки — ожидается 40001; завершите Б командой ROLLBACK.
5. Оберните всю операцию в ограниченное повторениеВ драйвере приложения перехватывайте ошибку по SQLSTATE 40001, откатывайте соединение, ждите небольшую случайную задержку и начинайте новый BEGIN. Повтор должен включать все чтения, расчёты и выбор SQL; ограничьте число повторов, например тремя попытками, затем сообщите вызывающему коду о перегрузке. Не используйте бесконечный цикл.
6. Защитите от повторных внешних действийПроверьте, не отправляет ли код письмо, платёж или запрос стороннему API внутри повторяемой транзакции. У такого действия может быть эффект, даже если SQL откатился. Перенесите отправку на этап после успешного COMMIT либо примените механизм идемпотентности (уникальный ключ операции) и транзакционную очередь отправки, если система это поддерживает.
7. Сократите время конкурентной транзакцииУберите из BEGIN медленные сетевые запросы и пользовательское ожидание; используйте короткие SQL-операции и индекс для поиска записи. Если бизнес-правила позволяют, пересмотрите необходимость REPEATABLE READ/SERIALIZABLE только после тестов на потерянные обновления и другие аномалии. Не объявляйте переход к READ COMMITTED универсальным лечением.
8. Проверьте повтор и завершение операцийВ тестовой среде запустите две параллельные операции и проверьте итоговые строки SELECT-ом: значения должны соответствовать ровно одному подтверждённому эффекту каждой успешной бизнес-операции. В журнале допускается зарегистрированный 40001 с успешным ограниченным повтором; если конфликт повторяется много раз, снизьте конкуренцию и исследуйте долгие транзакции.
9. Команды для отдельных ситуацийВыберите только подходящий вариант. Это не одна последовательность: часть команд диагностирует проблему, а часть меняет настройки или данные.
   Выберите подходящий вариант — это не команды для последовательного выполнения

   Вариант 1psql или pgAdmin в правильной роли.

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

   psql или pgAdmin в правильной роли. Только чтение параметров; внутри BEGIN проверьте реальный уровень конкретной транзакции.

   SHOW default_transaction_isolation;

   SHOW transaction_isolation;

   Вариант 2ТОЛЬКО отдельная чистая тестовая база.

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

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

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

   INSERT INTO whelp_serial_demo VALUES(1,0);

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

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

-- Окно A, отдельная тестовая база
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT n FROM whelp_serial_demo WHERE id=1;
UPDATE whelp_serial_demo SET n=n+1 WHERE id=1;
COMMIT;

-- Окно Б: выполнить BEGIN и SELECT ДО COMMIT в окне А
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT n FROM whelp_serial_demo WHERE id=1;
-- После COMMIT в окне A:
UPDATE whelp_serial_demo SET n=n+1 WHERE id=1;
-- Ожидается 40001; завершите ROLLBACK;

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

Успех — две конкурентные операции проходят за ограниченное число попыток и итоговые значения верны, а повторно выполняемые запросы не приводят к двойным списаниям. Если ошибка остаётся после нескольких попыток, журналируйте число повторов, длительности транзакций, SQLSTATE и идентификатор операции; затем оптимизируйте конкурентный доступ вместе с разработчиком.