Когда в проде «всё зависло», а CPU почти пустой, очень часто проблема не в производительности, а в блокировках: одна транзакция держит ресурс, остальные выстраиваются в очередь. В PostgreSQL это нормальный механизм согласованности, но как только блокировки начинают жить слишком долго, вы получаете лавину таймаутов в приложении, забитый пул соединений и деградацию всего контура.
Для оперативной диагностики почти всегда хватает двух системных представлений:
pg_stat_activity— кто подключён, что делает, какие состояния, сколько длится транзакция и запрос.pg_locks— какие блокировки удерживаются и какие ожидаются (черезgranted).
Ниже — «боевой» набор запросов и подход, который помогает быстро ответить на главный вопрос инцидента: кто реально блокирует, и что с этим делать без лишнего ущерба.
Быстрый чек-лист: действительно ли это блокировки
Перед тем как глубоко копать pg_locks, убедитесь, что симптомы похожи именно на lock wait, а не на I/O, CPU или сеть:
- Много сессий «как будто работают», но прогресса нет, а CPU не загружен.
- В
pg_stat_activity.wait_event_typeчасто видноLock. - Растут таймауты в приложении, увеличивается число активных/висящих транзакций.
Начинать почти всегда стоит с обзора активности: это быстрее и проще интерпретировать, чем сырые строки блокировок.
pg_stat_activity: кого смотреть в первую очередь
Базовый обзор текущих сессий. Сортировка ниже поднимает вверх тех, кто ждёт блокировки, и тех, у кого самые «старые» транзакции/запросы:
SELECT
now() AS ts,
pid,
usename,
datname,
application_name,
client_addr,
state,
wait_event_type,
wait_event,
xact_start,
query_start,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
left(query, 200) AS query_short
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY
(wait_event_type = 'Lock') DESC,
(now() - xact_start) DESC NULLS LAST,
(now() - query_start) DESC NULLS LAST;На что смотреть в первую очередь:
state:active,idle,idle in transaction(обычно самая опасная история — транзакция открыта, а сессия «ничего не делает»).wait_event_type/wait_event: еслиwait_event_type=Lock, это кандидат в blocked query.xact_startи возраст транзакции: длинные транзакции часто являются корневой причиной очередей и проблем с VACUUM.query_startи возраст запроса: помогает быстро отделить «обычно долгий» отчёт от внезапной деградации.
Чаще всего виновник — не тот, кто ждёт (blocked), а тот, кто давно держит транзакцию и не завершает её. Поэтому всегда смотрите и ожидающих, и самых «старых» по
xact_age.
Быстро найти запросы, которые прямо сейчас ждут lock
SELECT
pid,
usename,
application_name,
client_addr,
state,
wait_event_type,
wait_event,
now() - query_start AS waiting_for,
left(query, 300) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY waiting_for DESC;Это ваши blocked queries. Дальше задача — найти того, кто блокирует, а не пытаться лечить последствия.
pg_locks: что именно заблокировано и кто держит
pg_locks показывает блокировки по каждому ресурсу и режиму. Ключевое поле — granted:
granted = true— блокировка уже удерживается.granted = false— сессия ждёт блокировку.
Сами ресурсы бывают разными (таблицы, строки, транзакции, advisory locks), поэтому для диагностики почти всегда нужно соединять pg_locks с pg_stat_activity.
Кто кого блокирует: самый практичный запрос (pg_blocking_pids)
Если ваша версия PostgreSQL поддерживает pg_blocking_pids(pid), это самый короткий путь увидеть пары blocked → blocker.
SELECT
a.pid AS blocked_pid,
a.usename AS blocked_user,
a.application_name AS blocked_app,
a.client_addr AS blocked_client,
now() - a.query_start AS blocked_for,
left(a.query, 200) AS blocked_query,
b.pid AS blocker_pid,
b.usename AS blocker_user,
b.application_name AS blocker_app,
b.client_addr AS blocker_client,
now() - b.xact_start AS blocker_xact_age,
b.state AS blocker_state,
left(b.query, 200) AS blocker_query
FROM pg_stat_activity a
JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS bp(blocker_pid) ON true
JOIN pg_stat_activity b ON b.pid = bp.blocker_pid
WHERE a.wait_event_type = 'Lock'
ORDER BY blocked_for DESC, blocker_xact_age DESC NULLS LAST;Дальше обычно один из трёх сценариев:
- ждём, если блокировка штатная и краткоживущая;
- просим корректно завершить транзакцию (commit/rollback) на стороне приложения/пользователя;
- аккуратно отменяем запрос или завершаем сессию, если блокировка держится слишком долго и ломает критичный контур.
Если нужна детализация по объектам: relation locks (таблицы)
Когда подозрение на DDL против DML (например, ALTER TABLE «держит» таблицу), полезно посмотреть блокировки уровня relation и привязать их к схеме/таблице:
SELECT
a.pid,
a.usename,
a.application_name,
a.client_addr,
l.locktype,
l.mode,
l.granted,
n.nspname AS schema,
c.relname AS relation,
now() - a.query_start AS age,
left(a.query, 200) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
LEFT JOIN pg_class c ON c.oid = l.relation
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE a.datname = current_database()
AND l.locktype = 'relation'
ORDER BY
(l.granted = false) DESC,
age DESC;Так вы быстро увидите, какая таблица «под замком», и кто удерживает конфликтный режим.
Цепочки ожиданий: когда blocked блокируют других blocked
Частый реальный кейс: есть не один blocker, а цепочка. Например, сессия A держит блокировку, сессия B ждёт A, а сессия C ждёт B. Внешне кажется, что «падает всё», хотя корень один.
Практический алгоритм:
- найти «топ блокеров» по количеству ожидающих;
- проверить возраст транзакции блокера и его
state; - если blocker —
idle in transaction, это почти всегда проблема границ транзакций в приложении.
Топ «виновников»: кто блокирует больше всего сессий
WITH blocked AS (
SELECT
a.pid AS blocked_pid,
unnest(pg_blocking_pids(a.pid)) AS blocker_pid
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
)
SELECT
b.blocker_pid,
sa.usename,
sa.application_name,
sa.client_addr,
sa.state,
now() - sa.xact_start AS xact_age,
count(*) AS blocked_sessions,
left(sa.query, 200) AS blocker_query
FROM blocked b
JOIN pg_stat_activity sa ON sa.pid = b.blocker_pid
GROUP BY
b.blocker_pid,
sa.usename,
sa.application_name,
sa.client_addr,
sa.state,
sa.xact_start,
sa.query
ORDER BY blocked_sessions DESC, xact_age DESC NULLS LAST;Во время инцидента это особенно удобно: вместо десятков «ждущих» вы видите 1–3 сессии, которые реально держат систему.
Если параллельно у вас ещё и фронт «ложится» из‑за очередей, может помочь кэширование/ограничение конкуренции на уровне веб-слоя; идеи по устойчивости очередей есть в заметке про настройку proxy_cache со stale и lock в Nginx.
Deadlock: чем отличается от «просто долго ждём»
Deadlock — взаимная блокировка, когда две (или больше) транзакции ждут друг друга, и прогресс невозможен. PostgreSQL умеет находить deadlock и разрывает одну из транзакций, поэтому обычно это проявляется как ошибка запроса (а не как бесконечное ожидание).
Важно для диагностики:
- Долгое ожидание блокировки само по себе не означает deadlock: часто виноваты длинные транзакции, DDL, массовые апдейты.
- Deadlock почти всегда лечится в приложении: единый порядок захвата ресурсов, меньше широких транзакций, аккуратнее с конкурентными обновлениями.
- Для расследования нужен контекст из логов.
Логи для расследования deadlock и долгих ожиданий
Минимальный набор параметров, который стоит проверить (значения подбирайте под свою нагрузку и политику логирования):
log_lock_waits— логировать ожидания блокировок дольше порога;deadlock_timeout— через какое время начинается проверка на deadlock;log_line_prefix— чтобы в логах удобно коррелировать события по PID/пользователю/базе.
Когда включён log_lock_waits, расследование перестаёт быть «угадайкой»: в логах появляется, кто кого ждёт и что именно происходит.
Таймауты как страховка: lock_timeout и statement_timeout
Таймауты не лечат первопричину, но защищают систему от «вечных» ожиданий и разрастания очередей. Два ключевых механизма:
lock_timeout— сколько ждать блокировку, прежде чем запрос завершится ошибкой;statement_timeout— максимальное время выполнения оператора, включая ожидание блокировки.
Часто удобно задавать их на уровне роли/приложения: для веб-запросов один профиль, для миграций и фоновых задач — другой.
Поставить таймауты на сессию (быстро для диагностики)
SET lock_timeout = '3s';
SET statement_timeout = '15s';
Cancel или terminate: что безопаснее во время инцидента
Когда blocker найден, рука тянется к «terminate backend». Но отмена запроса и убийство сессии — разные по последствиям действия:
pg_cancel_backend(pid)— пытается отменить текущий запрос, соединение остаётся. Если сессияidle in transaction, отменять может быть нечего.pg_terminate_backend(pid)— завершает сессию принудительно. Транзакция откатывается, блокировки снимаются, но приложение получит ошибку соединения и может начать ретраи.
Если сомневаетесь, начните с
pg_cancel_backend. Переходите кpg_terminate_backend, когда сессия держит транзакцию слишком долго, блокирует критичный путь и «по-хорошему» не отпускает.
Аккуратно отменить запрос
SELECT pg_cancel_backend(12345);Принудительно завершить сессию
SELECT pg_terminate_backend(12345);Проверки перед pg_terminate_backend
- Это точно blocker, а не «ждущий» (проверьте через
pg_blocking_pids). - Посмотрите
xact_ageиstate;idle in transactionс большим возрастом обычно красный флаг. - Убедитесь, что это не критичный DDL/миграция, откат которой будет очень дорогим.
- Подумайте о ретраях: приложение может мгновенно переподключиться и повторить тот же конфликтующий сценарий.
Частые причины блокировок и как их узнавать
Idle in transaction
Самая частая причина «непонятных» блокировок: приложение открыло транзакцию и ушло в idle, забыв commit/rollback. Такая сессия может удерживать блокировки и параллельно ухудшать ситуацию с автоочисткой.
Диагностика: state = 'idle in transaction' плюс большой xact_age. Лечение: корректные границы транзакций в коде, ограничение времени «жизни» транзакции, дисциплина на уровне приложения.
DDL против DML
Некоторые операции ALTER TABLE, создание индексов без concurrently, изменение типов — берут конфликтные блокировки. В итоге даже обычные SELECT/UPDATE выстраиваются в очередь.
Лечение: планировать миграции, по возможности использовать менее блокирующие варианты и обязательно контролировать время выполнения.
Массовые UPDATE и «горячие» строки
Один большой UPDATE может держать блокировки на множестве строк, а параллельно идут точечные UPDATE/DELETE по тем же ключам. Внешне это выглядит как «всё стало медленным», хотя причина — конкуренция за одни и те же данные.
Лечение: дробить батчи, менять порядок обновлений, убирать лишний параллелизм, следить за планами и индексами.
Runbook: минимальный набор запросов для дежурного
-
Кто ждёт lock прямо сейчас:
SELECT pid, usename, application_name, client_addr, now() - query_start AS waiting_for, left(query, 200) FROM pg_stat_activity WHERE wait_event_type = 'Lock' ORDER BY waiting_for DESC; -
Blocked → Blocker с контекстом:
SELECT a.pid AS blocked_pid, now() - a.query_start AS blocked_for, left(a.query, 200) AS blocked_query, b.pid AS blocker_pid, now() - b.xact_start AS blocker_xact_age, b.state AS blocker_state, left(b.query, 200) AS blocker_query FROM pg_stat_activity a JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS bp(blocker_pid) ON true JOIN pg_stat_activity b ON b.pid = bp.blocker_pid WHERE a.wait_event_type = 'Lock' ORDER BY blocked_for DESC; -
Топ блокеров по числу заблокированных сессий:
WITH blocked AS ( SELECT a.pid AS blocked_pid, unnest(pg_blocking_pids(a.pid)) AS blocker_pid FROM pg_stat_activity a WHERE a.wait_event_type = 'Lock' ) SELECT blocker_pid, count(*) AS blocked_sessions FROM blocked GROUP BY blocker_pid ORDER BY blocked_sessions DESC;
Что улучшить, чтобы реже ловить блокировки
Зафиксировать таймауты. Для веб-контуров часто выгоднее ограничить ожидание через
lock_timeoutиstatement_timeout, чем копить очередь.Нормализовать транзакции в коде. Не держите транзакцию открытой во время внешних вызовов, ожидания сети и долгих вычислений.
Разносить тяжёлые миграции. DDL в часы пик — частый источник массовых очередей.
Включить диагностическое логирование ожиданий. Это резко упрощает разбор инцидентов.
Хранить runbook рядом. Чтобы в стрессе не вспоминать запросы и не действовать наугад.
Если PostgreSQL крутится на отдельном сервере/виртуалке, убедитесь, что у вас хватает ресурсов и вы не боретесь ещё и с соседями по железу. Для выделенного контура часто удобнее использовать VDS, где проще контролировать дисковую подсистему и лимиты.
Итог
pg_stat_activity отвечает на вопрос «кто сейчас что делает и сколько это длится», а pg_locks — «кто что держит и кто чего ждёт». Вместе они позволяют быстро разобрать почти любой инцидент с блокировками: найти blocked queries, определить настоящего blocker’а, увидеть очереди/цепочки и принять решение — ждать, отменять запрос или завершать проблемную сессию.
