Значит, ситуация: CD-пайплайн запускает миграцию в БД, которая делает совершенно рядовой
ALTER TABLE - например добавляет колонку или меняет поле. Миграция должна пройти за считанную секунду, но она зависает, и приходит алерт, что прод перестал отвечать на запросы.Представили? А мне и представлять не нужно. Рассказываю до чего докопался и что узнал.
Каждый
SELECT запрос в PostgreSQL создает блокировку - AccessShareLock на таблицы, которые он задействует. Это самый нестрогий лок, он не конфликтует с другими запросами и локами. Кроме одного.AccessExclusiveLock - создается при различных изменениях таблиц:
DROP, ALTER, TRUNCATE и тд. Этот единственный лок, который блокирует другие SELECT запросы.(подробнее про локи тут).
В базу отправляется жирнющий
SELECT-запрос, который выполняется, скажем, 60 секунд. Все это время на таблице висит AccessShareLock, но он никак не мешает другим запросам - всё работает.И тут прилетает
ALTER TABLE со своим AccessExclusiveLock. Он встает в очередь за предыдущей транзакцией - ждет пока она не завершится и не освободит таблицу от Share-лока. А заодно ExclusiveLock блокирует все последующие запросы в БД к этим таблицам, которые просто выстраиваются в очередь. А если табличка популярная, то и запросы с прода будут зависать.Вот схемка, спасибо Claude за визуализацию, все остальное писал сам:
10:00:00 — Большой SELECT с кучей JOIN-ов на 60 секунд
🟢 AccessShareLock взят
10:00:10 — ALTER TABLE запускается (нужен AEL)
🔴 Встал в очередь, ждёт окончания SELECT
10:00:11 — Обычный SELECT от приложения
⏸️ Встал в очередь ЗА миграцией
10:00:13 — SELECT, INSERT, SELECT, UPDATE...
⏸️ Все в очереди!
10:01:00 — Первый SELECT закончился
🔴 ALTER TABLE начал выполняться (5 секунд)
10:01:05 — Миграция завершилась
✅ Очередь разблокировалась
В моем случае было еще немного классного легаси-кода: есть периодическая джоба, которая открывает транзакцию (сессию) к БД, забирает из часть данных (2-5 сек), начинает эти данные обрабатывать, работать со внешними системами, потом забирает еще часть данных, обрабатывает их, и так далее в цикле. И только в сааааамом конце - закрывает транзакцию в БД (делает COMMIT).
Проблема в том, что все это время на задействованных таблицах висит AccessShareLock потому что транзакция не закрыта, COMMIT-а не было. Обычной работе это не мешает. А вот если во время работы этой джобы мы запускаем миграцию, то получается жопа.
Что делаем?
- Закрываем транзакции после того как сделали запрос. Лучше сделать 100 отдельных селектов только когда они нужны, и не держать транзакцию в
idle in transaction, удерживая AccessShareLock.- Оптимизируем большие запросы.
- Миграции запускаем с параметром PGOPTIONS="-c lock_timeout=5s" - тогда если миграция и уткнулась в ShareLock, то через 5 секунд она упадет и прод не будет лежать
- Установить idle_in_transaction_session_timeout в настройках PostgreSQL - оно будет дропать подобные транзакции в Idle in transaction статусе. Но сначала надо починить старые запросы, которые создают такое.
- Переносим SELECT-запросы на read-реплику (пока только теория)
- Мониторинг и логирование. Пока еще думаю какое именно.
- (еще почитать)