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

MAATRIX

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

Зачем нужны блокировки строк и таблиц

Блокировка (lock) — механизм, который не даёт двум транзакциям одновременно менять одну и ту же строку так, чтобы получился нечитаемый результат. Без блокировок при параллельной работе с одной строкой можно получить classic lost update: обе транзакции читают значение 100, обе прибавляют по 10, обе записывают 110 — хотя правильный результат 120.

Row-level locking решает это на уровне строки, а не всей таблицы: когда транзакция делает UPDATE или DELETE, СУБД ставит на строку эксклюзивную блокировку до конца транзакции (COMMIT или ROLLBACK). Другая транзакция, пытающаяся изменить ту же строку, встаёт в очередь и ждёт освобождения. Это не баг и не тормоза, а ровно то, ради чего блокировки существуют — гарантия, что данные не испортятся при параллельном доступе.

Важный нюанс: блокировка держится не до конца SQL-запроса, а до конца транзакции. Если вы открыли транзакцию, сделали UPDATE, а дальше приложение зависло на сетевом вызове или ждёт ответа от внешнего API — блокировка всё это время остаётся висеть. Именно отсюда растут большинство практических проблем с блокировками, о которых дальше пойдёт речь.

В PostgreSQL и MySQL/InnoDB есть разные уровни блокировок:

• Row-level (строчные) — самые частые, ставятся на конкретную строку при UPDATE/DELETE/SELECT ... FOR UPDATE.
• Table-level (табличные) — ставятся на всю таблицу, например при ALTER TABLE, TRUNCATE, LOCK TABLE. Гораздо жёстче: под ними встают все операции с таблицей.
• Advisory locks — в PostgreSQL приложение ставит их вручную через pg_advisory_lock(), не привязывая к конкретным строкам — удобно для распределённых блокировок между воркерами.

Уровень изоляции транзакций (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) влияет на то, какие блокировки ставятся и как долго. По умолчанию в PostgreSQL и MySQL используется READ COMMITTED — блокировки строк снимаются сразу при коммите, но не защищают от некоторых аномалий вроде phantom read. Более строгая гарантия на REPEATABLE READ или SERIALIZABLE обходится бóльшим числом конфликтов блокировок.

Deadlock: классический сценарий по кругу

Deadlock (взаимная блокировка) — ситуация, когда две транзакции ждут друг друга по кругу и никогда не дождутся сами. Классический пример:

-- Транзакция A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ждём немного (например, обращение к внешнему сервису)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Транзакция B, выполняется почти одновременно
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- транзакция A уже держит блокировку на id=2 → B встаёт в очередь
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- а транзакция A теперь хочет заблокировать id=1, который держит B
COMMIT;

Транзакция A заблокировала строку id=1 и ждёт строку id=2. Транзакция B заблокировала id=2 и ждёт id=1. Круг замкнулся — обе ждут друг друга бесконечно, и без вмешательства это не разрешится само.

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

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 987654; blocked by process 12346.

В MySQL — ошибка ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. Это нормальная, ожидаемая ситуация в высоконагруженной системе: приложение должно быть готово ловить такую ошибку и повторять транзакцию (retry), а не падать целиком.

Самый частый источник deadlock на практике — разный порядок изменения строк в разных частях кода. Если в одном месте вы обновляете сначала orders, потом inventory, а в другом — наоборот, рано или поздно при параллельных запросах получите deadlock. Лечится простым правилом: всегда изменять таблицы и строки в одном и том же порядке во всём приложении, например по возрастанию первичного ключа.

Забытый commit: как одна зависшая транзакция кладёт всех

Deadlock СУБД разрешает сама за доли секунды. Гораздо неприятнее другая ситуация: одна транзакция открылась, сделала UPDATE, а потом — из-за бага, забытого commit(), исключения без rollback() в блоке try/except, или долгого запроса к внешнему API внутри транзакции — не закрылась вовсе. Она висит открытой в состоянии idle in transaction (PostgreSQL) или просто активной (MySQL) и всё это время держит блокировки на изменённых строках.

Дальше — цепная реакция. Любой другой запрос к той же строке (например, счётчик заказов или запись пользователя) встаёт в очередь ожидания. Через несколько секунд скапливается пул зависших соединений, приложение упирается в лимит max_connections, и снаружи это выглядит как «сайт лёг», хотя на деле — одна забытая транзакция где-то в фоновой задаче.

Типичные причины:

• В коде получили exception, но забыли обернуть в finally: connection.rollback().
• ORM (Django, SQLAlchemy, Hibernate) держит транзакцию открытой на всё время HTTP-запроса, а внутри вызывается медленный внешний API — пока он отвечает, блокировка висит.
• Ручная сессия в psql/mysql через SSH, где кто-то сделал BEGIN; UPDATE ... и отвлёкся, забыв про открытую транзакцию.

Профилактика на уровне СУБД — таймауты, которые сами обрывают зависшие транзакции:

-- PostgreSQL: убивать транзакции, простаивающие в idle in transaction дольше 5 минут
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

# MySQL/InnoDB: сколько секунд ждать блокировку до отката запроса
[mysqld]
innodb_lock_wait_timeout = 50

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

PostgreSQL: смотрим, кто кого блокирует

В PostgreSQL есть системное представление pg_locks (текущие блокировки) и pg_stat_activity (активные сессии с запросами). По отдельности pg_locks малочитаем — там просто список объектов и режимов блокировки без понятного «кто кого ждёт». Но есть готовый запрос, который сводит их вместе:

