Взаимные блокировки (deadlock) в БД: причины и решения

Коротко

Взаимная блокировка (deadlock) — это цикл: транзакция A держит блокировку и ждёт B, а B держит блокировку и ждёт A. Ни одна не может продолжить. PostgreSQL и MySQL обнаруживают цикл, откатывают одну транзакцию с ошибкой и дают другой завершиться.

Deadlock — не порча данных. Это сигнал, что код захватывает блокировки в несогласованном порядке. Лечение — порядок, короткие транзакции и повтор для «жертвы».

Как блокировки образуют цикл

Классика — два перевода между одними счетами в противоположных направлениях:

-- Сессия A                          -- Сессия B
BEGIN;                                BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
                                      UPDATE accounts SET balance = balance - 10 WHERE id = 2;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;  -- ждёт B
                                      UPDATE accounts SET balance = balance + 10 WHERE id = 1;  -- ждёт A: deadlock

Другие частые источники:

• Пакетные обновления в случайном порядке: две задачи обновляют пересекающиеся наборы строк без ORDER BY.
• Повышение блокировки: обе транзакции берут разделяемую блокировку (FOR SHARE или неявно при проверке внешнего ключа), а затем пытаются обновить ту же строку.
• Внешние ключи: вставка дочерних строк блокирует родительскую в разделяемом режиме, что конфликтует с обновлением этого родителя.
• Gap- и next-key-блокировки в MySQL: на Repeatable Read InnoDB блокирует диапазоны между записями индекса, поэтому параллельные вставки в один диапазон могут дать deadlock даже с разными ключами.
• Нет индекса в MySQL: InnoDB блокирует просканированные строки, и UPDATE без подходящего индекса блокирует гораздо больше строк, чем меняет.
• Смешение блокировок таблиц и строк: явный LOCK TABLE или DDL в одной транзакции, пока другая держит блокировки строк.

Как читать лог deadlock в PostgreSQL

PostgreSQL ждёт deadlock_timeout, потом проверяет наличие цикла и выдаёт SQLSTATE 40P01:

ERROR:  deadlock detected
DETAIL:  Process 4121 waits for ShareLock on transaction 9812; blocked by process 4135.
         Process 4135 waits for ShareLock on transaction 9811; blocked by process 4121.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,7) in relation "accounts"

Как читать: два процесса, каждый ждёт завершения транзакции другого. В логе сервера есть точные запросы обоих процессов. Включите log_lock_waits = on, чтобы видеть и долгие ожидания, которые не стали deadlock.

Как читать лог deadlock в MySQL

InnoDB обнаруживает deadlock сразу и возвращает ошибку 1213:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Выполните SHOW ENGINE INNODB STATUS и найдите раздел LATEST DETECTED DEADLOCK. Для каждой транзакции там видны последний запрос, HOLDS THE LOCK(S) и WAITING FOR THIS LOCK TO BE GRANTED с индексом и типом блокировки (lock_mode X, locks gap before rec, insert intention). Последняя строка сообщает, какая транзакция откачена. Там хранится только последний случай; задайте innodb_print_all_deadlocks = ON, чтобы писать все deadlock в лог ошибок.

Как устранить deadlock

1. Блокируйте в одном порядке. Если каждая ветка кода блокирует строки по возрастанию ID, цикл не образуется:

BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

То же для таблиц: если транзакция трогает orders и payments, всегда в одном и том же порядке.

2. Сразу берите самую сильную блокировку. Используйте FOR UPDATE с самого начала, а не читайте с разделяемой блокировкой, чтобы потом её повышать.

3. Держите транзакции короткими. Никаких сетевых вызовов, ожидания пользователя или тяжёлых вычислений, пока удерживаются блокировки.

4. Делите большие пакеты на небольшие порции, отсортированные по первичному ключу.

5. Добавьте нужные индексы, особенно в MySQL, чтобы запросы блокировали только нужные строки.

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

for attempt in range(3):
    try:
        with conn.transaction():
            transfer(conn, from_id, to_id, amount)
        break
    except DeadlockDetected:
        if attempt == 2:
            raise
        time.sleep(random.uniform(0.05, 0.2) * (attempt + 1))

Убедитесь, что транзакцию безопасно повторять: внутри не отправляются письма и не вызываются внешние API.

Deadlock или просто долгое ожидание?

Обычное ожидание блокировки — не deadlock: одна транзакция просто ждёт завершения другой. Это проявляется как медленные запросы, а не ошибки. Ограничивайте такие ожидания через lock_timeout в PostgreSQL или innodb_lock_wait_timeout в MySQL, а смотреть, кто кого блокирует, можно через pg_locks вместе с pg_stat_activity.