Блокировки в PostgreSQL: как найти blocker и безопасно освободить очередь

Проблема с блокировками редко выглядит как сообщение «у вас блокировка». Обычно всё начинается иначе:

• форма в приложении не сохраняется;
• миграция висит на простом ALTER TABLE;
• несколько воркеров внезапно перестают разбирать очередь;
• запрос, который обычно выполняется за миллисекунды, продолжает работать минуту;
• растёт число активных соединений, хотя нагрузка на CPU и диски остаётся обычной.

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

Практическая задача состоит не в том, чтобы увидеть список всех lock в PostgreSQL. Нужно быстро ответить на четыре вопроса:

1. Какой запрос ждёт?
2. Какой PID не даёт ему продолжить работу?
3. Почему blocker всё ещё удерживает блокировку?
4. Что безопаснее сделать: подождать, завершить транзакцию в приложении, закрыть cursor или прервать сессию?

Блокировка не всегда означает проблему

PostgreSQL постоянно использует блокировки для согласованной конкурентной работы. Сами по себе строки в pg_locks — не инцидент. Важно наличие ожидания, его длительность и влияние на другие сессии.

Обычный SELECT и изменение данных чаще всего могут выполняться параллельно благодаря MVCC. Но конфликт появляется, когда две операции запрашивают несовместимые режимы блокировок или конкурируют за одну версию строки.

Например, первая транзакция изменила заказ и не завершилась:

BEGIN;

UPDATE orders
SET status = 'paid'
WHERE id = 1042;

-- COMMIT или ROLLBACK пока не выполнен

Вторая транзакция пытается изменить тот же заказ:

UPDATE orders
SET status = 'cancelled'
WHERE id = 1042;

Ускорение второго UPDATE индексом не устранит ожидание. Сначала должна завершиться первая транзакция.

Другой распространённый случай — DDL. Обычный SELECT держит табличную блокировку ACCESS SHARE до конца транзакции. Она не мешает параллельным чтениям и изменениям строк, но конфликтует с ACCESS EXCLUSIVE, который требуется многим операциям ALTER TABLE. Поэтому короткий DDL может долго ждать старую транзакцию с уже завершившимся на вид чтением.

Полезная формула для расследования:

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

Сценарий 1. Транзакция осталась idle in transaction

Это одна из самых неприятных причин. Приложение выполнило UPDATE, перестало отправлять запросы, но не сделало COMMIT или ROLLBACK. В pg_stat_activity сессия выглядит бездействующей, хотя её транзакция продолжает удерживать блокировки.

Такое случается, если:

• код открыл транзакцию и ушёл в долгую внешнюю операцию;
• обработчик ошибки забыл выполнить rollback;
• пользователь открыл ручную транзакцию в SQL-клиенте и переключился на другую задачу;
• соединение вернулось в неправильное состояние после тайм-аута на стороне приложения.

Смотрите не только на query_start, но и на xact_start, state и последний запрос blocker. Старый xact_start при idle in transaction — сильный сигнал, что проблема находится в жизненном цикле транзакции, а не в производительности SQL.

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

Сценарий 2. Миграция образовала очередь

Представим, что миграция выполняет:

ALTER TABLE orders
ADD COLUMN source text;

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

В списке сессий это выглядит как множество проблемных PID. На деле устранять нужно не каждый лист очереди, а понять цепочку:

длинная транзакция
└── ожидающий ALTER TABLE
    ├── SELECT из API
    ├── UPDATE фонового задания
    └── ещё один SELECT

Именно поэтому дерево полезнее плоского списка. Оно показывает корень и объясняет, почему один blocker влияет на несколько внешне независимых запросов.

Для production-миграций задавайте ограниченное время ожидания блокировки, чтобы DDL не стоял в очереди бесконечно:

BEGIN;
SET LOCAL lock_timeout = '5s';

ALTER TABLE orders
ADD COLUMN source text;

COMMIT;

Если блокировку не удалось получить быстро, миграцию можно повторить в более подходящий момент после изучения blocker. Это безопаснее, чем оставлять DDL в очереди без контроля.

Сценарий 3. Несколько процессов меняют одну горячую строку

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

UPDATE job_counters
SET processed = processed + 1
WHERE job_id = 77;

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

Здесь не всегда нужно завершать blocker. Часто правильное решение архитектурное:

