Блокировки отношений и строк

MVCC решает конфликт «читатель против писателя»: читателю достаётся старая версия строки, писатель делает новую, никто не ждёт. Но есть конфликты, которые версионностью не закрыть.

Первый - два писателя на одну строку. Если T1 и T2 одновременно делают UPDATE accounts SET balance = balance - 100 WHERE id = 5, то «последний выиграл» дал бы потерянное обновление. PostgreSQL этого не допускает: вторая транзакция ждёт, пока первая зафиксируется, и только потом перечитывает строку. Ожидание - это блокировка строки.

Второй - изменение самой таблицы. Пока кто-то читает flights, нельзя посреди чтения выполнить DROP TABLE flights или переписать её формат через ALTER TABLE. Чтение и структурное изменение должны выстроиться в очередь. Это блокировка отношения.

Так появляются два мира блокировок: тяжёлые (heavyweight) на объекты и блокировки строк. Они живут по разным правилам и хранятся в разных местах. Разберём оба.

Когда транзакция трогает таблицу, она берёт на неё блокировку одного из восьми режимов. Режим зависит от команды, а не от твоего желания: PostgreSQL выбирает минимально достаточный уровень сам.

Сверху вниз режимы «тяжелеют». ACCESS SHARE конфликтует только с ACCESS EXCLUSIVE. ACCESS EXCLUSIVE конфликтует со всеми, включая сам себя и обычный SELECT. Поэтому ALTER TABLE на горячей таблице - классический способ остановить базу: он ждёт, пока уйдут все читатели, и держит за собой всю очередь.

Подробный разбор каждого режима - в relation-locks.

Два запроса могут держать блокировки на одну таблицу одновременно, если их режимы не конфликтуют. SELECT и UPDATE уживаются: ACCESS SHARE и ROW EXCLUSIVE совместимы. Два UPDATE на разные строки тоже уживаются - конфликт будет уже на уровне строк, не таблицы.

Простое правило вместо заучивания матрицы 8×8: режимы со словом EXCLUSIVE конфликтуют с записью и друг с другом; ACCESS EXCLUSIVE конфликтует вообще со всеми. Всё, что не EXCLUSIVE, между собой совместимо.

-- Сессия A

BEGIN;

SELECT * FROM flights LIMIT 1;   -- ACCESS SHARE, держится до COMMIT

-- Сессия B (пока A не закоммитила)

ALTER TABLE flights ADD COLUMN note text;  -- хочет ACCESS EXCLUSIVE

-- → ВИСНЕТ: ждёт, пока A отпустит ACCESS SHARE

Команда B не выполнится, пока A не сделает COMMIT или ROLLBACK. Блокировка отношения держится до конца транзакции, а не до конца запроса - это ключ к понимаю каскадов.

pg_locks - это сама таблица блокировок из разделяемой памяти, показанная как view. Одна строка - одна запрошенная блокировка. Колонка granted говорит, выдана она (t) или транзакция ждёт в очереди (f).

SELECT locktype, relation::regclass, mode, granted, pid

FROM pg_locks

WHERE relation = 'flights'::regclass

ORDER BY granted DESC;

Если в выводе есть строка с granted = f, кто-то стоит в очереди. Чтобы понять, кого он ждёт, не нужно глазами сводить таблицу - есть функция:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query

FROM pg_stat_activity

WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids(pid) возвращает массив PID, которые держат блокировки, мешающие этому процессу. Это самый короткий путь от «всё висит» к «вот этот бэкенд виноват».

Частая ошибка - думать, что блокировка снимается, когда запрос отработал. Нет: тяжёлая блокировка живёт до конца транзакции.

Сессия выполнила SELECT, получила результат, и человек ушёл пить кофе, не закрыв транзакцию (BEGIN без COMMIT). Запрос давно закончился, но ACCESS SHARE на таблице всё ещё держится. Для psql признак такого состояния - приглашение меняется с lab=# на lab=*#: звёздочка значит «открыта транзакция».

Отсюда правило: смотри не на последний запрос блокировщика, а на его state в pg_stat_activity. Если там idle in transaction - блокировку держит «забытая» открытая транзакция, и сам по себе он её не отпустит.

Когда UPDATE или DELETE трогает строку, он блокирует именно её, а не всю таблицу (на таблице при этом висит лёгкий ROW EXCLUSIVE). Явно строки блокируют через SELECT ... FOR:

