Блокировки PostgreSQL: как найти блокирующий запрос и снять

Запрос может внезапно «зависнуть» не из-за плохого плана, а потому, что ждёт блокировку. В такой ситуации важен не самый долгий запрос в базе, а цепочка зависимостей: какой сеанс ждёт, кто стоит перед ним и какая транзакция удерживает ресурс. Завершать первый попавшийся PID опасно — можно оборвать полезную операцию, потерять незакоммиченные изменения и запустить волну повторных запросов.

Диагностика ниже рассчитана на PostgreSQL 18 и начинается с чтения состояния. Для просмотра полных текстов чужих запросов и части служебных полей обычно нужны права суперпользователя либо роль с правами pg_read_all_stats, например через предопределённую роль pg_monitor. Сигнальные функции тоже требуют отдельных прав; доступ к ним не стоит выдавать прикладному пользователю.

SQL / 01koddo

Разбирай цепочку от ожидающего к корню

Быстрый снимок: кто ждёт блокировку

Начните с pg_stat_activity. Состояние active не означает, что запрос прямо сейчас работает на CPU: если wait_event_type = 'Lock', он выполняется, но ждёт блокировку. Время транзакции и время текущего запроса — разные величины, поэтому полезны оба поля.

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  now() - xact_start AS transaction_age,
  now() - query_start AS query_age,
  wait_event_type,
  wait_event,
  pg_blocking_pids(pid) AS blocking_pids,
  left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND (
    wait_event_type = 'Lock'
    OR cardinality(pg_blocking_pids(pid)) > 0
    OR state LIKE 'idle in transaction%'
  )
ORDER BY xact_start NULLS LAST, query_start NULLS LAST;

У блокировки нет одного достаточного признака. wait_event_type = 'Lock' подтверждает текущее ожидание; pg_blocking_pids(pid) называет сеансы, перед которыми стоит ожидающий процесс; xact_start показывает возраст всей транзакции. У сеанса idle in transaction поле query_start относится к последней команде: активного запроса в этот момент нет.

Результат — моментальный снимок. Между чтением и действием сеанс может закончиться, а операционная система позже может переиспользовать PID. Поэтому PID всегда записывают вместе с backend_start, пользователем, приложением и адресом клиента, а перед cancel или terminate проверяют снова.

Что добавляет pg_locks

pg_locks хранит по строке на активный блокируемый объект, режим и процесс. granted = true означает, что блокировка получена; false — что процесс её ждёт. Представление объясняет, чего именно ждёт известный PID:

SELECT
  l.pid,
  a.state,
  l.locktype,
  l.relation::regclass AS relation,
  l.page,
  l.tuple,
  l.transactionid,
  l.mode,
  l.granted,
  l.waitstart,
  left(a.query, 160) AS query
FROM pg_locks AS l
LEFT JOIN pg_stat_activity AS a USING (pid)
WHERE l.pid = 7241
ORDER BY l.granted, l.locktype, l.mode;

Замените 7241 на PID из первого запроса. Для ожидания изменения строки часто виден не locktype = 'tuple', а ожидание transactionid: информация о большинстве блокировок строк хранится в самих строках, а ожидающий процесс ждёт завершения транзакции-владельца. Поэтому искать пару blocker/blocked только по совпавшим полям pg_locks ненадёжно.

Документация PostgreSQL прямо рекомендует для этой связи pg_blocking_pids(). Функция учитывает конфликтующие режимы, очередь ожидания и параллельные workers. pg_locks оставьте для ответа на второй вопрос: какой тип объекта и режим участвуют в ожидании.

Цепочка blocker → blocked

Один блокирующий сеанс способен остановить десятки запросов, а один из ожидающих — стать blocker для следующего. Следующий запрос сначала строит прямые рёбра через pg_blocking_pids(), затем разворачивает их от корневого blocker к жертвам:

WITH RECURSIVE waits AS (
  SELECT DISTINCT
    blocked.pid AS blocked_pid,
    blocker.pid AS blocker_pid
  FROM pg_stat_activity AS blocked
  CROSS JOIN LATERAL
    unnest(pg_blocking_pids(blocked.pid)) AS blocker(pid)
  WHERE blocked.datname = current_database()
),
chain AS (
  SELECT
    w.blocker_pid AS root_pid,
    w.blocker_pid,
    w.blocked_pid,
    1 AS depth,
    ARRAY[w.blocker_pid, w.blocked_pid] AS path
  FROM waits AS w
  WHERE NOT EXISTS (
    SELECT 1
    FROM waits AS parent
    WHERE parent.blocked_pid = w.blocker_pid
  )

  UNION ALL

  SELECT
    c.root_pid,
    w.blocker_pid,
    w.blocked_pid,
    c.depth + 1,
    c.path || w.blocked_pid
  FROM chain AS c
  JOIN waits AS w ON w.blocker_pid = c.blocked_pid
  WHERE NOT w.blocked_pid = ANY(c.path)
)
SELECT
  c.root_pid,
  c.depth,
  c.blocker_pid,
  blocker.state AS blocker_state,
  now() - blocker.xact_start AS blocker_xact_age,
  left(blocker.query, 100) AS blocker_query,
  c.blocked_pid,
  blocked.wait_event,
  now() - blocked.query_start AS blocked_query_age,
  left(blocked.query, 100) AS blocked_query