• сократить время транзакции;
• не выполнять внешние вызовы между BEGIN и COMMIT;
• обновлять независимые строки вместо одного общего счётчика;
• использовать FOR UPDATE SKIP LOCKED для конкурентного разбора очереди, если семантика задачи это допускает;
• выполнять одинаковые изменения объектов в одном порядке, чтобы уменьшить риск deadlock.

Мониторинг помогает отличить редкое короткое ожидание от устойчивой очереди. Поэтому порог длительности важнее самого факта появления lock.

Сценарий 4. Открытый cursor мешает DDL

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

Для обычного читающего запроса это особенно заметно при DDL: cursor может не мешать INSERT или UPDATE, но отложить операцию, которой нужен ACCESS EXCLUSIVE. Если запрос cursor содержит FOR UPDATE или FOR SHARE, последствия могут затронуть и работу со строками.

В pgtools загрузка порциями реализована через pg-cursor. Размер порции, автодогрузка при прокрутке и тайм-аут неактивного cursor задаются в настройках. На панели результата видны состояние cursor и число загруженных строк. Когда оставшиеся данные не нужны, cursor лучше закрыть, а не оставлять вкладку ждать автоматического тайм-аута.

Если собственный cursor pgtools стал blocker, мониторинг связывает его PID с реестром активных cursor. В карточке блокировки можно:

• закрыть cursor штатным механизмом;
• перейти к вкладке, которая его открыла;
• открыть полное дерево блокировок;
• скрыть уведомление по этому случаю.

Это важное отличие от безусловного завершения backend: pgtools знает владельца PID и может сначала предложить более точное действие.

Подробнее о порционной загрузке — в разделе документации «Результаты запросов».

Как найти прямого blocker с помощью SQL

Для первичной диагностики удобно соединить pg_stat_activity с результатом pg_blocking_pids(). Функция возвращает PID процессов, которые мешают конкретной сессии получить блокировку:

SELECT
    waiting.pid AS waiting_pid,
    waiting.usename AS waiting_user,
    waiting.application_name AS waiting_application,
    now() - waiting.query_start AS wait_duration,
    waiting.query AS waiting_query,
    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.application_name AS blocker_application,
    blocker.state AS blocker_state,
    now() - blocker.xact_start AS blocker_transaction_age,
    blocker.query AS blocker_query
FROM pg_stat_activity AS waiting
CROSS JOIN LATERAL unnest(pg_blocking_pids(waiting.pid)) AS p(blocker_pid)
JOIN pg_stat_activity AS blocker
  ON blocker.pid = p.blocker_pid
WHERE waiting.wait_event_type = 'Lock'
ORDER BY wait_duration DESC;

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

PostgreSQL рекомендует использовать pg_blocking_pids() для определения blocker, потому что самостоятельное соединение pg_locks с самой собой должно корректно учитывать режимы конфликтов, очередь ожидания и параллельные процессы. pg_locks при этом остаётся полезным источником подробностей о запрошенных и уже полученных блокировках.

Как читать дерево блокировок в pgtools

pgtools получает компактный набор данных из PostgreSQL, а связи и дерево строит на стороне приложения. Это особенно полезно при lock-storm, когда одна проблема породила десятки или сотни ожидающих сессий.

Начинайте с корня дерева, а не с самого долгого ожидающего запроса. Для корневого blocker проверьте:

1. PID, пользователя, базу и application_name;
2. состояние сессии — active или idle in transaction;
3. возраст транзакции по xact_start;
4. последний или текущий SQL;
5. принадлежит ли PID вашему приложению, миграции, администратору или текущему экземпляру pgtools;
6. сколько сессий и какие операции находятся ниже по цепочке.

Большое число потомков повышает приоритет инцидента, но не даёт права автоматически завершать корень. Например, blocker может выполнять критичную платёжную транзакцию, откат которой опаснее нескольких секунд ожидания.

Автоматический мониторинг в pgtools

Дерево удобно во время расследования, но проблему ещё нужно заметить. Поэтому мониторинг блокировок в pgtools работает отдельным фоновым сервисом и не требует держать диагностическую панель открытой.

В секции настроек «Общие» доступны:

• Автоматический мониторинг блокировок — по умолчанию включён;
• Интервал проверки блокировок (сек) — по умолчанию 5 секунд;
• Минимальная длительность блокировки до уведомления — порог, который отсекает короткие штатные ожидания.

Когда обнаружена устойчивая блокировка, в левом нижнем углу основного окна появляется одно сводное уведомление. Оно:

