База данных встала на ровном месте: разбор блокировки

MAATRIX

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

Первая версия: не хватает ресурсов

Когда приложение «встаёт», первый рефлекс — открыть top или htop и посмотреть на CPU и память. Логика понятная: если сервис не отвечает, значит, он что-то усиленно считает или упёрся в своп. Но в сценарии с блокировкой картина обманчива.

top -o %CPU

Вы видите примерно следующее: процесс postgres или mysqld занимает единицы процентов CPU, память в норме, свопа нет, диск не пишет на пределе (iostat -x 1 показывает низкий %util). База данных не работает на износ — она ждёт. Это ключевое отличие от инцидента с реальной нехваткой ресурсов, где вы бы увидели забитую очередь на диск или CPU в потолке у самого процесса СУБД.

Похожая ловушка разбирается в статье про высокую нагрузку на процессор — там CPU действительно виноват, здесь же он ни при чём. Второй частый ложный след — нехватка памяти и OOM: если процесс СУБД жив, в dmesg нет записей об OOM killer, а память в free -h не исчерпана, эта версия тоже отпадает.

Ещё один тест, который экономит время: посмотрите на число активных соединений и на то, сколько из них реально что-то делают.

-- PostgreSQL
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Если большинство соединений в состоянии active, но при этом суммарный CPU низкий — это почти наверняка ожидание блокировки, а не вычислительная нагрузка. Если видите десятки соединений в idle in transaction — это отдельный и очень характерный симптом, к которому мы вернёмся ниже.

Настоящая причина: смотрим на блокировки

Когда ресурсы ни при чём, следующий шаг — посмотреть, что конкретно делают активные запросы и кто кого ждёт. В PostgreSQL это pg_stat_activity и представление pg_locks, в MySQL/MariaDB — SHOW PROCESSLIST и таблицы information_schema (или performance_schema в новых версиях).

PostgreSQL: находим долгие запросы

SELECT pid, now() - query_start AS duration, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 20;

Обратите внимание на колонку wait_event_type — если там Lock, запрос не выполняется, а стоит в очереди на блокировку. Дальше нужно понять, кто именно держит эту блокировку:

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.query AS blocked_query,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.query AS blocking_query,
       blocking_activity.state AS blocking_state,
       now() - blocking_activity.query_start AS blocking_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Этот запрос — рабочая лошадка любого разбора блокировок в PostgreSQL: он показывает пары «кто ждёт — кого ждёт». Типичная картина инцидента: один blocking_pid держит блокировку минутами (точная цифра зависит от вашего случая), а к нему выстроилась очередь из blocked_pid, каждый из которых, в свою очередь, блокирует следующих. Один зависший процесс каскадно останавливает всё приложение.

MySQL/MariaDB: тот же принцип

SHOW PROCESSLIST;
-- или подробнее, с временем в состоянии
SELECT id, user, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

Для поиска именно блокировок в InnoDB:

SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ASC;
SELECT * FROM sys.innodb_lock_waits;  -- если подключена схема sys

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

Признак, который почти всегда указывает на первопричину: состояние idle in transaction в PostgreSQL или Sleep внутри активной транзакции в MySQL. Это означает, что транзакция открыта (BEGIN выполнен), но следующая команда так и не пришла — соединение просто держит блокировки и ничего не делает.

Откуда берётся зависшая транзакция

Найти виновника — половина дела, но полезно понимать, как он вообще там оказался, чтобы не наступать на те же грабли повторно. На практике встречаются три сценария, и все три — не про баг в самой СУБД, а про то, как её использует приложение или человек.

1. Забытый BEGIN без COMMIT в ручной сессии. Администратор или разработчик открыл psql или mysql, начал транзакцию для проверки чего-то («сейчас гляну, а потом откачу»), отвлёкся на созвон — и забыл про сессию. Транзакция висит открытой часами, держа блокировки на строках, которые она успела тронуть. Находится она моментально по query_start в pg_stat_activity: если state = idle in transaction, а последний query был BEGIN или SELECT ... FOR UPDATE, — это она.

2. Долгий batch-джоб, который держит блокировку дольше, чем рассчитывали. Ночной скрипт пересчёта отчётов, миграция данных или массовое обновление в цикле — если такая задача обновляет строки внутри одной большой транзакции без промежуточных COMMIT, она держит блокировку на всё время своей работы. Если джоб рассчитан на минуты, а из-за выросшего объёма данных стал работать заметно дольше — он превращается в блокировщика для всех остальных операций с теми же таблицами, особенно если это «горячие» таблицы — пользователей, заказов, сессий.

3. Приложение не закрыло транзакцию из-за необработанного исключения. Самый частый случай в проде. Код открывает транзакцию, что-то идёт не так (сетевой сбой, исключение в бизнес-логике, таймаут внешнего вызова внутри транзакции), а try/except вокруг работы с базой не содержит finally с явным rollback(). Соединение возвращается в пул «как есть» — с незакрытой транзакцией, и блокировки на стороне БД остаются, пока не истечёт таймаут пула или сессия не оборвётся сама. Особенно часто это встречается там, где ORM автоматически оборачивает операции в транзакцию, а разработчик не всегда явно видит, где она открывается и закрывается.

