Представьте следующую ситуацию: ваш CI/CD-пайплайн запускает миграцию базы данных, которая выполняет стандартную операцию ALTER TABLE — например, добавляет колонку или изменяет тип поля. Ожидается, что такая операция займет считанные секунды, но вместо этого она зависает, и вскоре вы получаете тревожное оповещение о том, что продакшн-сервер перестал отвечать на запросы.

Знакомая ситуация? Мне не нужно ее представлять. В этой статье мы разберем корни этой проблемы, связанной с блокировками в PostgreSQL, и предложим эффективные решения.

Понимание блокировок в PostgreSQL

Каждый SELECT-запрос в PostgreSQL, обращаясь к таблицам, создает нестрогую блокировку типа AccessShareLock. Это самый слабый вид блокировки, который обычно не конфликтует с большинством других запросов и блокировок. Он позволяет другим SELECT, INSERT, UPDATE и DELETE операциям выполняться одновременно.

Однако существует исключение: AccessExclusiveLock. Эта блокировка создается при выполнении операций, существенно изменяющих структуру или данные таблицы, таких как DROP TABLE, ALTER TABLE, TRUNCATE TABLE и т.д. AccessExclusiveLock является самой строгой блокировкой и блокирует все другие типы доступа к таблице, включая даже SELECT-запросы.

Сценарий блокировки: От долгих запросов до простоя продакшна

Рассмотрим пошаговый сценарий, который приводит к проблемам:

  1. К базе данных отправляется ресурсоемкий SELECT-запрос, выполнение которого занимает, скажем, 60 секунд. На протяжении всего этого времени на задействованных таблицах удерживается AccessShareLock. Он не мешает другим обычным запросам, и система функционирует штатно.
  2. Через 10 секунд после старта долгого SELECT запускается миграция, выполняющая ALTER TABLE. Для этой операции требуется AccessExclusiveLock. Поскольку AccessShareLock от долгого SELECT еще удерживается, ALTER TABLE не может получить требуемую эксклюзивную блокировку и встает в очередь ожидания, пока предыдущая транзакция не завершится и не освободит таблицу.
  3. Что происходит дальше? AccessExclusiveLock, хотя и находится в очереди, немедленно блокирует все последующие запросы к этим таблицам. Любые новые SELECT, INSERT, UPDATE или DELETE операции, которые пытаются получить доступ к заблокированным таблицам, также встают в очередь, но уже *за* ожидающей миграцией.
  4. Если таблица, к которой обращаются запросы, является популярной, то быстро формируется длинная очередь, и запросы от основного приложения на продакшне начинают зависать, приводя к полной остановке сервиса.

Вот наглядная визуализация последовательности событий:

  • 10:00:00 — Запускается большой SELECT с множеством JOIN-ов, выполняющийся 60 секунд.
    • 🟢 AccessShareLock захвачен.
  • 10:00:10 — Запускается ALTER TABLE (требует AccessExclusiveLock).
    • 🔴 Встает в очередь, ожидая завершения SELECT.
  • 10:00:11 — Обычный SELECT от приложения.
    • ⏸️ Встает в очередь *за* миграцией.
  • 10:00:13 — Последующие SELECT, INSERT, UPDATE и другие запросы.
    • ⏸️ Все они также встают в очередь.
  • 10:01:00 — Первый большой SELECT завершается, освобождая AccessShareLock.
    • 🔴 ALTER TABLE начинает выполняться (допустим, 5 секунд).
  • 10:01:05 — Миграция завершается, освобождая AccessExclusiveLock.
    • ✅ Очередь запросов разблокируется, работа восстанавливается.

Проблема «долгоживущих» транзакций

Ситуация усугубляется наличием так называемого «легаси-кода». В моем случае это была периодическая фоновая задача (джоба), которая открывала транзакцию (сессию) к БД, забирала часть данных (2-5 секунд), затем начинала их обрабатывать, взаимодействовать с внешними системами, после чего забирала еще часть данных, обрабатывала их и так далее по циклу. И только в самом конце — через длительное время — закрывала транзакцию в БД, выполняя COMMIT.

Проблема в том, что все это время на задействованных таблицах висел AccessShareLock, поскольку транзакция оставалась открытой (в статусе idle in transaction) и не была закоммичена. В обычных условиях это не мешает работе, но если в момент выполнения такой джобы мы запускаем миграцию, то гарантированно получаем описанный выше сценарий с блокировкой и простоем.

Как предотвратить зависания продакшна?

Для минимизации риска подобных проблем рекомендуется применять следующие подходы:

  • Закрывайте транзакции как можно раньше. После выполнения запросов не держите транзакции открытыми без необходимости. Лучше выполнить несколько отдельных запросов, когда они действительно нужны, чем удерживать AccessShareLock на протяжении длительного времени в статусе idle in transaction.
  • Оптимизируйте длительные запросы. Пересмотрите и оптимизируйте сложные SELECT-запросы, чтобы сократить время их выполнения и, соответственно, время удержания AccessShareLock. Используйте индексы, уменьшайте количество JOIN-ов, если это возможно, или разбивайте запросы на более мелкие.
  • Устанавливайте таймаут для миграций. Запускайте миграции с параметром PGOPTIONS="-c lock_timeout=5s". Это позволит миграции автоматически завершиться с ошибкой (откатиться) через заданное время (например, 5 секунд), если она не сможет получить необходимую блокировку. Таким образом, миграция не будет бесконечно ждать, а быстро упадет, что предотвратит долгосрочное зависание продакшна.
  • Настройте idle_in_transaction_session_timeout в PostgreSQL. Этот параметр в конфигурации PostgreSQL автоматически принудительно завершает сессии, находящиеся в статусе idle in transaction, если они превышают установленный таймаут. Это эффективная мера против забытых или некорректно завершенных транзакций. Однако перед активацией убедитесь, что ваш код корректно обрабатывает завершение транзакций, чтобы избежать нежелательных ошибок.
  • Переносите SELECT-запросы на read-реплики. Если архитектура приложения позволяет, перенесите все или часть ресурсоемких SELECT-запросов на реплики базы данных, предназначенные только для чтения. Это значительно снизит нагрузку на основную базу данных и уменьшит вероятность конфликтов блокировок.
  • Внедряйте мониторинг и логирование. Настройте детальный мониторинг состояния блокировок в PostgreSQL (например, через таблицы pg_locks) и логируйте длительные запросы и транзакции. Это поможет оперативно выявлять потенциальные проблемы и анализировать причины простоев.