PostgreSQL
Блокировки: row, table, advisory
ALTER TABLE на таблице с 50 миллионами строк в production. Ждёт 30 секунд пока не закроется долгий SELECT. За это время очередь к таблице вырастает до 500 запросов. Сервис лежит. Всё из-за одной DDL-команды и непонимания lock queue stacking.
- **GitHub** в 2019 публично описали инцидент: ALTER TABLE на таблице pull_requests вызвал lock queue stacking - сотни запросов встали в очередь за DDL-операцией, latency выросла до 30+ секунд. Решение: lock_timeout = 2s + retry loop
- **PagerDuty** использует advisory locks для распределённых cron jobs: pg_try_advisory_lock(hashtext('job-name')) гарантирует что задача выполняется только на одном из 20 app-серверов одновременно
- **Discord** применяет SELECT FOR UPDATE SKIP LOCKED для message delivery queue - 100+ workers берут сообщения из одной таблицы без конкуренции и deadlocks
Матрица совместимости lock modes
PG имеет 8 режимов table-level блокировки от самого слабого (AccessShareLock) до самого сильного (AccessExclusiveLock). Режимы конфликтуют если не могут удерживаться одновременно двумя транзакциями. Обычный SELECT берёт AccessShareLock. ALTER TABLE требует AccessExclusiveLock - конфликтует со всем, включая читателей.
| Lock Mode | Кто берёт | Конфликтует с |
|---|---|---|
| AccessShare | SELECT | AccessExclusive |
| RowShare | SELECT FOR UPDATE/SHARE | Exclusive, AccessExclusive |
| RowExclusive | INSERT, UPDATE, DELETE | Share, ShareRowExclusive, Exclusive, AccessExclusive |
| ShareUpdateExclusive | VACUUM, CREATE INDEX CONCURRENTLY | ShareUpdateExclusive, Share, ... |
| Share | CREATE INDEX | RowExclusive, ShareUpdateExclusive, ... |
| ShareRowExclusive | Редко, явно | RowExclusive, ShareUpdateExclusive, Share, ... |
| Exclusive | Refresh MV concurrently | Всё кроме AccessShare |
| AccessExclusive | ALTER TABLE, DROP, TRUNCATE | Всё включая AccessShare |
Транзакция делает CREATE INDEX (не CONCURRENTLY) на таблице. Какие операции заблокированы?
FOR UPDATE / FOR SHARE - row-level блокировки
SELECT FOR UPDATE берёт ExclusiveLock на строки - другие транзакции не могут их изменить или взять FOR UPDATE пока первая не закоммитит. Используется для паттерна read-modify-write: прочитать строку, принять решение, обновить - без риска что другая транзакция изменит строку между чтением и записью.
SKIP LOCKED - паттерн для job queues: несколько workers берут задачи из одной таблицы без конкуренции. Каждый worker делает FOR UPDATE SKIP LOCKED, получает незаблокированную задачу. Используется в Sidekiq, Delayed Job, PGMQ.
Зачем SELECT FOR UPDATE перед UPDATE в той же транзакции, если UPDATE сам берёт row lock?
Table locks - опасные DDL-операции
DDL-операции (ALTER TABLE, DROP, TRUNCATE) требуют AccessExclusiveLock - конфликтуют со всем включая обычные SELECT. Опасность: если на таблице есть долгоживущие транзакции (долгий SELECT), ALTER TABLE встаёт в очередь. При этом новые запросы к таблице тоже встают за ALTER TABLE - lock queue растёт, таблица фактически недоступна.
lock_timeout + retry - рекомендуемый паттерн для DDL в продакшене. Установить короткий timeout (2-5 с), попробовать ALTER TABLE, если ошибка - подождать и повторить. Это лучше чем ждать блокировки бесконечно и стаковать очередь запросов.
ALTER TABLE ждёт AccessExclusiveLock. В это время приходит новый SELECT на эту таблицу. Что произойдёт?
Advisory locks - бизнес-логика без таблиц
Advisory locks - механизм блокировок с произвольными int-ключами, не привязанными к таблицам. Приложение само определяет семантику ключа. Бывают session-level (держатся пока не отпущены явно или до закрытия соединения) и transaction-level (отпускаются автоматически при COMMIT/ROLLBACK).
Advisory locks видны в pg_locks как тип 'advisory'. Приложениям нужно договориться о ключах - обычно используют hashtext('job-name') или числовые константы из enum. Эти locks не защищают данные сами по себе - только координируют логику приложения.
pg_advisory_lock vs pg_advisory_xact_lock: в чём ключевое отличие при использовании connection pool?
Deadlock detection - автоматическое обнаружение
Deadlock возникает когда две транзакции циклически ждут друг друга. PG проверяет наличие циклов в wait-for графе раз в deadlock_timeout (1 сек по умолчанию). При обнаружении цикла PG выбирает 'жертву' (обычно транзакцию с меньшим весом работы) и откатывает её с ERROR: deadlock detected.
Deadlock - редкая аномалия, возникающая только при ошибках в коде
Deadlock может возникнуть в правильном коде при неудачном порядке блокировок. Профилактика - всегда блокировать ресурсы в детерминированном порядке (например по возрастанию id). Приложение должно обрабатывать ERROR: deadlock detected и повторять транзакцию.
В высоконагруженных системах даже правильный код при достаточном concurrency рано или поздно попадёт в deadlock. Monitoring pg_stat_database.deadlocks и retry-логика в приложении - обязательные компоненты production-системы.
Как PG выбирает 'жертву' при deadlock - кого откатывать?
Итоги
- AccessExclusiveLock (ALTER TABLE, DROP) конфликтует со всем - вызывает lock queue stacking; решение: lock_timeout + retry
- FOR UPDATE SKIP LOCKED - паттерн для job queues: workers конкурируют без deadlocks
- Deadlock профилактика: блокировать ресурсы всегда в одном порядке (по возрастанию id)
Связанные темы
Блокировки взаимодействуют с транзакциями и MVCC:
- Уровни изоляции транзакций — Serializable SSI использует predicate locks (pg_locks тип 'relation') дополнительно к обычным - важно понимать матрицу совместимости
- MVCC и читатели — MVCC объясняет почему обычные SELECT не берут read locks - видимость через snapshot, не через блокировки
- VACUUM и блокировки — VACUUM берёт ShareUpdateExclusiveLock - не мешает читателям и писателям, но конфликтует с другими VACUUM и некоторыми DDL
Вопросы для размышления
- Приложение делает BEGIN; SELECT FOR UPDATE; UPDATE; COMMIT. Другое приложение делает то же самое с теми же строками в другом порядке. Как гарантированно избежать deadlock?
- CREATE INDEX CONCURRENTLY берёт слабые блокировки в несколько фаз. Почему он может упасть с ошибкой и как это диагностировать через pg_locks?
- advisory lock session-level и connection pool: какой баг может возникнуть и как pg_advisory_xact_lock решает проблему?