Во всех трёх случаях объединяющий признак один: транзакция открыта, но не завершена явным COMMIT или ROLLBACK, и именно незавершённость (а не длительность запроса как такового) держит блокировки.

Экстренное решение: убить блокировщика

Когда виновник найден — по pid в PostgreSQL или id в MySQL — экстренная мера одна: принудительно прервать эту сессию.

PostgreSQL:

-- мягкий вариант: попросить отменить текущий запрос
SELECT pg_cancel_backend(12345);

-- жёсткий вариант: разорвать соединение целиком (откатывает транзакцию)
SELECT pg_terminate_backend(12345);

pg_cancel_backend работает, только если сессия выполняет активный запрос — на «зависшую» idle in transaction сессию он не подействует, потому что там прямо сейчас ничего не выполняется. В этом случае нужен pg_terminate_backend — он закрывает соединение принудительно, СУБД откатывает незавершённую транзакцию и снимает все её блокировки.

MySQL/MariaDB:

KILL 987654;          -- прерывает соединение и транзакцию
KILL QUERY 987654;    -- прерывает только текущий запрос, соединение остаётся

Здесь по аналогии: если процесс висит на пустом месте без активного запроса, KILL QUERY ничего не даст, нужен полный KILL.

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

• Если это была транзакция администратора с забытым BEGIN — откатятся все изменения внутри неё, включая то, что человек, возможно, хотел сохранить.
• Если это был batch-джоб — он прервётся на середине. Нужно понимать, идемпотентен ли он (можно ли просто перезапустить) или потребует ручной проверки, что именно успело примениться.
• Если это было приложение — клиент, который инициировал запрос, скорее всего уже получил таймаут на своей стороне и, возможно, уже показал пользователю ошибку. Само по себе прерывание не «чинит» баг в обработке исключений — оно снимает симптом сейчас, но проблему в коде всё равно нужно чинить отдельно.

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

Таймауты транзакций как профилактика

Самая эффективная защита от повторения — не позволять транзакции висеть бесконечно. И PostgreSQL, и MySQL умеют автоматически обрывать сессии, которые превысили разумное время простоя или выполнения.

PostgreSQL:

# postgresql.conf — глобально для всей базы
idle_in_transaction_session_timeout = '5min'   # закрыть транзакцию, простаивающую без команд
statement_timeout = '30s'                       # прервать сам запрос, если выполняется слишком долго
lock_timeout = '10s'                             # не ждать блокировку дольше N секунд, вернуть ошибку

Эти же параметры можно задать точечно — на уровне роли или конкретной сессии, не трогая глобальный конфиг:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE batch_job_user SET statement_timeout = '15min';

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

MySQL/MariaDB:

SET GLOBAL innodb_lock_wait_timeout = 50;   -- секунд ожидания блокировки на строке (по умолчанию 50)
SET GLOBAL wait_timeout = 600;              -- закрыть неактивное соединение через N секунд

У MySQL нет прямого аналога idle_in_transaction_session_timeout в чистом виде — ближайший рабочий инструмент это wait_timeout/interactive_timeout для неактивных соединений плюс дисциплина на уровне пула соединений (описанного ниже).

Отдельная линия защиты — на уровне приложения: явный try/finally (или with в Python, defer в Go, using в C#) вокруг блока с транзакцией, гарантированно вызывающий rollback() при любом исключении. Также стоит настроить таймаут для самого пула соединений (max_lifetime, idle_timeout в HikariCP, pgbouncer и аналогах) — дополнительная страховка, если таймауты СУБД по какой-то причине не сработали.

Мониторинг долгих транзакций заранее

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

Проверочный запрос для cron (PostgreSQL):

#!/bin/bash
psql -U monitor_user -d appdb -t -c "
  SELECT count(*) FROM pg_stat_activity
  WHERE state = 'idle in transaction'
  AND now() - state_change > interval '2 minutes';
" | tr -d ' '

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

Для более наглядной картины на постоянной основе подойдёт мониторинг баз данных через Grafana — экспортер postgres_exporter или mysqld_exporter отдаёт метрики по числу активных транзакций, их возрасту и состоянию блокировок, а на дашборде можно настроить алерт именно на «есть транзакция старше N минут» — это даёт возможность вмешаться до того, как пользователи заметят зависание.

Что стоит держать под наблюдением постоянно:

• число сессий в состоянии idle in transaction / транзакций InnoDB старше нескольких минут;
• максимальный возраст самой старой активной транзакции;
• количество процессов, ожидающих блокировку (wait_event_type = 'Lock' в PostgreSQL, Waiting for table lock и подобные состояния в MySQL);
• рост очереди активных соединений при неизменном количестве запросов в секунду — характерный признак того, что кто-то встал в затор.

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

Частые вопросы