Зачем четыре, а не один? Из-за внешних ключей. Когда ты вставляешь строку в tickets, PostgreSQL должен убедиться, что родительская bookings никуда не денется - но менять её данные ты не собираешься. Поэтому он берёт слабый FOR KEY SHARE: он пропускает параллельный UPDATE неключевых колонок родителя и блокирует только удаление или смену ключа. До PostgreSQL 9.3 тут был полноценный конфликт, и вставки в дочернюю таблицу сериализовались на пустом месте.

Подробности по режимам строк - в row-locks.

Блокировок строк нет в pg_locks как отдельных записей - иначе на миллионе обновлённых строк таблица блокировок взорвалась бы. PostgreSQL хранит факт блокировки прямо в самой строке: в поле xmax заголовка кортежа и в битах t_infomask. Заблокированная строка - это строка, чей xmax равен номеру держащей транзакции, но при этом строка не удалена (об этом говорят hint-биты).

Поэтому блокировка строки не стоит ничего, пока строк мало под замком: она часть данных, а не отдельный объект в памяти. В pg_locks ты увидишь только косвенный след: ждущая транзакция висит на locktype = 'transactionid', ожидая завершения той, чей xmax стоит на строке.

-- T1: блокирует строку, не отпускает

BEGIN;

SELECT * FROM bookings WHERE book_ref = '0000a1' FOR UPDATE;

-- T2: хочет ту же строку → встаёт в очередь

BEGIN;

SELECT * FROM bookings WHERE book_ref = '0000a1' FOR UPDATE;  -- виснет

-- Из третьей сессии видно, на что ждёт T2:

SELECT pid, wait_event_type, wait_event, state

FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Если одну строку хотят заблокировать сразу несколько транзакций в разделяемом режиме (FOR SHARE), одного xmax не хватит - в нём помещается только один номер. Тогда PostgreSQL заводит multixact: xmax указывает не на транзакцию, а на запись в pg_multixact, где перечислены все участники и их режимы.

Это та самая структура, чей возраст отслеживает relminmxid и которую тоже нужно замораживать, иначе - переполнение по той же логике, что и обычный wraparound. Multixact не виден в обычной работе, но всплывает в логах vacuum и в редких ошибках «multixact members limit exceeded» под тяжёлой нагрузкой на FOR SHARE.

На время самого захвата строки в pg_locks мелькает запись locktype = 'tuple' - короткоживущая блокировка на конкретный кортеж. Она нужна, чтобы упорядочить претендентов, и снимается сразу после того, как право на строку определено.

Теперь соберём механизм, который кладёт прод. Возьмём три сессии.

-- A: открыл транзакцию, что-то прочитал и «забыл» закрыть

BEGIN;

SELECT count(*) FROM flights;   -- держит ACCESS SHARE, state = idle in transaction

-- B: миграция выкатывается ровно сейчас

ALTER TABLE flights ADD COLUMN gate text;  -- хочет ACCESS EXCLUSIVE → встаёт за A

-- C: обычный пользовательский запрос

SELECT * FROM flights WHERE flight_no = 'PG1';  -- хочет ACCESS SHARE → встаёт за B

Казалось бы, C совместим с A - два читателя. Но между ними в очереди стоит B с ACCESS EXCLUSIVE, а очередь блокировок честная (FIFO): новые запросы не могут «перепрыгнуть» ждущего. Поэтому безобидный SELECT из C тоже виснет. Один забытый BEGIN в A плюс одна миграция в B - и встала вся таблица.

Вывод неочевидный, но важный: опасен не ALTER TABLE сам по себе, а его сочетание с долгой открытой транзакцией впереди. Разбор сценария целиком - в idle-in-transaction-cascade.

Диагностика всегда идёт от ждущего к держащему. Находим, кто стоит в очереди, спрашиваем pg_blocking_pids, смотрим, что за процесс виноват.

SELECT

  w.pid          AS waiting_pid,

  w.query        AS waiting_query,

  b.pid          AS blocking_pid,

  b.state        AS blocking_state,

  b.query        AS blocking_query

FROM pg_stat_activity w

JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid) ON true

JOIN pg_stat_activity b ON b.pid = bp.pid

WHERE cardinality(pg_blocking_pids(w.pid)) > 0;

Если виновник - idle in transaction, у тебя два инструмента:

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

SELECT pg_terminate_backend(<pid>);  -- закрыть сессию целиком (жёстко)

pg_cancel_backend шлёт сигнал отмены запроса; для idle in transaction он часто бесполезен - там нет активного запроса, есть открытая транзакция. Тогда нужен pg_terminate_backend: он обрывает соединение, транзакция откатывается, блокировки спадают, очередь рассасывается. На проде от такого спасает настройка idle_in_transaction_session_timeout, которая прибивает забытые транзакции автоматически.