Блокировки в PostgreSQL: как за минуту найти, кто держит продакшен

Когда в боевой базе вдруг всё встаёт колом, а мониторинг заваливает алертами про таймауты, единственное, что хочется знать, - кто именно забрал лок и не отдаёт. Без паники и долгих копаний в доках. Ниже - живые запросы и ровно та логика, которая помогает выйти на виновника за считаные секунды.

Почему блокировки на проде - это больно

Клиент жмёт «Оплатить», фронтенд уходит в бесконечную загрузку, бэкенд отваливается по таймауту. Блокировка, зацепившая триггерную таблицу, способна положить весь сервис. При этом база не падает - она просто не отвечает, пока кто-то держит чужой ресурс.

Хуже всего, когда разработчики начинают одновременно выяснять, что случилось, и множат число параллельных транзакций, только усугубляя ситуацию. Поэтому главное правило для дежурного инженера: не дёргать базу наобум, а методично пройти путь от симптомов к корню.

Два главных помощника: pg_stat_activity и pg_locks

PostgreSQL из коробки предоставляет всё, что нужно для разбора блокировок, - системные представления. Двух из них достаточно, чтобы увидеть полную картину.

pg_stat_activity - кто сейчас в базе и что делает

Это представление показывает по одной строке на каждый активный серверный процесс (backend). Здесь можно найти:

pid - идентификатор процесса, который понадобится для точечного воздействия;
usename - имя пользователя;
query - выполняющийся или последний завершённый запрос;
state - статус (active, idle, idle in transaction и т. д.);
wait_event_type и wait_event - информация о том, чего ждёт сессия;
query_start, xact_start и backend_start - тайминги, по которым видно, как долго висит транзакция или запрос.

Именно wait_event_type даёт мгновенную зацепку: если сессия ждёт блокировку, в этом поле будет значение 'Lock'.

pg_locks - карта выданных и ожидающих блокировок

Второе представление - pg_locks. Оно предоставляет построчный слепок текущих блокировок:

locktype - тип блокировки (relation, transactionid, virtualxid, advisory и др.);
relation - OID таблицы, если блокировка на уровне отношений (можно преобразовать в имя через pg_class);
mode - режим блокировки (AccessShareLock, RowExclusiveLock, ExclusiveLock и т. д.);
granted - true, если блокировка получена, и false, если процесс ждёт её выдачи.

Процесс, ожидающий освобождения ресурса, всегда имеет строку с granted = false. Процесс-владелец на тот же объект - строку с granted = true. Сопоставив их, мы получим иерархию блокировок.

Мгновенная диагностика ожидающих сессий

Когда мониторинг орёт, нужно сразу найти все страдающие процессы. Для быстрого скрининга есть два пути - по событиям ожидания и по флагу в pg_locks.

Фильтр по wait_event_type = ‘Lock’

Самый быстрый способ - запросить pg_stat_activity с условием на блокировочное ожидание:

SELECT pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND state != 'idle';

Строки, где state = 'idle', мы исключаем, потому что они чаще всего относятся к уже неактивным соединениям, которые просто не закрыты.

Результат покажет всех «ожидающих» прямо сейчас: кто висит, как долго и на каком запросе. Но ещё не покажет, кто запер ресурс. Для этого потребуется следующий шаг.

Альтернативный путь: поиск не‑granted записей в pg_locks

Если необходимо проверить состояние через pg_locks или версия PostgreSQL не предоставляет удобного разделения по wait_event_type = 'Lock', можно использовать флаг granted:

SELECT l.pid,
  l.locktype,
  l.mode,
  l.granted,
  a.usename,
  a.query
FROM pg_locks l
  JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.granted = FALSE
  AND a.state != 'idle';

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

Находим виновника: кто реально держит блокировку

Идентифицировав заблокированный процесс, дальше смотрим на его «тюремщика». Здесь есть два основных подхода - лёгкий и детальный.

pg_blocking_pids() - «быстрая кнопка» для одного запроса

Функция pg_blocking_pids(pid) возвращает массив PID-ов процессов, из-за которых переданная сессия находится в ожидании. Если массив пуст, процесс не заблокирован. Если не пуст - это и есть виновники.

