Миграция ALTER TABLE ... ADD COLUMN без конкурентов выполняется за 5,3 мс (замер на таблице из опыта), но стоит ей встретить долгий запрос, и все обычные SELECT к этой таблице простаивают десять секунд. Дело не в плане и не в нагрузке: ALTER TABLE просит самый сильный режим блокировки, ждёт чужой долгий запрос, а каждый запрос, пришедший после него, встаёт в очередь за ним, а не за долгим запросом. Как найти такую цепочку и снять её, разобрано в статье про блокирующий запрос; здесь — устройство: матрица режимов, правило очереди в lock.c и способ запускать DDL, который очередь не собирает.
Опыт 1: матрица восьми режимов
Подготовка: контейнер и таблица на тысячу строк.
docker run -d --name dba-locks-internals-and-queue -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-locks-internals-and-queue psql -U postgres -c "SELECT version()" \
-c "CREATE TABLE t (id int PRIMARY KEY, v text)" \
-c "INSERT INTO t SELECT g, 'r' || g FROM generate_series(1, 1000) g"Версия: PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu. Сначала — какой режим берёт какая команда. Каждая выполняется в транзакции, затем из pg_locks читаются режимы собственного процесса на таблице t:
for s in "SELECT count(*) FROM t" "SELECT id FROM t WHERE id = 1 FOR UPDATE" \
"UPDATE t SET v = 'x' WHERE id = 1" "CREATE INDEX t_v ON t (v)" "ALTER TABLE t ADD COLUMN c2 int"; do
printf '%-42s' "$s"
docker exec -i dba-locks-internals-and-queue psql -U postgres -Atq -c "BEGIN; $s; SELECT string_agg(mode, ',') FROM pg_locks WHERE locktype = 'relation' AND relation = 't'::regclass AND pid = pg_backend_pid()" | tail -1
doneвывод
SELECT count(*) FROM t AccessShareLock
SELECT id FROM t WHERE id = 1 FOR UPDATE RowShareLock
UPDATE t SET v = 'x' WHERE id = 1 RowExclusiveLock
CREATE INDEX t_v ON t (v) ShareLock
ALTER TABLE t ADD COLUMN c2 int AccessExclusiveLockТеперь матрица. Один процесс держит режим 4 секунды, второй пробует взять каждый из восьми режимов с NOWAIT: ошибка could not obtain lock значит конфликт.
modes=("ACCESS SHARE" "ROW SHARE" "ROW EXCLUSIVE" "SHARE UPDATE EXCLUSIVE" "SHARE" "SHARE ROW EXCLUSIVE" "EXCLUSIVE" "ACCESS EXCLUSIVE")
psql() { docker exec -i dba-locks-internals-and-queue psql -U postgres -Atq "$@" 2>&1; }
for h in "${modes[@]}"; do
psql -c "BEGIN; LOCK TABLE t IN $h MODE; SELECT pg_sleep(4)" >/dev/null &
sleep 1; row=""
for r in "${modes[@]}"; do
if psql -c "BEGIN; LOCK TABLE t IN $r MODE NOWAIT" | grep -q "could not obtain"; then row="$row X "; else row="$row . "; fi
done
printf '%-24s%s\n' "$h" "$row"; wait
doneвывод
held \ asked AS RS RE SU S SR E AE
ACCESS SHARE . . . . . . . X
ROW SHARE . . . . . . X X
ROW EXCLUSIVE . . . . X X X X
SHARE UPDATE EXCLUSIVE . . . X X X X X
SHARE . . X X . X X X
SHARE ROW EXCLUSIVE . . X X X X X X
EXCLUSIVE . X X X X X X X
ACCESS EXCLUSIVE X X X X X X X X Конфликтуют 38 из 64 пар, и таблица симметрична. Обычный SELECT (AS) мешает только ACCESS EXCLUSIVE, UPDATE (RE) не конфликтует сам с собой, а ALTER TABLE (AE) конфликтует со всеми восемью, включая безобидный SELECT. В коде это тот же массив: для каждого режима хранится маска режимов, с которыми он конфликтует. Первые две записи и последняя (с пропуском строк 73–98):
/* AccessShareLock */
LOCKBIT_ON(AccessExclusiveLock),
/* RowShareLock */
LOCKBIT_ON(ExclusiveLock) | LOCKBIT_ON(AccessExclusiveLock),
...
/* AccessExclusiveLock */
LOCKBIT_ON(AccessShareLock) | LOCKBIT_ON(RowShareLock) |
LOCKBIT_ON(RowExclusiveLock) | LOCKBIT_ON(ShareUpdateExclusiveLock) |
LOCKBIT_ON(ShareLock) | LOCKBIT_ON(ShareRowExclusiveLock) |
LOCKBIT_ON(ExclusiveLock) | LOCKBIT_ON(AccessExclusiveLock)lock.c, LockConflicts, строки 68–72 и 99–103: строка AccessShareLock в выводе опыта — одна маска из одного бита, строка AccessExclusiveLock — все восемь.
Опыт 2: очередь за ALTER TABLE
Пять терминалов с docker exec -it dba-locks-internals-and-queue psql -U postgres; \timing on включён в S2–S5. Время отсчитывается от запуска S1:
- S1, 0 с:
SELECT pg_sleep(12) FROM t LIMIT 1;— долгий читатель, держитAccessShareLock12 секунд; - S2, 1 с:
ALTER TABLE t ADD COLUMN c int; - S3, S4, S5, на 2-й, 2,5-й и 3-й секундах:
SELECT count(*) FROM t; - на 4,5-й секунде из шестого терминала — снимок:
SELECT a.pid, a.wait_event_type || ':' || a.wait_event AS wait, l.mode, l.granted AS g,
round(extract(epoch FROM now() - l.waitstart)::numeric, 1) AS waited_s,
pg_blocking_pids(a.pid) AS blocked_by, left(a.query, 28) AS query
FROM pg_stat_activity a JOIN pg_locks l USING (pid)
WHERE l.locktype = 'relation' AND l.relation = 't'::regclass
ORDER BY l.granted DESC, a.query_start;вывод
pid | wait | mode | g | waited_s | blocked_by | query
------+-----------------+---------------------+---+----------+------------+------------------------------
1171 | Timeout:PgSleep | AccessShareLock | t | | {} | SELECT pg_sleep(12) FROM t L
1178 | Lock:relation | AccessExclusiveLock | f | 3.5 | {1171} | ALTER TABLE t ADD COLUMN c i
1185 | Lock:relation | AccessShareLock | f | 2.5 | {1178} | SELECT count(*) FROM t;
1192 | Lock:relation | AccessShareLock | f | 2.0 | {1178} | SELECT count(*) FROM t;
1199 | Lock:relation | AccessShareLock | f | 1.5 | {1178} | SELECT count(*) FROM t;Три SELECT совместимы с читателем S1 (матрица: AS и AS не конфликтуют), но pg_blocking_pids называет их блокировщиком не S1 (1171), а ALTER (1178). После завершения S1 время ожидания, которое напечатал \timing:
вывод
S2 (ALTER): Time: 11012.266 ms (00:11.012)
S3 (SELECT): Time: 10010.908 ms (00:10.011)
S4 (SELECT): Time: 9508.330 ms (00:09.508)
S5 (SELECT): Time: 9007.937 ms (00:09.008)Тот же SELECT count(*) FROM t без очереди занимает 0,39 мс (три замера: 0,389, 0,437, 0,388 мс). Девять-десять секунд — чистое ожидание в очереди. Причина — одно сравнение в LockAcquireExtended:
/*
* If lock requested conflicts with locks requested by waiters, must join
* wait queue. Otherwise, check for conflict with already-held locks.
* (That's last because most complex check.)
*/
if (lockMethodTable->conflictTab[lockmode] & lock->waitMask)
found_conflict = true;
else
found_conflict = LockCheckConflicts(lockMethodTable, lockmode,
lock, proclock);lock.c, строки 1088–1097. waitMask — режимы, которых уже ждёт кто-то в очереди этого объекта. Новый запрос сверяется с очередью раньше, чем с держателями: AccessShareLock конфликтует с AccessExclusiveLock из маски, и SELECT встаёт в хвост, хотя держатель его не блокирует.
Один читатель, один ALTER, три SELECT: очередь по времени
Опыт 3: lock_timeout и повтор DDL
Если DDL не должен собирать очередь, он должен отказываться ждать. lock_timeout обрывает команду, которая ждёт блокировку дольше лимита; вместе с ней уходит из очереди и ALTER, а читатели за ним проходят. Опыт: читатель держит таблицу 6 секунд, миграция повторяется с лимитом 500 мс и паузой 1 секунда, а параллельно каждые 250 мс идёт зонд SELECT count(*) FROM t с \timing.
q() { docker exec -i dba-locks-internals-and-queue psql -U postgres -q "$@"; }
q -c "ALTER TABLE t DROP COLUMN c" # столбец остался от опыта 2
( q -At -c "SELECT pg_sleep(6) FROM t LIMIT 1" >/dev/null 2>&1 ) &
sleep 1
( n=0; until q -At -v ON_ERROR_STOP=1 -c "SET lock_timeout = '500ms'" -c "ALTER TABLE t ADD COLUMN c int"; do
n=$((n+1)); echo "attempt $n failed at $(date +%S.%N | cut -c1-5)"; sleep 1
done; echo "attempt $((n+1)) ok at $(date +%S.%N | cut -c1-5)" ) &
for i in $(seq 1 24); do q -At <<< $'\\timing on\nSELECT count(*) FROM t;' | grep Time | awk '{print $2}'; sleep 0.25; done > probe.out
wait
echo "probes: $(wc -l < probe.out), max ms: $(sort -n probe.out | tail -1), over 100 ms: $(awk '$1>100' probe.out | wc -l)"вывод
ERROR: canceling statement due to lock timeout
attempt 1 failed at 36.00
ERROR: canceling statement due to lock timeout
attempt 2 failed at 37.55
ERROR: canceling statement due to lock timeout
attempt 3 failed at 39.11
attempt 4 ok at 40.50
probes: 24, max ms: 345.604, over 100 ms: 4Три попытки прервались через 500 мс каждая, четвёртая прошла сразу после того, как читатель завершился. Худший зонд ждал 345,6 мс против 9–10 секунд без лимита; четыре зонда из 24 вышли за 100 мс — те, что попали в окна по 500 мс. Цена повтора — эти окна: лимит 500 мс означает, что читатели могут задержаться до 500 мс на каждую попытку. Если читатель так и не уйдёт, цикл надо ограничить числом попыток.
Другие уровни и advisory locks
Матрица выше — только «тяжёлые» (regular) блокировки. В README менеджера блокировок их четыре вида, и у каждого своя цена ожидания:
* Lightweight locks (LWLocks). These locks are typically used to
interlock access to datastructures in shared memory. LWLocks support
both exclusive and shared lock modes (for read/write and read-only
access to a shared object). There is no provision for deadlock
detection, but the LWLock manager will automatically release held
LWLocks during elog() recovery, so it is safe to raise an error while
holding LWLocks. Obtaining or releasing an LWLock is quite fast (a few
dozen instructions) when there is no contention for the lock. When a
process has to wait for an LWLock, it blocks on a SysV semaphore so as
to not consume CPU time. Waiting processes will be granted the lock in
arrival order. There is no timeout.
* Regular locks (a/k/a heavyweight locks). The regular lock manager
supports a variety of lock modes with table-driven semantics, and it has
full deadlock detection and automatic release at transaction end.src/backend/storage/lmgr/README, строки 20–34. Для администратора различие видно в pg_stat_activity.wait_event_type: тип Lock — тяжёлая блокировка, где работают lock_timeout и pg_blocking_pids; LWLock — внутренняя структура в памяти сервера, где тайм-аута нет. В 18.6 pg_wait_events перечисляет 12 событий Lock и 84 события LWLock:
SELECT type, count(*) FROM pg_wait_events WHERE type IN ('Lock', 'LWLock') GROUP BY 1 ORDER BY 1;
SELECT string_agg(name, ', ' ORDER BY name) FROM pg_wait_events WHERE type = 'Lock';вывод
Lock|12
LWLock|84
advisory, applytransaction, extend, frozenid, object, page, relation, spectoken, transactionid, tuple, userlock, virtualxidВ списке Lock есть advisory: рекомендательные блокировки прикладного кода идут через тот же менеджер. Сеанс берёт pg_advisory_lock(42), второй пробует то же без ожидания:
SELECT pg_advisory_lock(42); -- сеанс 1
SELECT pg_try_advisory_lock(42) AS got_it; -- сеанс 2
SELECT pid, locktype, classid, objid, mode, granted FROM pg_locks WHERE locktype = 'advisory';вывод
got_it
--------
f
pid | locktype | classid | objid | mode | granted
------+----------+---------+-------+---------------+---------
1445 | advisory | 0 | 42 | ExclusiveLock | tВторой сеанс получил f без ожидания, а в pg_locks ключ 42 виден как locktype = 'advisory', режим ExclusiveLock. Блокировка не привязана к таблице: приложение само решает, что означает число 42 (очередь задач, один воркер на ресурс), и должно само снять её pg_advisory_unlock.
Что из этого следует
Опасен не долгий SELECT, а DDL за ним. Сам читатель никого не задерживает: три SELECT в опыте 2 были бы совместимы с ним. Очередь образует ACCESS EXCLUSIVE, и любая команда, которой нужен AS или сильнее, ждёт её хвост.
Любая миграция с ACCESS EXCLUSIVE идёт с lock_timeout. В опыте 3 лимит 500 мс сократил худшую задержку читателя с 9–10 с до 0,35 с. Значение выбирай по допустимой задержке чтения, а повтор ограничивай по числу попыток.
Режим читай по команде, а не по названию. CREATE INDEX берёт ShareLock и пропускает чтение, но блокирует UPDATE (RE конфликтует с S в матрице); тот же ALTER TABLE ADD COLUMN берёт AccessExclusiveLock, как показал первый вывод опыта 1. Что берёт конкретная подкоманда — в документации ALTER TABLE и разделе 13.3; на стенде проверяется тем же запросом к pg_locks.
Ожидание тяжёлой блокировки диагностируется, ожидание LWLock — нет. pg_blocking_pids и lock_timeout работают в области типа Lock; для LWLock остаётся только имя события в pg_stat_activity.
Чек-лист
- Перед миграцией с
ACCESS EXCLUSIVEпоставьSET lock_timeout; команда без лимита ждёт любого читателя и собирает очередь. - Проверь долгие транзакции:
SELECT pid, xact_start, state, left(query, 60) FROM pg_stat_activity WHERE xact_start < now() - interval '1 minute'. Один читатель на 12 секунд в опыте 2 дал очередь на 9–10 секунд. - В цепочке
pg_blocking_pidsищи не держателя, а первый ждущийAccessExclusiveLock: за ним встают все новые запросы. - Повтор DDL ограничь числом попыток и паузой, иначе повтор сам станет постоянной нагрузкой на очередь.
- Advisory lock снимай явно (
pg_advisory_unlockили конец транзакции дляpg_advisory_xact_lock): по документации сеансовая блокировка живёт до конца сеанса или явного снятия.
Контейнер удаляется командой docker rm -f dba-locks-internals-and-queue.