Блокировки в PostgreSQL: как найти причину и избежать deadlock

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

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

PostgreSQL использует MVCC*: обычные чтения, как правило, видят подходящую версию строки, а не ждут завершения каждой транзакции, которая её меняет. Но изменения, блокировки строк и некоторые операции с таблицами могут конкурировать за ресурсы. Поэтому фраза «таблица заблокирована» сама по себе мало что объясняет: важно знать режим блокировки и то, что именно ждёт запрос.

Термин. MVCC — механизм многоверсионности: база хранит версии строк, чтобы согласовать параллельное чтение и запись. Он уменьшает число конфликтов, но не отменяет блокировки.

Упрощённо полезно различать три группы:

• Блокировки таблиц. PostgreSQL берёт их автоматически для SQL-операций. Например, обычный SELECT получает ACCESS SHARE, а INSERT, UPDATE и DELETE — ROW EXCLUSIVE. Само название не означает, что все строки таблицы стали недоступны.
• Блокировки строк. Они возникают при изменении строки или явных конструкциях вроде SELECT ... FOR UPDATE. Другие транзакции могут читать старую версию строки, но конфликтующая запись или блокировка будет ждать.
• Блокировки advisory. Их приложение запрашивает явно, чтобы согласовать собственную операцию, например обработку одной задачи. PostgreSQL не проверяет, соответствует ли такой ключ реальному объекту: соглашение должны соблюдать сами участники.

Для таблиц есть восемь режимов — от ACCESS SHARE до ACCESS EXCLUSIVE. Почти каждый конфликтует только с частью остальных; ACCESS EXCLUSIVE конфликтует со всеми и, в частности, нужен для ряда операций, требующих эксклюзивного доступа. Для строк применяются отдельные режимы: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE и FOR UPDATE. Полную матрицу совместимости лучше смотреть в документации PostgreSQL 16, а не пытаться вывести её из названий.

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

Как найти запрос, который держит блокировку

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

SELECT
    blocked.pid AS blocked_pid,
    blocked.wait_event_type,
    blocked.wait_event,
    now() - blocked.query_start AS query_duration,
    blocked.query AS blocked_query,
    blocker.pid AS blocker_pid,
    blocker.state AS blocker_state,
    blocker.query AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS p(blocker_pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = p.blocker_pid
WHERE blocked.wait_event_type = 'Lock';

pg_blocking_pids показывает процессы, которые блокируют этот запрос. Поле wait_event_type = 'Lock' помогает отличить ожидание блокировки от других ожиданий. При этом query_duration — длительность текущего запроса, а не точное время ожидания блокировки. Смотрите также state, текст запроса и момент последнего изменения активности.

Если блокировщик находится в состоянии idle in transaction, он уже не выполняет запрос, но транзакцию не завершил. Это частая причина долгих ожиданий: приложение открыло транзакцию и оставило её ждать ввода, сетевого ответа или следующего шага. В pg_locks можно изучить удерживаемые и ожидаемые блокировки, но блокировки строк обычно не отображаются там как отдельная запись: ожидание может быть видно как ожидание блокировки транзакции-владельца.

Как возникает взаимоблокировка

Обычное ожидание разрешится, когда блокирующая транзакция завершится или откатится. Deadlock возникает, если сформировался цикл: транзакция A ждёт ресурс у B, а B — у A. PostgreSQL обнаруживает такой цикл и прерывает одну транзакцию. Это не то же самое, что тайм-аут ожидания: база разрывает именно цикл, а не просто отменяет любой долгий запрос.

Пример для проверки в тестовой базе PostgreSQL 16. Сначала подготовьте таблицу:

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

INSERT INTO lock_demo (id, value)
VALUES (1, 0), (2, 0);

Откройте два соединения. В первом выполните:

BEGIN;
UPDATE lock_demo SET value = value + 1 WHERE id = 1;
UPDATE lock_demo SET value = value + 1 WHERE id = 2;

До выполнения последнего запроса во втором соединении выполните:

BEGIN;
UPDATE lock_demo SET value = value + 1 WHERE id = 2;
UPDATE lock_demo SET value = value + 1 WHERE id = 1;

Первое соединение удерживает строку с id = 1 и ждёт строку id = 2; второе удерживает строку с id = 2 и ждёт первую. PostgreSQL обнаружит цикл и завершит одну из транзакций ошибкой взаимоблокировки. Вторая сможет продолжить, если повторить неуспевшую операцию уже после освобождения ресурса. Пример предназначен для теста: не запускайте его на рабочей таблице.

В PostgreSQL 16 параметр deadlock_timeout задаёт время ожидания перед проверкой на deadlock; значение по умолчанию — одна секунда. Это не максимальная продолжительность запроса и не настройка, которая устраняет причину. Уменьшение значения может повысить частоту проверок и их накладные расходы.

Что ломается в приложении и как снизить риск

Самая практичная профилактика — единый порядок захвата ресурсов. Если транзакции меняют несколько строк, пусть сначала обращаются к ним в одном и том же порядке, например по возрастанию первичного ключа. Это не гарантирует отсутствия всех deadlock: цикл может возникнуть из-за других таблиц, триггеров или advisory-блокировок. Но такой порядок устраняет распространённый конфликт, когда две операции обновляют одни и те же строки в обратной последовательности.

• Сократите транзакции. Не ждите внутри открытой транзакции ответа пользователя, стороннего API или долгой фоновой операции. Держите её открытой только на время согласованного набора действий с базой.
• Проверяйте порядок запросов. Сравните транзакции, которые меняют одинаковые записи, включая вызовы, запускаемые триггерами. Приведите порядок захвата строк к единому.
• Обрабатывайте deadlock как повторяемую ошибку. Ошибка имеет SQLSTATE 40P01. Обычно повторяют всю транзакцию, а не только последний запрос: после ошибки текущая транзакция не может продолжать обычную работу без отката.
• Повторяйте с ограничениями. Используйте небольшую задержку с разбросом и ограниченное число попыток. Повтор безопасен только если операция корректно переживает повторное выполнение; внешние действия, например отправку письма, нужно отделять от транзакции или делать идемпотентными.
• Не завершайте сессию наугад. Прежде чем отменять или завершать блокирующий процесс, проверьте владельца, транзакцию и последствия отката. В production это может сорвать полезную работу или повлиять на другие запросы.

Что измерять и где подход ломается

При расследовании соберите время ожидания, SQLSTATE, тексты запросов и параметры приложения, по которым можно связать записи логов с одной операцией. Сопоставьте их с активностью в pg_stat_activity и сообщениями сервера о deadlock. Если проблема повторяется, фиксируйте, какие таблицы и строки участвуют в цикле. Для оценки эффекта после исправления сравнивайте число ошибок 40P01, длительность ожиданий блокировок и время транзакций в одинаковых условиях нагрузки. Универсального «нормального» числа без профиля нагрузки нет.

Единый порядок блокировок не спасёт от долгой транзакции, которая держит ресурс, пока приложение ждёт сеть. Повтор транзакции не поможет, если код каждый раз создаёт тот же цикл. А lock_timeout ограничивает ожидание блокировки, но не исправляет порядок захвата ресурсов: запрос может просто завершиться ошибкой тайм-аута. Аналогично, statement_timeout ограничивает время выполнения запроса, а не служит заменой диагностике.

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