Три часа ночи. Телефон орёт. В слаке паника: «Прод лежит, ничего не работает, база не отвечает». Ты продираешь глаза, лезешь в мониторинг и видишь — приложение встало колом. Не 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_timeoutlock_timeout— максимальное время ожидания блокировки для сессииstatement_timeout— максимальное время выполнения запроса
Ставьте разумные таймауты на уровне приложения, не полагайтесь только на базу.
Надеюсь, этот разбор поможет кому-то избежать ночных звонков. Или хотя бы быстрее разобраться, когда звонок всё-таки случится. Если есть свои истории про deadlock на проде — делитесь в комментариях, всегда интересно послушать чужие грабли.