FROM chain AS c
LEFT JOIN pg_stat_activity AS blocker ON blocker.pid = c.blocker_pid
LEFT JOIN pg_stat_activity AS blocked ON blocked.pid = c.blocked_pid
ORDER BY c.root_pid, c.depth, c.blocker_pid, c.blocked_pid;

Разбор обычно начинают с корневого blocker в строке с depth = 1, но это ещё не основание завершать сеанс. Корень может выполнять миграцию, платёжную операцию или другой запрос, который дешевле дождаться. PID 0 в результате означает, что конфликтующую блокировку держит prepared transaction; такого blocker нет в pg_stat_activity, его проверяют через pg_prepared_xacts и завершают осознанным COMMIT PREPARED либо ROLLBACK PREPARED.

Если запрос вернул пусто, это не доказывает, что задержки не было: блокировка могла освободиться между снимками. Для повторяющихся эпизодов полезен log_lock_waits: он пишет сообщение, когда ожидание превышает deadlock_timeout. Часто опрашивать pg_blocking_pids() тоже не бесплатно — функции нужен краткий эксклюзивный доступ к общему состоянию lock manager.

Воспроизводимый пример в трёх сеансах

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

CREATE TABLE lock_demo (
  id integer PRIMARY KEY,
  balance integer NOT NULL
);

INSERT INTO lock_demo (id, balance) VALUES (1, 1000);

В сеансе A откройте транзакцию и не завершайте её:

BEGIN;

UPDATE lock_demo
SET balance = balance + 100
WHERE id = 1;

SELECT pg_backend_pid();

В сеансе B выполните конкурентное изменение той же строки. Команда будет ждать:

BEGIN;

UPDATE lock_demo
SET balance = balance - 50
WHERE id = 1;

В сеансе C запустите быстрый снимок и запрос к pg_locks. У B будет wait_event_type = 'Lock', а pg_blocking_pids(B_pid) вернёт PID сеанса A. В A выполните ROLLBACK; UPDATE в B продолжится. После этого завершите B и удалите учебный объект:

ROLLBACK;
DROP TABLE lock_demo;

Такой опыт показывает границу: PostgreSQL удерживает обычные блокировки до конца транзакции, а не до конца отдельного UPDATE.

Ожидание блокировки и deadlock — не одно и то же

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

Deadlock — цикл. Например, A уже изменила строку 1 и хочет строку 2, а B изменила строку 2 и хочет строку 1. Продолжить не может никто. PostgreSQL проверяет такие циклы и прерывает одну из транзакций с ошибкой deadlock detected; полагаться на выбор «жертвы» нельзя. Приложение должно откатить неудавшуюся транзакцию и при необходимости повторить её целиком.

Лечить deadlock периодическим terminate не нужно. Устойчивое исправление — брать строки и таблицы в одинаковом порядке во всех путях приложения и не держать транзакцию открытой во время сетевого вызова или ожидания пользователя.

Откуда берутся длинные цепочки

Idle in transaction

Сеанс выполнил BEGIN, изменил данные и ждёт следующую команду клиента. Сам запрос уже не работает, но транзакция остаётся открытой, а её блокировки — занятыми. Поэтому pg_cancel_backend() здесь обычно не решает проблему: отменять нечего. Сначала ищут владельца соединения и просят приложение выполнить COMMIT или ROLLBACK. Заодно длинная открытая транзакция мешает VACUUM удалить версии строк, которые она ещё может видеть; связь с bloat разобрана в статье про обслуживание индексов.

DDL на горячей таблице

Многие варианты ALTER TABLE по умолчанию запрашивают ACCESS EXCLUSIVE, хотя для отдельных подкоманд документация задаёт более слабый режим. DDL может ждать старую транзакцию, а следующие запросы — встать за DDL в очереди. В итоге короткая миграция выглядит корнем инцидента, хотя исходный blocker появился раньше. Проверяйте всю цепочку и запускайте миграции с ограниченным lock_timeout, чтобы они завершались ошибкой, а не собирали очередь.

