Три часа ночи. Телефон орёт. В слаке паника: «Прод лежит, ничего не работает, база не отвечает». Ты продираешь глаза, лезешь в мониторинг и видишь — приложение встало колом. Не OOM, не диск кончился, а просто тихо умерло на ровном месте. Знакомо?

За последние пять лет я собрал неплохую коллекцию ночных вызовов из-за проблем с блокировками в PostgreSQL. И почти всегда причина одна — кто-то написал «обычный UPDATE», который оказался совсем не обычным.

Сначала разберёмся, что происходит

Deadlock — это когда две транзакции держат друг друга за горло и ждут. Первая захватила строку A и хочет B. Вторая захватила B и хочет A. Обе ждут. Вечно.

PostgreSQL умеет это детектить и убивает одну из транзакций через deadlock_timeout (по умолчанию секунда). Но пока он думает, ваши пользователи видят спиннер. А если таких ситуаций много — прод захлёбывается.

Как два UPDATE начинают воевать

Вот классика жанра. Есть таблица счетов:

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    balance DECIMAL(10, 2) NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);

И два процесса переводят деньги между аккаунтами:

Процесс 1:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- какая-то логика, пауза
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Процесс 2:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- какая-то логика, пауза
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

Если они стартуют одновременно и первый UPDATE каждого успевает пройти — привет, deadlock. Процесс 1 держит строку 1, ждёт строку 2. Процесс 2 держит строку 2, ждёт строку 1. PostgreSQL через секунду прибьёт кого-то с ошибкой:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.
Process 67891 waits for ShareLock on transaction 12345; blocked by process 12345.

Почему это стреляет на проде, а не на тестах

На локалке ты один. Запросы идут последовательно. На проде — сотни параллельных соединений, и шанс пересечения растёт нелинейно. Плюс на проде данные большие, индексы пухлые, запросы медленнее. Транзакции живут дольше, окно для deadlock шире.

Уровни изоляции: почему это важно понимать

Многие знают про READ COMMITTED и REPEATABLE READ, но не все понимают, как они влияют на блокировки.

READ COMMITTED (по умолчанию в PostgreSQL)

Каждый запрос внутри транзакции видит только данные, закоммиченные к моменту его старта. Звучит безопасно, но есть нюанс.

-- Транзакция A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- видим 1000
-- пауза

-- Транзакция B (параллельно)
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Транзакция A (продолжение)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- balance станет 400, не 900!
COMMIT;

Твой SELECT показал 1000, ты думаешь, что списываешь из тысячи. А по факту там уже 500. Сюрприз.

REPEATABLE READ

Тут транзакция видит снимок данных на момент первого запроса. Все последующие SELECT вернут те же данные. Но есть цена — если кто-то поменял строку, которую ты хочешь UPDATE, получишь ошибку:

ERROR: could not serialize access due to concurrent update

И приложение должно это обработать — повторить транзакцию. Многие не готовы.

Что выбрать?

Нет универсального ответа. Для типичного CRUD хватает READ COMMITTED с правильными блокировками. Для финансовых операций, где важна консистентность — REPEATABLE READ или даже SERIALIZABLE, но с готовностью ретраить.

Ищем виновника: pg_stat_activity и pg_locks

Когда прод горит, не до теории. Нужно быстро понять — кто кого блокирует.

Смотрим активные соединения

SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event,
    usename,
    application_name
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '30 seconds'
  AND state != 'idle'
ORDER BY duration DESC;

Это покажет все запросы, которые висят дольше 30 секунд. Если видишь wait_event_type = 'Lock' — вот он, кандидат.

Кто кого блокирует

Этот запрос я держу в закладках и дёргаю первым делом:

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query,
    blocked_locks.locktype,
    now() - blocked_activity.query_start AS blocked_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.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    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
ORDER BY blocked_duration DESC;

Получаешь таблицу: кто заблокирован, кем, какой запрос висит, сколько времени. Сразу видно, кого прибить.

Прибиваем процесс

Если нашёл виновника и уверен, что можно убить:

SELECT pg_terminate_backend(12345);  -- 12345 - pid процесса

Или мягко:

SELECT pg_cancel_backend(12345);  -- отменит запрос, но соединение оставит

Мониторинг в реальном времени

Ждать, пока прод упадёт — плохая стратегия. Лучше узнавать о проблемах до пользователей.

Python-скрипт с алертами в Telegram

Написал простой мониторинг, который проверяет долгие транзакции и шлёт алерты. Работает уже год, пару раз реально спасал.