• показывает количество случаев или цепочек;
• обновляется при изменении состава блокировок;
• не создаёт дубликаты при каждом опросе;
• исчезает после устранения всех блокировок;
• визуально выделяется, если хотя бы один blocker принадлежит текущему экземпляру pgtools.

По клику открывается панель с карточками активных блокировок. Для каждого случая видны blocker PID и длительность. Список обновляется на месте, поэтому во время расследования не нужно закрывать и заново открывать дочернее окно.

Если blocker — внешняя сессия, карточка предлагает открыть дерево или скрыть уведомление. Если PID принадлежит cursor pgtools, появляются действия закрытия cursor и перехода к вкладке. Если blocker — другая активная сессия текущего pgtools, можно перейти к её вкладке или вручную прервать выполнение запроса.

Мониторинг ничего не завершает автоматически. Это принципиально: обнаружение технического конфликта можно автоматизировать, а решение об откате пользовательской транзакции требует контекста.

Полное описание интерфейса есть в документации «Блокировки».

Что делать после обнаружения blocker

Безопасный порядок действий выглядит так.

1. Оценить влияние

Посмотрите не только на длительность, но и на дерево. Один ожидающий служебный запрос и очередь из запросов пользовательского API требуют разной срочности.

2. Понять владельца PID

Проверьте usename, application_name, адрес клиента, текст запроса и вкладку pgtools, если сессия локальная. Не завершайте PID только потому, что он находится в корне.

3. Выбрать самое узкое действие

Если blocker — ненужный cursor, закройте cursor. Если это ваша ручная транзакция, выполните COMMIT или ROLLBACK в её вкладке. Если запрос всё ещё полезен и скоро завершится, иногда безопаснее подождать.

Принудительное завершение сессии оставляйте для ситуации, когда владелец и последствия понятны. PostgreSQL откатит её незавершённую транзакцию, а клиент получит разрыв или ошибку выполнения.

4. Проверить, что очередь движется

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

5. Исправить причину, а не только PID

Запишите, почему транзакция жила так долго. Типичные исправления:

• гарантированный COMMIT или ROLLBACK во всех ветках кода;
• более короткие транзакции;
• запрет сетевых вызовов внутри транзакции;
• lock_timeout для DDL и административных операций;
• idle_in_transaction_session_timeout как защитная граница для забытых транзакций;
• контролируемый тайм-аут неактивных cursor;
• единый порядок обновления объектов;
• уменьшение конкуренции за горячие строки.

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

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

PostgreSQL обнаруживает deadlock и прерывает одну из транзакций с ошибкой. Поэтому длительный устойчивый lock чаще означает не deadlock, а blocker, который долго работает, простаивает внутри транзакции или ждёт внешнего действия.

Для deadlock основным источником расследования будут ошибка приложения и серверный журнал. Для устойчивых очередей — pg_stat_activity, pg_blocking_pids(), дерево и история состояния blocker.

Краткий чек-лист при инциденте

1. Убедитесь, что запрос ждёт именно lock: wait_event_type = 'Lock'.
2. Найдите прямой blocker через pg_blocking_pids().
3. Постройте цепочку и найдите корень.
4. Проверьте владельца, состояние и возраст транзакции корневого PID.
5. Если это cursor pgtools, перейдите к вкладке и закройте его штатно.
6. Если это ваша транзакция, завершите её осознанно через COMMIT или ROLLBACK.
7. Прерывайте чужую сессию только после оценки последствий отката.
8. Убедитесь, что очередь исчезла, а запросы продолжили работу.
9. Исправьте причину долгой транзакции или конкуренции.

Главная практическая мысль: лечить нужно не самый заметный ожидающий запрос, а корневую причину цепочки. Автоматический мониторинг pgtools помогает вовремя увидеть устойчивое ожидание, карточки связывают blocker с собственными вкладками и cursor, а дерево показывает реальный масштаб влияния. Решение при этом остаётся за пользователем — именно так диагностика не превращается в опасную автоматику.

Официальная документация PostgreSQL

• Explicit Locking — режимы блокировок, конфликты и deadlock.
• pg_locks — представление активных и ожидающих блокировок.
• Monitoring Database Activity — поля pg_stat_activity и события ожидания.
• System Information Functions — функция pg_blocking_pids().
• DECLARE и CLOSE — жизненный цикл PostgreSQL cursor.