SELECT
    bl.pid AS blocked_pid, ba.usename AS blocked_user, ba.query AS blocked_query,
    kl.pid AS blocking_pid, ka.usename AS blocking_user, ka.query AS blocking_query,
    now() - ba.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity ba ON ba.pid = bl.pid
JOIN pg_catalog.pg_locks kl
    ON kl.locktype = bl.locktype
    AND kl.database IS NOT DISTINCT FROM bl.database
    AND kl.relation IS NOT DISTINCT FROM bl.relation
    AND kl.page IS NOT DISTINCT FROM bl.page
    AND kl.tuple IS NOT DISTINCT FROM bl.tuple
    AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

Результат показывает пару PID: кто заблокирован (blocked_pid), кто виновник (blocking_pid), и оба текущих запроса — обычно этого достаточно, чтобы понять, что происходит. Есть и короткий путь — встроенная функция pg_blocking_pids(pid), возвращающая массив PID тех, кто блокирует заданный процесс, без ручного JOIN'а.

Когда виновник найден и понятно, что это зависшая транзакция, а не активно работающий запрос — её можно завершить:

-- Мягко: отменить текущий запрос, транзакция остаётся открытой
SELECT pg_cancel_backend(12346);

-- Жёстко: полностью оборвать соединение и откатить транзакцию
SELECT pg_terminate_backend(12346);

Полезно также искать долгие idle in transaction напрямую — это кандидаты на «забытый commit»:

SELECT pid, usename, state, now() - xact_start AS tx_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY tx_age DESC;

Если tx_age — минуты или часы при обычной операции в пару миллисекунд, это почти наверняка проблема в коде приложения, а не в базе.

MySQL: SHOW ENGINE INNODB STATUS и информационные схемы

В MySQL с движком InnoDB классический инструмент — команда:

SHOW ENGINE INNODB STATUS\G

Она выводит большой текстовый отчёт с секцией TRANSACTIONS (список активных транзакций) и, если есть конфликт, секцией LATEST DETECTED DEADLOCK — там текстом описаны обе транзакции-участницы, какие блокировки они держали и какую пытались получить, и какая была отменена (WE ROLL BACK TRANSACTION). Это самый подробный источник по deadlock'ам, но читать его приходится руками — структурированного вывода тут нет.

Удобнее для запросов — представления information_schema (MySQL 5.7 и старше) или performance_schema (MySQL 8.0+, где старые INNODB_LOCKS/INNODB_LOCK_WAITS убраны):

-- MySQL 8.0+: активные транзакции
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- MySQL 8.0+: кто кого блокирует (через performance_schema)
SELECT r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query,
       b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

Чтобы performance_schema.data_lock_waits вообще заполнялась, нужные инструменты сбора должны быть включены (по умолчанию в современных версиях MySQL — да, но на урезанных сборках их иногда отключают вручную).

Завершить зависшую сессию можно командой KILL по идентификатору потока из trx_mysql_thread_id или SHOW PROCESSLIST:

SHOW PROCESSLIST;
KILL 987654;  -- прерывает запрос и/или соединение с этим id

KILL без уточнения обрывает саму сессию (аналог pg_terminate_backend); KILL QUERY прерывает только текущий запрос, оставляя соединение и транзакцию открытыми — аналог pg_cancel_backend.

Как разрулить: таймауты и принудительное завершение

Кроме разового «найти и убить», у обеих СУБД есть настройки, не дающие одной проблемной транзакции держать систему бесконечно:

Разумные значения зависят от профиля нагрузки: lock_timeout в 5-10 секунд и idle_in_transaction_session_timeout в 1-5 минут — рабочая отправная точка для веб-приложения с короткими транзакциями, но цифры стоит подбирать по факту, а не копировать вслепую.

Стоит настроить и мониторинг, чтобы не искать проблему руками, когда всё уже встало: регулярный запрос вида «есть ли транзакция старше 2 минут в idle in transaction» с алертом, или Grafana поверх метрик из pg_stat_activity/information_schema.innodb_trx — подробнее в статье про мониторинг баз данных через Grafana.

Как снизить частоту блокировок в приложении

Находить и лечить симптомы — половина дела, вторая — не доводить до конфликтов на уровне архитектуры:

• Короткие транзакции. Не делайте сетевых вызовов (к внешним API, очередям, кэшам) внутри открытой транзакции с базой. Сначала соберите данные, потом откройте транзакцию, сделайте минимум операций и сразу закоммитьте.
• Единый порядок блокировки. Если транзакция трогает несколько строк/таблиц, всегда делайте это в одном порядке во всех местах кода — это устраняет саму возможность кругового ожидания.
• SELECT ... FOR UPDATE вместо блокировки на уровне приложения. Если логике нужно «зарезервировать» строку, явный FOR UPDATE внутри транзакции честнее и предсказуемее, чем блокировки через Redis или флаг в отдельной таблице.
• Retry с backoff на deadlock и timeout. Приложение должно ловить 40001/1213 (deadlock) и 55P03/1205 (lock timeout), делать небольшую случайную паузу и повторять транзакцию — это штатное поведение, а не признак поломки.
• Индексы на внешние ключи. В MySQL/InnoDB блокировка при foreign key check без индекса на связанном столбце может расшириться на соседние строки (gap locks) — частая скрытая причина «необъяснимых» блокировок.
• Пул соединений с разумным лимитом. Если приложение открывает транзакций больше, чем нужно, очередь ожидания растёт быстрее, чем кажется — настройка connection pooling снижает и число открытых транзакций, и хаотичность их порядка.

На отдельном сервере под базу данных все описанные запросы и настройки применимы без изменений. Для нагруженной базы разумно держать её на отдельной машине с предсказуемым CPU/IO, а не делить с веб-сервером.

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