Lock conflict matrix: что с чем совместимо

В предыдущем уроке мы посмотрели восемь уровней блокировок и узнали, какая команда какой уровень берёт. Само по себе это знание ничего не даёт — главный вопрос: какие уровни конфликтуют между собой? Если у меня уже взят ROW EXCLUSIVE на таблице, может ли вторая сессия одновременно взять ROW EXCLUSIVE? SHARE? ACCESS EXCLUSIVE?

Ответ задаётся матрицей 8×8 — её надо запомнить (или хотя бы знать главные клетки наизусть).

Матрица совместимости

Lock conflict matrix (X = конфликт, . = совместимо)

Столбцы — что уже взято, строки — что мы пытаемся взять. ACCESS SHARE конфликтует только с ACCESS EXCLUSIVE. ROW EXCLUSIVE совместим сам с собой (две UPDATE-сессии параллельно). ACCESS EXCLUSIVE несовместим вообще ни с чем.

новый \\ взятыйAS RS RE SUE S SRE E AE

1. ACCESS SHARE (AS). . . . . . . X

2. ROW SHARE (RS). . . . . . X X

3. ROW EXCLUSIVE (RE). . . . X X X X

4. SHARE UPDATE EXCL (SUE). . . X X X X X

5. SHARE (S). . X X . X X X

6. SHARE ROW EXCL (SRE). . X X X X X X

7. EXCLUSIVE (E). X X X X X X X

8. ACCESS EXCLUSIVE (AE)X X X X X X X X

Главные клетки, которые надо знать наизусть:

ACCESS SHARE ↔ ACCESS EXCLUSIVE — единственный конфликт самого лёгкого уровня. Любой SELECT блокирует DROP TABLE и наоборот. Всё остальное (INSERT, UPDATE, CREATE INDEX, VACUUM) — параллельно с SELECT.
ROW EXCLUSIVE ↔ ROW EXCLUSIVE — не конфликтуют. Две UPDATE-сессии работают параллельно на table-level. Если они трогают одну и ту же строку — конфликт перейдёт на row-level через xmax.
ROW EXCLUSIVE ↔ SHARE — конфликтуют. Поэтому CREATE INDEX (без CONCURRENTLY) встаёт в очередь за всеми пишущими и сам блокирует новых.
SHARE UPDATE EXCLUSIVE ↔ SHARE UPDATE EXCLUSIVE — конфликтуют. Это значит, что нельзя запустить два VACUUM параллельно на одну таблицу, нельзя одновременно ANALYZE и VACUUM, нельзя два CREATE INDEX CONCURRENTLY. Постгрес сериализует их.
ACCESS EXCLUSIVE — конфликтует со всем, включая ACCESS SHARE. Это полная блокировка таблицы.

Правило большого пальца

В большинстве OLTP-сценариев тебя интересуют только три комбинации:

OLTP-сценарии: что блокирует что

SELECT не блокирует ничего кроме DDL. INSERT/UPDATE/DELETE параллельны между собой, не блокируют чтение. CREATE INDEX CONCURRENTLY = безопасная альтернатива.

SELECT vs SELECTпараллельно (AS + AS совместимы)

SELECT vs INSERT/UPDATE/DELETEпараллельно (AS + RE совместимы)

INSERT vs INSERTпараллельно (RE + RE)

UPDATE vs UPDATE одной строкипоследовательно (через xmax, row-level)

SELECT vs ALTER TABLEпоследовательно (AS vs AE — конфликт!)

VACUUM vs ANALYZEпоследовательно (SUE vs SUE — конфликт)

VACUUM vs INSERTпараллельно (SUE vs RE — совместимы)

CREATE INDEX CONCURRENTLY vs INSERTпараллельно (SUE vs RE)

Очередь блокировок и эффект «FIFO загораживается»

Самый коварный эффект, про который не пишут в туториалах — это очередь блокировок. Когда транзакция не может взять lock, она встаёт в очередь. Очередь обслуживается FIFO: новый запрос на lock не может «перепрыгнуть» через уже стоящего в очереди, даже если они совместимы.

Классический сценарий миграции в проде:

ACCESS EXCLUSIVE загораживает очередь

t0: SELECT (T1) держит ACCESS SHARE, работает медленно. t1: ALTER TABLE (T2) приходит, хочет ACCESS EXCLUSIVE — встаёт в очередь. t2: новый SELECT (T3) приходит, хочет ACCESS SHARE — он совместим с T1, но НЕ может перепрыгнуть через T2 в очереди. Результат — все новые SELECT встают, прод лежит.

t0: SELECT долгийT1 держит ACCESS SHARE

t1: ALTER TABLE пришёлT2 в очереди ACCESS EXCLUSIVE

t2: новый SELECTT3 в очереди ACCESS SHARE (за T2!)

итогT3 теоретически совместим с T1, но FIFO не пропускает — T3 ждёт T2, T2 ждёт T1. Прод OLTP лежит до COMMIT T1.

Это главная причина того, что миграция в проде неожиданно валит всю базу. Сама миграция занимает 5 ms, но за время ожидания одной случайной транзакции с долгим SELECT весь OLTP встаёт в очередь за ней.

Защита: lock_timeout + retry

Стандартная практика безопасной миграции в Postgres:

SET lock_timeout = '2s';
SET statement_timeout = '5s';

ALTER TABLE orders ADD COLUMN refunded_at TIMESTAMPTZ;

