Материал подготовлен в рамках курса «Высоконагруженные системы: архитектура и масштабирование».
Привет, Хабр!
У блокировок в PostgreSQL есть неприятная особенность: пока нагрузки мало, их почти не видно. Транзакции короткие, конкуренции за одни и те же строки нет, всё разбирается за миллисекунды. А потом приходит нагрузка, и оказывается, что одна медленная транзакция держит блокировку, за ней выстраивается очередь, очередь упирается в пул соединений, пул заканчивается — и база, которая по метрикам почти ничем не занята, перестаёт отвечать.
Самое проблемно тут — CPU и диск при этом свободны. База не работает, она ждёт. И если не знать, куда смотреть, диагностика превращается в гадание: запросы вроде те же, что вчера, а сегодня всё стоит.
Разберём, как находить блокировки, кто кого держит, и главное — как отличать типы проблем, потому что «всё висит» бывает по нескольким совершенно разным причинам.
Как вообще устроена блокировка
PostgreSQL берёт блокировки на разных уровнях: на таблицу, на строку, на идентификатор транзакции. Большая часть того, с чем сталкиваешься — это тяжёлые блокировки: у них есть детектор взаимоблокировок и именно они выстраивают очереди ожидания.
Ключевое, что нужно держать в голове: блокировка живёт до конца транзакции. Не до конца запроса — до конца транзакции. Транзакция взяла блокировку на строку в первом запросе, потом полминуты делала что‑то ещё, потом ходила во внешний сервис — и всё это время блокировка держится. Кто‑то, кому нужна та же строка, всё это время ждёт.
Отсюда растёт большинство проблем: блокировки сами по себе быстрые, а держат их долго из‑за того, что транзакции длинные.
Первый вопрос: кто кого блокирует
Когда база встала, первое, что надо узнать, — кто на кого ждёт. В PostgreSQL для этого есть функция, которая делает всю работу за вас:
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
pg_blocking_pids(pid) возвращает список процессов, которые держат блокировки, мешающие данному. Это самый быстрый способ увидеть картину: слева процессы, которые ждут, справа — кто им мешает. Функция покрывает все типы блокировок разом, поэтому городить сложные джойны с pg_locks для первого взгляда не нужно.
Если один и тот же PID оказался справа у многих — вот он, корень. Одна транзакция держит блокировку, за которой выстроилась очередь.
Дальше нужно понять, что это за транзакция и чем она занята:
SELECT pid, now() - xact_start AS xact_age, now() - query_start AS query_age, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE pid = <тот_самый_pid>;
И тут начинается самое интересное, потому что state этого блокирующего процесса объясняет, с какой из трёх принципиально разных проблем вы имеете дело.
Случай первый: idle in transaction
Если у блокирующего процесса state = 'idle in transaction' — это самая частая и самая обидная причина.
Означает она вот что: транзакция открыта, взяла блокировки, а сейчас ничего не делает. Не выполняет запрос, а именно висит с открытой транзакцией. Обычно за этим стоит приложение, которое сделало BEGIN, выполнило пару запросов, а потом ушло делать что‑то не в базе — вызов внешнего API, обработку в коде, а то и вовсе застряло на ошибке, не сделав ни COMMIT, ни ROLLBACK.
База при этом полностью здорова. Она не занята ничем, она ждёт, пока приложение соизволит завершить транзакцию. А оно не соизволит, потому что забыло.
Диагностический признак однозначный: блокирующий процесс idle in transaction, и время xact_age растёт, а query_age — нет, потому что запрос давно закончился. Транзакция живёт, работа не идёт.
Причина почти всегда в коде приложения. Транзакция открыта слишком широко: внутри неё оказались вещи, которым там не место, — сетевые вызовы, тяжёлые вычисления, ожидание пользователя. Лечится сужением транзакции до собственно работы с базой. Всё, что не про базу, выносится за пределы BEGIN/COMMIT.
На стороне сервера есть страховка — параметр, который автоматически прибивает слишком долго висящие в транзакции сессии:
idle_in_transaction_session_timeout = '30s'
Он не решает причину, но не даёт одной забытой транзакции подвесить базу на часы. Ставить его стоит, но с пониманием, что это предохранитель, а не лечение.
Случай второй: длинная транзакция, которая реально работает
Если у блокирующего процесса state = 'active' и запрос действительно выполняется — это другая история. Транзакция не забыта, она честно что‑то делает, просто долго. Тяжёлый отчёт, массовое обновление, миграция данных, аналитический запрос на пол‑таблицы.
Пока она работает, она держит блокировки, и всё, что конкурирует за те же данные, ждёт. Тут виновата не забывчивость, а то, что тяжёлая операция идёт в одной транзакции с тем, что нужно другим.
Классический сценарий, который валит базы: кто‑то запустил ALTER TABLE на большой таблице. DDL берёт блокировку уровня таблицы, причём в режиме, несовместимом почти со всем. И вот что происходит дальше — цепочка, которую важно понимать. Сам ALTER не может взять блокировку сразу, потому что таблицу читает какая‑то долгая транзакция.
Он встаёт в очередь. Но пока он стоит в очереди на исключительную блокировку, за ним встают уже все остальные — потому что новые запросы к таблице не могут проскочить вперёд ожидающего DDL. В итоге одна медленная читающая транзакция плюс один ALTER блокируют вообще весь доступ к таблице, хотя по отдельности ни то, ни другое базу бы не остановило.
Диагностика: смотрите на xact_age блокирующего процесса и на его запрос. Если это DDL, ждущий за длинным чтением, — вы нашли причину каскада. Корень тут не сам ALTER, а долгая транзакция впереди него, но спусковым крючком стал DDL, вставший в очередь.
Лечится по‑разному в зависимости от того, что можно трогать. Долгие операции разносят по времени с нагрузкой или выносят на реплику. DDL на больших таблицах делают с коротким таймаутом на захват блокировки, чтобы он не вставал в очередь надолго:
SET lock_timeout = '2s'; ALTER TABLE orders ADD COLUMN note text;
Если ALTER не смог взять блокировку за две секунды, он падает с ошибкой, а не встаёт в очередь, парализуя таблицу. Вы повторите его позже, а прод продолжит работать.
Случай третий: конкуренция за одни и те же строки
Третий вариант — когда ни у кого нет ни забытой, ни особо длинной транзакции, но много коротких транзакций дерутся за одни и те же строки.
Типичный пример — счётчик, который инкрементят из сотни соединений разом:
UPDATE counters SET value = value + 1 WHERE id = 1;
UPDATE берёт блокировку на строку до конца транзакции. Пока одна транзакция держит строку, остальные девяносто девять ждут своей очереди на неё же. Каждая по отдельности быстрая, но выстраиваются они в линию, и под нагрузкой эта линия становится узким местом — все пишут в одну строку по очереди.
Диагностика отличается от предыдущих: тут нет одного долгого виновника. Вместо этого много процессов ждут на wait_event_type = 'Lock', блокировки берутся и отпускаются быстро, но их много и они на одном ресурсе. Полезно посмотреть агрегированно, кто вообще ждёт:
SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE wait_event_type IS NOT NULL GROUP BY 1, 2 ORDER BY 3 DESC;
Если наверху Lock с большим счётчиком — это конкуренция за строки, а не забытая транзакция.
Лечится изменением схемы работы с горячими данными. Счётчики, которые молотят из многих соединений, разносят на несколько строк и суммируют при чтении, или выносят из транзакционной таблицы в структуру, приспособленную под частые инкременты. Общая идея — убрать точку, за которую дерутся все сразу.
Случай четвёртый: блокировки, которых не видно в pg_locks
Есть отдельный класс проблем, который не показывается обычными запросами по блокировкам, и потому сбивает с толку. Это лёгкие блокировки — LWLocks, которыми PostgreSQL защищает свои внутренние структуры в разделяемой памяти.
Симптом похож на конкуренцию за строки: много процессов чего‑то ждут, но pg_blocking_pids молчит, потому что LWLocks в него не попадают. Смотреть надо на wait_event_type в pg_stat_activity:
SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state = 'active' GROUP BY 1, 2 ORDER BY 3 DESC;
Если наверху не Lock, а LWLock с конкретным именем события — это уже не про ваши транзакции, а про внутренние узкие места движка. Самые частые виновники: WALWrite — упёрлись в запись журнала, BufferMapping — конкуренция за буферный кеш, LockManager — слишком много блокировок берётся одновременно.
Причины тут другого рода, и лечатся они не в коде запросов, а в настройке. Упор в WALWrite под нагрузкой на запись — сигнал разбираться с дисковой подсистемой и параметрами журнала. Массовый LockManager — часто следствие запросов, которые трогают тысячи партиций разом, беря блокировку на каждую.
Это более глубокий уровень, чем забытая транзакция, но знать про него полезно: когда pg_blocking_pids пуст, а всё стоит, причина обычно тут.
SELECT, который заблокировал запись
Неочевидный источник блокировок — явные блокировки в читающих запросах.
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
SELECT ... FOR UPDATE берёт на строку блокировку, как будто её обновляют, — чтобы прочитать и гарантированно потом изменить, не дав никому вклиниться. Инструмент нужный, но его часто ставят на всякий случай, а потом эта блокировка держится до конца транзакции и мешает всем, кому нужна та же строка.
Хуже, когда FOR UPDATE попадает в запрос, который возвращает много строк: он залочит их все разом. SELECT ... FOR UPDATE без LIMIT на популярной таблице — способ заблокировать половину таблицы одной транзакцией.
Диагностируется как обычная блокировка строк, но при разборе блокирующего запроса вы видите SELECT, а не UPDATE, и это сбивает — «он же просто читает». Не просто: FOR UPDATE делает из чтения блокирующую операцию. Если строгая блокировка не нужна, а нужно лишь не дать строке измениться на время чтения, есть более мягкий FOR SHARE. А часто оказывается, что блокировка не нужна вовсе и её поставили из перестраховки.
Взаимоблокировка: когда PostgreSQL сам разрывает узел
Отдельно стоит дедлок — ситуация, когда транзакция A ждёт ресурс, который держит B, а B ждёт ресурс, который держит A. Ни одна не может продолжиться.
В отличие от обычного ожидания, дедлок сам не рассосётся. PostgreSQL это понимает: у него есть детектор, который периодически ищет циклы ожидания, и найдя, убивает одну из транзакций‑участниц. Приложение получает ошибку с кодом 40P01 и должно повторить транзакцию.
Диагностируется дедлок по логам — PostgreSQL пишет туда подробности: какие процессы, какие запросы, какой ресурс. Если в логах регулярно мелькает deadlock detected, значит две части кода берут блокировки на одни и те же строки в разном порядке.
Классическая причина — именно порядок.
Транзакция A обновляет строку 1, потом строку 2.
Транзакция B — сначала 2, потом 1.
Если они пересеклись в нужный момент, каждая держит то, что нужно другой. Лечится тем, чтобы все транзакции, трогающие одни и те же строки, брали их в одном и том же порядке — например, всегда по возрастанию идентификатора. Тогда цикл не образуется в принципе.
Блокировки, которые берёт сам PostgreSQL: автовакуум и заморозка
Ещё одна причина каскада, о которой забывают, потому что виновник — не приложение, а сам движок.
Автовакуум обычно работает незаметно и берёт слабые блокировки, не мешающие обычной работе. Но есть режим, где он становится агрессивным, — заморозка для предотвращения переполнения счётчика транзакций. Когда таблица подходит к порогу, PostgreSQL запускает вакуум, который эту заморозку обязан довести до конца, и в отличие от обычного его не так просто прервать.
Симптом: на большой старой таблице внезапно появляется долго живущий процесс автовакуума, а с ним растёт блокировка, и операции, которым нужна несовместимая блокировка (тот же ALTER), встают за ним. Смотрят на это через pg_stat_activity, отфильтровав фоновые процессы:
SELECT pid, now() - xact_start AS age, wait_event, query FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
Убивать такой автовакуум — плохая идея: он всё равно запустится снова, потому что заморозку надо доделать, а откладывание только приближает более жёсткую ситуацию, когда база уйдёт в защитный режим и перестанет принимать запись. Правильнее не доводить до аврала: настроить автовакуум так, чтобы он занимался заморозкой понемногу и регулярно, а не разом на пороге.
Сюда же примыкает эффект долгих транзакций на вакуум в целом. Вакуум не может убрать мёртвые версии строк, которые ещё может увидеть самая старая открытая транзакция. Поэтому одна забытая транзакция на часы не только держит свои блокировки, но и мешает вакууму убирать мусор по всей базе — таблицы пухнут, запросы по ним замедляются. Это ещё один аргумент за то, чтобы ловить долгие транзакции рано.
Пул соединений, который превращает блокировку в отказ
Отдельно стоит сказать, почему блокировка на несколько строк роняет весь сервис, а не только те запросы, что дерутся за эти строки.
Дело в пуле соединений. У приложения ограниченное число соединений к базе — скажем, сотня. Когда транзакции начинают ждать блокировку, они не отпускают соединение: оно занято, пока транзакция висит. Десять медленных транзакций держат десять соединений.
Сотня — держат все сто. И вот уже новые запросы, которым до заблокированных строк вообще нет дела, не могут получить соединение и падают с ошибкой пула, хотя сама база свободна.
Получается двухступенчатый эффект:
блокировка держит транзакции;
транзакции держат соединения;
соединения кончаются, и падает всё приложение целиком.
Диагностически это выглядит так: в приложении ошибки про исчерпание пула соединений, а в базе при этом низкая нагрузка и куча процессов в состоянии ожидания блокировки. Расхождение «приложение задыхается, база отдыхает» — верный признак, что вы упёрлись не в базу, а в блокировки через пул.
Отсюда практическое следствие: таймаут на уровне запроса нужен не только ради самого запроса, а чтобы висящая транзакция не держала соединение вечно. statement_timeout ограничивает время выполнения и освобождает соединение, не давая одной блокировке утащить за собой весь пул:
SET statement_timeout = '10s';
Это не лечит причину блокировки, но не даёт ей перерасти из «медленно» в «всё легло».
Что включить, чтобы видеть это заранее
Ловить блокировки в момент аварии тяжело. Гораздо легче, если база сама рассказывает о них в лог.
Параметр, который стоит включить на любом нагруженном сервере:
log_lock_waits = on deadlock_timeout = '1s'
log_lock_waits пишет в лог каждый случай, когда запрос ждал блокировку дольше deadlock_timeout. Механика тут хитрая и полезная: PostgreSQL и так раз в deadlock_timeout просыпается проверить, нет ли взаимоблокировок, и заодно, если включён log_lock_waits, отмечает всё, что ждёт дольше этого порога. То есть вы получаете журнал долгих ожиданий бесплатно, на уже существующем механизме.
Дальше по этому логу видно, какие запросы регулярно ждут, на каких таблицах, как долго. Это превращает диагностику из «база встала, сидим гадаем» в «вот таблица, вот запрос, вот кто его держал».
Полезно также держать под рукой запрос, показывающий самые старые транзакции, — они первые кандидаты в блокировщики:
SELECT pid, now() - xact_start AS xact_age, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;
Если в топе висит транзакция возрастом в минуты — с неё и начинают разбираться, ещё до того, как она успеет собрать за собой очередь.
Когда надо потушить пожар прямо сейчас
Если база стоит и надо восстановить работу немедленно, а разбираться будете потом, — можно прибить блокирующий процесс. Два способа, и разница между ними важна.
Мягкий — отменить текущий запрос, не трогая соединение:
SELECT pg_cancel_backend(<pid>);
Жёсткий — оборвать всё соединение целиком:
SELECT pg_terminate_backend(<pid>);
pg_cancel_backend отменяет запрос, но если проблема в idle in transaction, он не поможет — там нет активного запроса, чтобы его отменять, транзакция просто висит. В этом случае нужен pg_terminate_backend, который закроет соединение и откатит транзакцию, освободив блокировки.
Прибивать стоит именно корневой блокирующий процесс — тот, что стоит в начале цепочки, а не тех, кто за ним ждёт. Убьёте ждущего — ничего не изменится, очередь останется. Убьёте корневого — вся очередь за ним разъедется.
Но это тушение пожара, а не решение. Если вы прибили забытую транзакцию, а приложение продолжает их плодить, через минуту всё повторится. Поэтому сразу после того, как база задышала, идут смотреть, откуда взялась долгая транзакция, и чинят уже причину.
Как к этому подходить, чтобы не гадать
Если свести всё к последовательности, диагностика блокировок выглядит так. Сначала pg_blocking_pids показывает, кто кого держит, и находит корневой процесс. Потом state этого процесса говорит, что за проблема: idle in transaction — забытая транзакция в коде, active с долгим запросом — тяжёлая операция или DDL в очереди, а много коротких ждущих на Lock — конкуренция за горячие строки.
Дальше лечение зависит от типа, но почти всегда сводится к одному: транзакции держат блокировки дольше, чем нужно. Забытые транзакции сужают, тяжёлые разносят по времени или на реплику, горячие строки размазывают, чтобы за них не дрались все разом.
И главное — не диагностировать это в момент аварии с нуля. log_lock_waits, idle_in_transaction_session_timeout, lock_timeout на DDL и запрос по самым старым транзакциям под рукой превращают внезапную остановку базы из детектива в понятную задачу: база сама показывает, кто виноват, а вам остаётся починить код, который держит транзакцию открытой.
Когда база перестаёт отвечать, проблема не всегда в высокой нагрузке на процессор или диск. Иногда достаточно одной долгой транзакции, чтобы запустить цепочку блокировок, исчерпать пул соединений и остановить всё приложение.
Проверьте, умеете ли вы находить подобные узкие места и оценивать их влияние на устойчивость системы. Пройдите входной тест по теме высоконагруженных систем, чтобы понять, насколько вы готовы проектировать системы, которые сохраняют работоспособность под нагрузкой, — и увидеть, что источник серьёзного сбоя порой скрывается там, где база просто ждёт.
А расписание бесплатных демо-уроков по архитектуре, разработке и не только смотрите в дайджесте августа.
