Deadlock в PostgreSQL: причины, диагностика и решение

Deadlock (взаимная блокировка) — ситуация, когда две или более транзакции ждут друг друга, и ни одна не может продолжиться. PostgreSQL автоматически обнаруживает и разрывает deadlock, но это всё равно ошибка в приложении.

Как возникает deadlock

Классический пример:

Транзакция A:                    Транзакция B:
UPDATE accounts SET ... WHERE id=1   UPDATE accounts SET ... WHERE id=2
(ждёт id=2...)                   (ждёт id=1...)

-- Транзакция 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;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- Ждёт...
-- DEADLOCK DETECTED!

PostgreSQL прерывает одну из транзакций (обычно более «молодую») с ошибкой:

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

Как обнаружить deadlock

В логах PostgreSQL

grep "deadlock detected" /var/log/postgresql/postgresql.log

Настройте детальное логирование:

# postgresql.conf
log_min_duration_statement = 1000  # запросы дольше 1с
deadlock_timeout = 1s               # время ожидания до проверки deadlock
log_lock_waits = on                 # логировать долгие ожидания блокировок

Текущие блокировки через pg_locks

-- Кто кого блокирует прямо сейчас
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query,
    now() - blocked_activity.query_start AS waiting_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Долгоживущие транзакции

-- Транзакции старше 5 минут
SELECT pid, usename, state, query_start, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '5 minutes'
ORDER BY query_start;

Стратегия 1: Единый порядок блокировок

Самое эффективное решение — всегда блокировать ресурсы в одинаковом порядке:

-- Правильно: всегда блокируем по возрастанию id
BEGIN;
-- Берём id в отсортированном порядке
UPDATE accounts SET balance = balance - 100 WHERE id = LEAST(1, 2);
UPDATE accounts SET balance = balance + 100 WHERE id = GREATEST(1, 2);
COMMIT;

Или в коде приложения:

# Всегда сортируем id перед UPDATE
from_id, to_id = sorted([account_from, account_to])
cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", [amount, from_id])
cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", [amount, to_id])

Стратегия 2: SELECT FOR UPDATE с порядком

-- Явно блокируем строки в транзакции
BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Теперь безопасно обновляем
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ORDER BY id FOR UPDATE гарантирует порядок блокировок.

Стратегия 3: SELECT FOR UPDATE NOWAIT / SKIP LOCKED

-- NOWAIT: сразу падать с ошибкой, не ждать блокировку
SELECT * FROM tasks WHERE status = 'pending' LIMIT 1 FOR UPDATE NOWAIT;
-- Ошибка если строка уже заблокирована — можно повторить попытку

-- SKIP LOCKED: пропускать заблокированные строки (для очередей задач)
SELECT * FROM tasks WHERE status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED;
-- Никогда не блокируется — всегда берёт свободную строку

SKIP LOCKED — идеальный паттерн для конкурентной обработки задач.

Стратегия 4: Уменьшить время транзакции

Чем дольше транзакция держит блокировки, тем выше риск deadlock:

-- Плохо: длинная транзакция
BEGIN;
-- Много медленных операций...
SELECT * FROM heavy_table WHERE ...; -- 5 секунд
UPDATE users SET ...;                -- держит блокировку
COMMIT;

-- Хорошо: минимальная транзакция
-- Делаем всё вычисление ВНЕ транзакции
SELECT id, new_value FROM heavy_table WHERE ...;  -- без транзакции

BEGIN;
UPDATE users SET value = :new_value WHERE id = :id;  -- быстро
COMMIT;

Стратегия 5: Advisory Locks — свои блокировки

PostgreSQL позволяет создавать собственные блокировки по числовому ключу:

-- Захватить блокировку для обработки пользователя 42
SELECT pg_advisory_lock(42);
-- ... делаем работу ...
SELECT pg_advisory_unlock(42);

-- Попробовать без блокировки (не ждать)
SELECT pg_try_advisory_lock(42);
-- Возвращает true если захвачено, false — если занято

Это позволяет координировать доступ без блокировок на уровне строк.

Автоматический retry в приложении

После deadlock транзакция должна быть повторена:

import psycopg2
from psycopg2 import errors
import time

def transfer_funds(conn, from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                cur.execute("BEGIN")
                # Всегда в отсортированном порядке
                for aid in sorted([from_id, to_id]):
                    cur.execute("SELECT id FROM accounts WHERE id = %s FOR UPDATE", [aid])

                cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", [amount, from_id])
                cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", [amount, to_id])
                cur.execute("COMMIT")
                return True
        except errors.DeadlockDetected:
            cur.execute("ROLLBACK")
            if attempt == max_retries - 1:
                raise
            time.sleep(0.1 * (attempt + 1))  # exponential backoff

Убить зависший процесс

-- Завершить запрос мягко (ждёт пока можно)
SELECT pg_cancel_backend(pid);

-- Завершить процесс жёстко
SELECT pg_terminate_backend(pid);

-- Убить все ожидающие запросы старше 10 минут
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '10 minutes'
  AND pid <> pg_backend_pid();

Итог: профилактика deadlock

1. Единый порядок блокировок — самое важное правило
2. Короткие транзакции — минимальный набор операций
3. SELECT FOR UPDATE с ORDER BY — явный порядок
4. NOWAIT / SKIP LOCKED — для очередей задач
5. Advisory Locks — координация на уровне приложения
6. Retry с backoff — обрабатывайте deadlock в коде
7. Мониторинг — log_lock_waits = on, алерт на deadlock в логах