#!/usr/bin/env python3
"""
Мониторинг долгих транзакций PostgreSQL с алертами в Telegram.
Запускать по крону каждую минуту.
"""

import psycopg2
import requests
from datetime import datetime
import os

# Конфигурация
DB_CONFIG = {
    'host': os.getenv('PG_HOST', 'localhost'),
    'port': os.getenv('PG_PORT', 5432),
    'database': os.getenv('PG_DATABASE', 'production'),
    'user': os.getenv('PG_USER', 'monitor'),
    'password': os.getenv('PG_PASSWORD', ''),
}

TELEGRAM_TOKEN = os.getenv('TELEGRAM_TOKEN', '')
TELEGRAM_CHAT_ID = os.getenv('TELEGRAM_CHAT_ID', '')

# Пороги
LONG_QUERY_THRESHOLD_SEC = 60  # запрос висит дольше минуты
LONG_TRANSACTION_THRESHOLD_SEC = 300  # транзакция открыта дольше 5 минут
BLOCKED_QUERY_THRESHOLD_SEC = 30  # запрос заблокирован дольше 30 сек


def send_telegram(message: str):
    """Отправка сообщения в Telegram."""
    if not TELEGRAM_TOKEN or not TELEGRAM_CHAT_ID:
        print(f"[{datetime.now()}] ALERT: {message}")
        return
    
    url = f"https://api.telegram.org/bot{TELEGRAM_TOKEN}/sendMessage"
    payload = {
        'chat_id': TELEGRAM_CHAT_ID,
        'text': message,
        'parse_mode': 'HTML'
    }
    try:
        resp = requests.post(url, json=payload, timeout=10)
        if resp.status_code != 200:
            print(f"Telegram error: {resp.text}")
    except Exception as e:
        print(f"Telegram send failed: {e}")


def check_long_queries(cursor) -> list:
    """Ищем запросы, которые выполняются слишком долго."""
    cursor.execute("""
        SELECT 
            pid,
            usename,
            application_name,
            client_addr,
            EXTRACT(EPOCH FROM (now() - query_start))::int AS duration_sec,
            LEFT(query, 200) AS query_preview,
            wait_event_type,
            wait_event
        FROM pg_stat_activity
        WHERE state = 'active'
          AND query NOT LIKE '%pg_stat_activity%'
          AND EXTRACT(EPOCH FROM (now() - query_start)) > %s
        ORDER BY duration_sec DESC
        LIMIT 10
    """, (LONG_QUERY_THRESHOLD_SEC,))
    return cursor.fetchall()


def check_long_transactions(cursor) -> list:
    """Ищем транзакции, которые открыты слишком долго (включая idle in transaction)."""
    cursor.execute("""
        SELECT 
            pid,
            usename,
            application_name,
            client_addr,
            state,
            EXTRACT(EPOCH FROM (now() - xact_start))::int AS xact_duration_sec,
            LEFT(query, 200) AS last_query
        FROM pg_stat_activity
        WHERE xact_start IS NOT NULL
          AND state IN ('idle in transaction', 'idle in transaction (aborted)')
          AND EXTRACT(EPOCH FROM (now() - xact_start)) > %s
        ORDER BY xact_duration_sec DESC
        LIMIT 10
    """, (LONG_TRANSACTION_THRESHOLD_SEC,))
    return cursor.fetchall()


def check_blocked_queries(cursor) -> list:
    """Ищем заблокированные запросы."""
    cursor.execute("""
        SELECT 
            blocked.pid AS blocked_pid,
            blocked.usename AS blocked_user,
            blocking.pid AS blocking_pid,
            blocking.usename AS blocking_user,
            EXTRACT(EPOCH FROM (now() - blocked.query_start))::int AS blocked_duration_sec,
            LEFT(blocked.query, 150) AS blocked_query,
            LEFT(blocking.query, 150) AS blocking_query
        FROM pg_stat_activity blocked
        JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
        JOIN pg_locks blocking_locks 
            ON blocking_locks.locktype = blocked_locks.locktype
            AND blocking_locks.relation = blocked_locks.relation
            AND blocking_locks.pid != blocked_locks.pid
            AND blocking_locks.granted
        JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
        WHERE NOT blocked_locks.granted
          AND EXTRACT(EPOCH FROM (now() - blocked.query_start)) > %s
        ORDER BY blocked_duration_sec DESC
        LIMIT 5
    """, (BLOCKED_QUERY_THRESHOLD_SEC,))
    return cursor.fetchall()


