Как возникают deadlocks и как PostgreSQL с ними справляется?

Что такое дедлок и почему он возникает

Дедлок (взаимная блокировка) возникает, когда два или более транзакции ждут друг друга в цикле: транзакция A держит блокировку на ресурсе 1 и ждёт ресурс 2, а транзакция B держит блокировку на ресурсе 2 и ждёт ресурс 1. Ни одна из них не может продолжить выполнение без того, чтобы другая освободила свой ресурс.

Типичный сценарий — два UPDATE на одни и те же строки в разном порядке:

-- Транзакция A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ждёт, пока транзакция B отпустит id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Транзакция B (одновременно)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- ждёт, пока транзакция A отпустит id=1
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

Как PostgreSQL обнаруживает дедлоки

PostgreSQL использует алгоритм обнаружения циклов в графе ожидания (wait-for graph). Фоновый процесс проверяет граф с интервалом, заданным параметром deadlock_timeout (по умолчанию 1 с). Если цикл обнаружен, PostgreSQL выбирает одну транзакцию-«жертву» (обычно самую «дешёвую» для отката) и отменяет её с ошибкой:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 67890;
         blocked by process 11111.
HINT:   See server log for query details.

Транзакция-жертва получает rollback; другие могут продолжить работу. Событие также логируется в pg_log на уровне LOG.

Как найти и диагностировать дедлоки

Включите логирование дедлоков в postgresql.conf:

log_lock_waits = on
deadlock_timeout = 1s
log_min_duration_statement = 0

Просмотр активных ожиданий в реальном времени:

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Детальный граф блокировок
SELECT
  blocked.pid          AS blocked_pid,
  blocking.pid         AS blocking_pid,
  blocked.query        AS blocked_query,
  blocking.query       AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Стратегии предотвращения дедлоков

1. Единый порядок захвата ресурсов — всегда обрабатывайте строки в одном порядке (например, ORDER BY id):

-- Вместо произвольного порядка — сортируем id
UPDATE accounts
SET balance = balance + delta
WHERE id = ANY(ARRAY[1, 2, 3]::int[])
  AND id IN (SELECT id FROM unnest(ARRAY[1, 2, 3]::int[]) AS t(id) ORDER BY id)
RETURNING id, balance;

2. SELECT FOR UPDATE SKIP LOCKED — позволяет пропускать заблокированные строки вместо ожидания:

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

3. ADVISORY LOCKS — явный захват логических замков на уровне приложения:

SELECT pg_advisory_xact_lock(hashtext('transfer:' || least(1,2)::text));
SELECT pg_advisory_xact_lock(hashtext('transfer:' || greatest(1,2)::text));

4. Уменьшение времени транзакций — чем короче транзакция, тем меньше вероятность пересечения с другой.

Обработка на стороне приложения

Дедлоки неизбежны при высокой конкурентности, поэтому приложение должно уметь их retry. PostgreSQL возвращает SQLSTATE 40P01:

from psycopg2 import OperationalError
import time

def transfer_with_retry(conn, from_id, to_id, amount, retries=3):
    for attempt in range(retries):
        try:
            with conn.transaction():
                conn.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                conn.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
            return
        except OperationalError as e:
            if e.pgcode == '40P01' and attempt < retries - 1:
                time.sleep(0.05 * (attempt + 1))
                continue
            raise

Подводные камни

• Увеличение deadlock_timeout снижает нагрузку на обнаружение, но увеличивает латентность при реальных дедлоках — баланс нужно подбирать под нагрузку.
• ORM-фреймворки (SQLAlchemy, Django ORM) часто скрывают порядок UPDATE: при batch-операциях он может быть недетерминированным.
• Дедлоки и обычные lock-waits — разные вещи: lock_waits — это очередь, дедлок — цикл. log_lock_waits помогает найти и то и другое, но симптомы разные.
• Использование SELECT ... FOR UPDATE без ORDER BY в конкурентной среде почти гарантирует дедлоки при параллельных запросах на одном наборе строк.
• TRUNCATE и DDL-операции берут более сильные блокировки (AccessExclusiveLock), что может неожиданно создать дедлок с обычными DML-транзакциями.
• Retry без exponential backoff создаёт thundering herd — все конкурирующие транзакции снова сталкиваются одновременно.
• Advisory locks не освобождаются автоматически при сбое соединения в некоторых драйверах — нужно явно вызывать pg_advisory_unlock_all().

• Отвечать про deadlocks как про абстрактный SQL без учета MVCC, WAL, planner или конкретной версии PostgreSQL.
• Переносить поведение MySQL или ClickHouse на PostgreSQL без проверки документации.
• Не связывать механизм с последствиями для индексов, транзакций, блокировок или эксплуатации.
• Показывать синтаксис, который не поддерживается PostgreSQL.

• Кандидат объясняет deadlocks через реальный механизм PostgreSQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.