Как предотвратить дедлоки при конкурентных изменениях в базе данных

Что проверяют

Понимаете ли вы, откуда берётся взаимная блокировка и что с ней делает сама СУБД. Следом обычно спрашивают, как вы обработаете дедлок в коде и помогает ли смена уровня изоляции.

Как отвечать

Механика: транзакция A держит строку 1 и ждёт строку 2, транзакция B держит строку 2 и ждёт строку 1. PostgreSQL после ожидания длиной deadlock_timeout (по умолчанию 1 секунда) находит цикл и откатывает одну транзакцию с ошибкой 40P01. В psycopg это DeadlockDetected, в SQLAlchemy она приходит обёрнутой в OperationalError.

Как снизить вероятность:

• единый порядок блокировок: сортировать id и брать строки через SELECT ... ORDER BY id FOR UPDATE. В Django это select_for_update().order_by("pk") внутри transaction.atomic();
• короткие транзакции: никаких HTTP-вызовов и тяжёлых вычислений, пока транзакция держит блокировки;
• атомарные обновления: SET balance = balance + %s с отрицательной суммой для списания вместо чтения значения в Python и записи обратно, в Django то же даёт F();
• очереди задач: FOR UPDATE SKIP LOCKED, чтобы воркеры не ждали друг друга.

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

Про изоляцию ответьте точно. В PostgreSQL строки под UPDATE блокируются одинаково на любом уровне, а SERIALIZABLE добавляет ошибки сериализации 40001, которые тоже требуют повтора. В MySQL переход с REPEATABLE READ на READ COMMITTED действительно убирает часть дедлоков, потому что пропадают gap-блокировки.

Где спотыкаются

Считают, что таймаут предотвращает дедлок. База и так найдёт его сама, lock_timeout лишь ограничивает обычное ожидание.

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

Обновляют пачку записей в том порядке, в каком они пришли. Два воркера с пересекающимися наборами в разном порядке рано или поздно сцепятся.