Пример для конкретного PID заблокированного, скажем, 2942:

SELECT pg_blocking_pids(2942);

На практике можно сразу объединить с pg_stat_activity, чтобы увидеть текст запросов:

SELECT blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
  JOIN pg_stat_activity blocking ON blocking.pid = ANY (PG_BLOCKING_PIDS(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Здесь мы не гадаем, а сразу получаем пару «жертва - блокировщик». Это самый простой сценарий для оперативного разбора.

Детальный JOIN‑запрос с именами таблиц и текстом запросов

Когда одного PID недостаточно и нужно знать, какая именно таблица залочена и в каком режиме, строим JOIN через pg_locks, связывая заблокированные и удерживающие записи по типу и OID. Дополнительно прикручиваем pg_class для имён таблиц.

SELECT waiting.pid AS waiting_pid,
  waiting.usename AS waiting_user,
  waiting.query AS waiting_query,
  waiting.query_start AS waiting_since,
  lock_wait.relation::regclass AS locked_table,
  lock_wait.mode AS wait_mode,
  holding.pid AS holding_pid,
  holding.usename AS holding_user,
  holding.query AS holding_query,
  holding.query_start AS holding_since
FROM pg_locks lock_wait
  JOIN pg_stat_activity waiting ON lock_wait.pid = waiting.pid
  JOIN pg_locks lock_hold ON lock_wait.relation = lock_hold.relation
  AND lock_wait.locktype = lock_hold.locktype
  AND lock_wait.granted = FALSE
  AND lock_hold.granted = TRUE
  JOIN pg_stat_activity holding ON lock_hold.pid = holding.pid
WHERE waiting.wait_event_type = 'Lock'
  AND waiting.state != 'idle';

Пояснения:

lock_wait - строки из pg_locks для процессов, которые ждут (granted = false);
lock_hold - строки для владельцев (granted = true) на тех же объектах;
lock_wait.relation::regclass преобразует OID таблицы в человекочитаемое имя;
JOIN pg_stat_activity дважды - чтобы получить текст запросов и тайминги для обоих участников конфликта.

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

Собираем универсальный «экран оперативного дежурного»

Объединив лучшие куски, можно сделать один запрос, который в продакшен-сессии выдаёт всё самое важное: PID блокировщика, PID заблокированного, их запросы, длительность ожидания и затронутую таблицу. Подобные запросы живут во внутренних вики многих команд и спасают ночью.

SELECT wait.pid AS blocked_pid,
  wait.usename AS blocked_user,
  wait.query AS blocked_query,
  EXTRACT(
    EPOCH
    FROM (now() - wait.query_start)
  )::int AS blocked_sec,
  wait_lock.relation::regclass AS locked_table,
  wait_lock.mode AS wait_mode,
  hold.pid AS blocking_pid,
  hold.usename AS blocking_user,
  hold.query AS blocking_query,
  EXTRACT(
    EPOCH
    FROM (now() - hold.query_start)
  )::int AS blocking_sec
FROM pg_stat_activity wait
  JOIN pg_locks wait_lock ON wait.pid = wait_lock.pid
  JOIN pg_locks hold_lock ON wait_lock.relation = hold_lock.relation
  AND wait_lock.locktype = hold_lock.locktype
  AND wait_lock.granted = false
  AND hold_lock.granted = true
  JOIN pg_stat_activity hold ON hold_lock.pid = hold.pid
WHERE wait.wait_event_type = 'Lock'
  AND wait.state != 'idle';

Здесь добавлен подсчёт секунд ожидания для обеих сторон - сразу видно, не пора ли вмешаться.

Важно: любые системные запросы сначала обкатывают на тестовом окружении. На проде лишний тяжёлый джойн в критический момент может добавить нагрузки.

Что делать, когда источник найден

Обнаружили корневого блокировщика - остаётся принять решение.

Не дёргаться. Сначала оценить, что делает процесс:

Если он active и выполняется минуту‑другую, возможно, операция завершится сама.
Если запрос висит в idle in transaction десятки минут - перед вами забытая транзакция. Её владелец, скорее всего, ушёл на обед, оставив открытый курсор. Такой процесс смело снимают.

Выставить таймауты на уровне сессии или через параметры кластера statement_timeout и lock_timeout. Например:

SET lock_timeout = '5s';

Тогда запрос сам упадёт, если за 5 секунд не сможет взять лок, и не будет висеть бесконечно.

Принудительное прерывание - только в крайнем случае. Для этого есть две функции:

pg_cancel_backend(pid) - отменяет текущий выполняющийся запрос, но оставляет сессию открытой. Более мягкий вариант: транзакция откатится среднестатистически быстро, данные останутся консистентными.
pg_terminate_backend(pid) - убивает серверный процесс целиком, разрывая соединение. При этом также произойдёт откат незавершённой транзакции, но для приложения это выглядит как обрыв связи. Используйте, когда cancel не помогает.

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

Профилактика. Заведите мониторинг на длинные транзакции и сессии в статусе idle in transaction. Подобные проверки можно добавить в cron или Prometheus‑экспортёр. Чем раньше вы увидите «задумчивую» транзакцию, тем меньше шансов, что она разовьётся в прод-блокировку.

FAQ по блокировкам в PostgreSQL

Как максимально быстро найти все заблокированные запросы?

Самый быстрый способ - отфильтровать pg_stat_activity по wait_event_type = 'Lock'. Если такая колонка недоступна (очень старые версии), ищите в pg_locks строки с granted = false, связанные с активными сессиями.

Что делать, если запрос висит больше N минут и не отпускает?

Сначала определите, активен ли он и что делает. Если он idle in transaction - безопаснее всего снять процесс через pg_cancel_backend. Если запрос активен и потребляет ресурсы - свяжитесь с владельцем, а в критическом случае используйте cancel. Для предотвращения повторения настройте statement_timeout или lock_timeout.

С какой версии PostgreSQL доступна функция pg_blocking_pids?

Функция pg_blocking_pids() появилась в PostgreSQL 9.6 и присутствует во всех более поздних версиях.

Как получить не только PID, но и название таблицы, на которой произошла блокировка?

Колонка relation в pg_locks содержит OID таблицы. Приведя OID к имени через ::regclass или присоединив pg_class, вы получите человекочитаемое название. Например: wait_lock.relation::regclass AS locked_table.

Можно ли безопасно принудительно снять блокирующий запрос и как это сделать?

Относительно безопасный метод - SELECT pg_cancel_backend(<pid>);. Он отменяет текущий запрос, но оставляет сессию открытой. Это штатное прерывание, которое не нарушает целостность базы. pg_terminate_backend() - более жёсткая мера, применяется в крайнем случае.

Чем отличаются pg_cancel_backend и pg_terminate_backend при решении проблем с блокировками?

pg_cancel_backend отменяет выполняющийся запрос и вызывает откат текущей транзакции, но сессия остаётся живой - можно сразу выполнить новый запрос. pg_terminate_backend разрывает соединение на уровне процесса, что для приложения равносильно обрыву; после него также происходит откат незавершённой транзакции, но восстановление соединения ложится на клиент.

Почему запрос висит на Lock, хотя я не вижу конфликтующих транзакций?

Конфликт может быть вызван рекомендательными блокировками (advisory locks) или распределёнными транзакциями. Проверьте pg_locks на записи с locktype = 'advisory' и granted = true - их влияние не всегда очевидно, но они точно так же способны заблокировать другие сессии.

Вывод

Диагностика блокировок в PostgreSQL сводится к чёткой последовательности: находим ожидающие процессы, определяем их блокировщиков, оцениваем ситуацию и при необходимости мягко вмешиваемся. Два базовых инструмента - pg_stat_activity и pg_locks, подкреплённые функцией pg_blocking_pids(), - закрывают большинство инцидентов.

Держите под рукой готовый «экран оперативного дежурного», обкатанный на тестовом стенде, и помните: убивать процессы без разбора - последнее дело. Обычно достаточно настройки таймаутов и регулярного мониторинга, чтобы блокировки перестали будить вас по ночам.