Если за 2 секунды не удалось получить ACCESS EXCLUSIVE — миграция отвалится с ошибкой canceling statement due to lock timeout, очередь рассосётся, и можно попробовать снова через несколько секунд. Без lock_timeout миграция может стоять часами, держа всю базу.

Дополнительные приёмы:

В Postgres 11+ ADD COLUMN <type> DEFAULT <const> не переписывает heap — это просто запись в pg_attribute. Только короткий ACCESS EXCLUSIVE на момент записи метаданных.
ADD COLUMN ... NOT NULL DEFAULT <const> — тоже моментально (Postgres 11+).
ALTER TABLE ... DROP CONSTRAINT — не требует переписывания heap.
А вот ADD CONSTRAINT NOT NULL (без default) или ALTER COLUMN ... TYPE ... требуют полного сканирования таблицы под ACCESS EXCLUSIVE — это часами на больших таблицах. Используй вариант ADD CONSTRAINT ... NOT VALID; VALIDATE CONSTRAINT; — первая часть моментальна (ACCESS EXCLUSIVE только на метаданные), вторая часть берёт более лёгкий SHARE UPDATE EXCLUSIVE.

Смотрим конфликт в pg_locks

Посмотрим, как блокировка выглядит изнутри. Запустим транзакцию, которая держит ROW EXCLUSIVE, и в той же сессии попробуем LOCK TABLE ... IN ACCESS EXCLUSIVE MODE NOWAIT — это покажет, что конфликт есть:

Берём ROW EXCLUSIVE, потом пытаемся взять ACCESS EXCLUSIVE сами на ту же таблицу — это разрешено (внутри одной транзакции lock 'апгрейдится' без конфликта, потому что Postgres различает локи по transaction id, а не по pid).

PostgreSQL

Видишь — мы держим и RowExclusiveLock, и AccessExclusiveLock одновременно. В рамках одной транзакции LOCK TABLE поверх своего же UPDATE не конфликтует. Конфликт возникает только между разными транзакциями.

LOCK TABLE — отдельная команда, явно берущая указанный уровень. Используется редко — нужно либо для очень специальных миграций, либо для тестирования. В обычной OLTP-нагрузке руками LOCK TABLE не пишут.

Smart trick: SET LOCAL lock_timeout

Хорошая привычка для миграций:

SET LOCAL действует только до конца транзакции. После ROLLBACK/COMMIT параметр вернётся к глобальному значению. Используем для безопасных миграций.

PostgreSQL

Если бы в этой таблице кто-то держал ACCESS SHARE дольше 500 ms — миграция бы отвалилась с ошибкой canceling statement due to lock timeout, не положив остальную нагрузку.

Чек-лист

Lock conflict matrix 8×8 — нужно знать главные клетки наизусть.
ACCESS SHARE совместим со всем, кроме ACCESS EXCLUSIVE. Это значит, что любой SELECT блокируется только DDL.
ROW EXCLUSIVE (INSERT/UPDATE/DELETE) совместим сам с собой на table-level. Конфликты идут на row-level через xmax.
SHARE UPDATE EXCLUSIVE (VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY) совместим с ROW EXCLUSIVE (можно вакуумить под нагрузкой).
ACCESS EXCLUSIVE несовместим вообще ни с чем — это полная блокировка.
Очередь блокировок — FIFO: новый совместимый запрос не может перепрыгнуть через несовместимый. ACCESS EXCLUSIVE в очереди «загораживает» всё.
Безопасная миграция = SET lock_timeout + retry. Без timeout миграция может стоять часами, держа всю базу.
LOCK TABLE для апгрейда lock внутри своей транзакции не конфликтует — Postgres различает блокировки по transaction id.

CREATE TABLE: типы, NOT NULL, DEFAULT, IDENTITY Грабли потоков — deadlock, livelock, priority inversion, false sharing

Проверка знанийKnowledge check

Вечером в проде ты запустил CREATE INDEX CONCURRENTLY ON orders (placed_at). Команда зависла на час. Запустил CREATE INDEX CONCURRENTLY повторно (хотел отменить и переделать) — тоже зависла. В pg_stat_activity обе команды видны как 'active'. Что произошло, и как разрулить?

ОтветAnswer

CREATE INDEX CONCURRENTLY берёт SHARE UPDATE EXCLUSIVE на таблицу. SHARE UPDATE EXCLUSIVE несовместим сам с собой (см. матрицу): два CREATE INDEX CONCURRENTLY на одной таблице не могут идти параллельно — второй встаёт в очередь, ждёт первого. Кроме того, CREATE INDEX CONCURRENTLY делает три прохода по таблице и ждёт, пока завершатся все long-running snapshot'ы, начатые до его старта (потому что они могут видеть не-индексированные версии). Если у тебя в системе есть транзакция типа BEGIN; <часами не COMMIT>; — CREATE INDEX CONCURRENTLY будет ждать её. Разрулить: (1) найти long-running transactions через SELECT pid, query, xact_start FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active') ORDER BY xact_start; (2) отменить второй CREATE INDEX через pg_cancel_backend(pid); (3) если первый стоит ради старого snapshot'а — пристрелить виновного через pg_terminate_backend; (4) после успеха первого CREATE INDEX CONCURRENTLY (или её отмены) — проверить pg_indexes/pg_index.indisvalid; failed CREATE INDEX CONCURRENTLY оставляет INVALID индекс, его надо явно DROP-нуть перед retry.