Диагностика ожидания блокировок и взаимоблокировок PostgreSQL с pg_locks, pg_blocking_pids и lock_timeout

Цель

За несколько минут превратить сообщение «база данных зависла» в конкретный блокирующий сеанс, оператор, удерживающий блокировку, и решение о дальнейших действиях.

Предварительные условия

Роль с pg_read_all_stats или суперпользователь для просмотра операторов других сеансов; log_lock_waits = on, чтобы ожидания дольше deadlock_timeout (по умолчанию одна секунда) записывались в журнал сервера с указанием обеих сторон.

Шаги

1. Найдите ожидающие процессы: SELECT pid, wait_event_type, wait_event, state, xact_start, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'.
2. Найдите тех, кто их блокирует: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. Документация рекомендует эту функцию вместо соединения pg_locks с самой собой: такой запрос должен был бы учитывать, какие режимы блокировки конфликтуют, а представление не показывает порядок очереди ожидания.
3. Найдите блокирующие сеансы в pg_stat_activity: проверьте state (idle in transaction означает, что клиент держит транзакцию открытой, занимаясь чем-то другим), xact_start и query. Последнее поле содержит последний оператор, не обязательно тот, который захватил блокировку.
4. При необходимости посмотрите, за какую блокировку идёт конкуренция: SELECT locktype, relation::regclass, mode, granted, waitstart FROM pg_locks WHERE pid IN (...). ACCESS EXCLUSIVE, которую получают большинство форм ALTER TABLE, конфликтует со всеми остальными режимами, включая обычный SELECT, поэтому DDL-оператор в очереди блокирует всех следующих читателей таблицы.
5. Решите, что делать: pg_cancel_backend(pid) останавливает текущий оператор блокирующего сеанса; pg_terminate_backend(pid) завершает сеанс и откатывает транзакцию. В обоих случаях приложение получает ошибки, поэтому запишите, чья работа была прервана и почему.
6. При взаимоблокировках читайте журнал сервера: ошибка называет оба процесса и их операторы. Документация указывает, что PostgreSQL автоматически обнаруживает взаимоблокировки и прерывает одну из транзакций; заранее неизвестно какую. Лучшая защита — захватывать блокировки нескольких объектов в согласованном порядке. Повторяйте прерванную транзакцию в приложении.
7. Предотвратите повторение: выполняйте миграции и обслуживание с SET lock_timeout = '...' и циклом повторных попыток, задайте idle_in_transaction_session_timeout для ролей приложения, а в пакетных заданиях упорядочивайте обновления нескольких строк по первичному ключу.

Ожидаемый результат

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

Ограничения и основания метода

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