Долгий UPDATE

Массовый UPDATE держит блокировки до конца транзакции. Конкурентные изменения тех же строк ждут её завершения; большое число затронутых строк увеличивает область конфликта и цену отмены. Иногда такой запрос ещё и имеет плохой план. После снятия инцидента разберите фактический план и буферы в безопасной среде, затем сверьте индексы с разбором индексов PostgreSQL.

pg_cancel_backend() или pg_terminate_backend()

pg_cancel_backend(pid) отменяет текущий запрос, но сохраняет соединение. Это первый технический вариант для активного blocker: приложение получает ошибку и может корректно завершить транзакцию. Cancel не гарантирует освобождение всех блокировок. Если в транзакции были предыдущие команды, соединение может остаться в открытой либо ошибочной транзакции до COMMIT/ROLLBACK; у idle in transaction текущего запроса вообще нет.

pg_terminate_backend(pid) завершает сеанс целиком. Открытая транзакция откатывается, а соединение исчезает. Это жёстче: теряется вся незакоммиченная работа сеанса, клиент может немедленно переподключиться и повторить тот же запрос, а большой объём уже сделанных изменений оставляет WAL, мёртвые версии строк и работу для VACUUM. PostgreSQL не переписывает при откате каждую изменённую строку обратно, как система с физическим undo, но обещать мгновенное и безрисковое освобождение всё равно нельзя: завершение транзакции, очистка ресурсов и последствия для нагрузки требуют наблюдения.

Безопасная последовательность действий:

1. Сохранить снимок pg_stat_activity, цепочку и строки pg_locks; определить корневой PID и объект.
2. Проверить pid, backend_start, usename, application_name, client_addr, возраст транзакции и полный текст запроса. Не воздействовать на autovacuum, репликацию или служебный backend только потому, что он старый.
3. Связаться с владельцем приложения. Предпочтительный исход — штатный COMMIT или ROLLBACK из клиента.
4. Если blocker активен и запрос можно прервать, вызвать cancel, затем заново проверить цепочку.
5. Terminate использовать, только если соединение не завершает транзакцию, владелец не может вмешаться, а ущерб от очереди уже выше цены отката. После этого наблюдать за повторными подключениями, WAL, репликами, autovacuum и задержками.

Перед сигналом снова получите идентификаторы:

SELECT
  pid,
  backend_start,
  usename,
  application_name,
  client_addr,
  state,
  xact_start,
  wait_event_type,
  wait_event,
  query
FROM pg_stat_activity
WHERE pid = 6110;

Подставив проверенный PID, сначала отмените запрос:

SELECT pg_cancel_backend(6110);

Если после согласования нужен terminate, PostgreSQL 18 принимает вторым аргументом время ожидания фактического завершения backend в миллисекундах:

SELECT pg_terminate_backend(6110, 10000);

Здесь 10000 — пример операторского ожидания ответа функции, а не безопасное значение для любой базы и не предел длительности последствий отката. Без второго аргумента true сообщает лишь об успешной отправке сигнала, а не подтверждает, что процесс уже завершился.

Тайм-ауты ограничивают ущерб, но не заменяют диагностику

lock_timeout прерывает команду, если отдельная попытка получить блокировку длится дольше лимита. statement_timeout ограничивает всё время выполнения команды, включая работу и ожидания. Если оба параметра ненулевые, ставить lock_timeout равным или больше statement_timeout обычно бессмысленно: раньше сработает общий лимит.

Для миграции параметры удобно задавать локально внутри транзакции, чтобы не менять поведение остальных соединений:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '30s';

ALTER TABLE orders
  ADD COLUMN processing_note text;

COMMIT;

Две секунды и 30 секунд здесь нужны только для демонстрации синтаксиса. Универсальных значений нет. Порог выбирают по нормальной длительности транзакций, SLO запроса, цене повтора и окну миграции. Сначала измеряют распределение времени на своей нагрузке, затем задают разные пределы для интерактивных запросов, фоновых задач и DDL.

Для забытых транзакций существует idle_in_transaction_session_timeout: он завершает сеанс, который слишком долго бездействует внутри открытой транзакции. Настройка полезна как страховка, но способна оборвать законный интерактивный сеанс или удивить пул соединений. Применяйте её к конкретной роли или классу соединений после проверки поведения клиента, а не как одно глобальное число для всех.

Блокировку можно считать снятой только после повторного запроса к pg_stat_activity и pg_blocking_pids(). Успешный вызов сигнальной функции — ещё не результат: корневой сеанс мог смениться, приложение могло повторить транзакцию, а очередь — перестроиться вокруг другого blocker.