def format_alert(alert_type: str, data: list) -> str:
    """Форматируем алерт для Telegram."""
    lines = [f"? <b>{alert_type}</b>\n"]
    
    for row in data[:3]:  # максимум 3 записи, чтобы не спамить
        if alert_type == "Долгие запросы":
            pid, user, app, addr, duration, query, wait_type, wait_event = row
            lines.append(
                f"PID: {pid} | {user}@{app}\n"
                f"Duration: {duration}s\n"
                f"Wait: {wait_type}/{wait_event}\n"
                f"<code>{query[:100]}...</code>\n"
            )
        elif alert_type == "Зависшие транзакции":
            pid, user, app, addr, state, duration, query = row
            lines.append(
                f"PID: {pid} | {user}@{app}\n"
                f"State: {state}\n"
                f"Duration: {duration}s\n"
                f"<code>{query[:100]}...</code>\n"
            )
        elif alert_type == "Блокировки":
            blocked_pid, blocked_user, blocking_pid, blocking_user, duration, blocked_q, blocking_q = row
            lines.append(
                f"Blocked: PID {blocked_pid} ({blocked_user})\n"
                f"By: PID {blocking_pid} ({blocking_user})\n"
                f"Duration: {duration}s\n"
            )
    
    return "\n".join(lines)


def main():
    alerts = []
    
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        conn.autocommit = True
        cursor = conn.cursor()
        
        # Проверяем всё
        long_queries = check_long_queries(cursor)
        if long_queries:
            alerts.append(format_alert("Долгие запросы", long_queries))
        
        long_transactions = check_long_transactions(cursor)
        if long_transactions:
            alerts.append(format_alert("Зависшие транзакции", long_transactions))
        
        blocked = check_blocked_queries(cursor)
        if blocked:
            alerts.append(format_alert("Блокировки", blocked))
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        alerts.append(f"? <b>Ошибка мониторинга</b>\n{str(e)}")
    
    # Отправляем алерты
    for alert in alerts:
        send_telegram(alert)
    
    if not alerts:
        print(f"[{datetime.now()}] OK - no issues found")


if __name__ == '__main__':
    main()

Добавляем в cron:

* * * * * /usr/bin/python3 /opt/monitoring/pg_monitor.py >> /var/log/pg_monitor.log 2>&1

И не забываем переменные окружения в /etc/environment или где вам удобно.

Как не допускать deadlock

Лечить — хорошо, но лучше не болеть.

Правило #1: Порядок блокировок

Если два процесса всегда захватывают строки в одинаковом порядке — deadlock невозможен. В примере с переводом денег:

-- Всегда сначала меньший id
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- меньший id первым
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Второй процесс тоже должен начинать с id = 1, даже если списывает с id = 2.

Правило #2: Короткие транзакции

Чем дольше транзакция держит блокировки, тем больше шанс пересечься. Не делайте внутри транзакции HTTP-запросы, не ждите ответа от пользователя, не запускайте тяжёлые вычисления.

# Плохо
with db.transaction():
    user = db.get_user(user_id)
    result = external_api.validate(user)  # HTTP запрос внутри транзакции!
    db.update_user(user_id, validated=result)

# Хорошо
user = db.get_user(user_id)
result = external_api.validate(user)  # HTTP вне транзакции
with db.transaction():
    db.update_user(user_id, validated=result)

Правило #3: SELECT FOR UPDATE с умом

Если знаешь, что будешь обновлять строку — блокируй сразу:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- теперь строка наша, никто не влезет
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Для нескольких строк:

SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

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

Правило #4: NOWAIT и SKIP LOCKED

Если не хотите ждать блокировку — используйте NOWAIT:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- если строка заблокирована — сразу ошибка

Или SKIP LOCKED для обработки очередей:

SELECT * FROM tasks WHERE status = 'pending' 
ORDER BY created_at 
LIMIT 1 
FOR UPDATE SKIP LOCKED;
-- пропустит заблокированные, возьмёт первую свободную

Что ещё почитать

PostgreSQL документация по локам достаточно внятная, но многословная. Из практичного:

• log_lock_waits = on в postgresql.conf — будет логировать все ожидания блокировок дольше deadlock_timeout

• lock_timeout — максимальное время ожидания блокировки для сессии

• statement_timeout — максимальное время выполнения запроса

Ставьте разумные таймауты на уровне приложения, не полагайтесь только на базу.

Надеюсь, этот разбор поможет кому-то избежать ночных звонков. Или хотя бы быстрее разобраться, когда звонок всё-таки случится. Если есть свои истории про deadlock на проде — делитесь в комментариях, всегда интересно послушать чужие грабли.