Границы транзакции PostgreSQL: как не оставить смену без дежурного

Проблема начинается не с команды BEGIN, а с правила, которое лежит между строками. Пусть в ночной смене два дежурных. Каждый может снять только себя, но после любой подтверждённой операции должен остаться хотя бы один активный. Два запроса читают одно состояние activeCount = 2, каждый считает действие допустимым и меняет разные строки. Если оба успевают сохранить решение, смена остаётся без дежурного. Цена ошибки конкретна: интерфейс покажет два успешных ответа, а правило, на котором держится процесс, уже ложно.

Ниже не разбор настоящего инцидента и не обещание, что одна SQL-конструкция лечит любую конкуренцию. Я соберу фиксированную учебную модель из двух transaction-like операций: T1 выключает Анну, T2 выключает Бориса. Сначала они проходят небезопасное расписание. Затем T2 ждёт общую границу строк, читает состояние после T1 и отказывается от записи. В конце есть вариант с учебным retry. Он нужен, чтобы отличить инвариант, scope и результат проверки от названия isolation level.

Историческая рамка: январь 2021 и PostgreSQL 13

Для января 2021 беру документацию PostgreSQL 13. Major release 13 вышел 24 сентября 2020 года, а 13.1 — 12 ноября 2020 года. Это важно не ради даты в подвале. Поведение изоляции и формулировки о row-level locks следует проверять по версии, с которой работает приложение. В PostgreSQL 13 Read Committed — default; Read Uncommitted ведёт себя как Read Committed; Repeatable Read и Serializable дают более стабильную картину, но для конфликтов могут потребовать retry.

Слово «транзакция» здесь означает границу для чтений, проверки и записей, которые вместе поддерживают один инвариант. Оно не означает, что надо обернуть в один блок HTTP-вызов, ожидание пользователя или отправку письма. Чем шире такой блок, тем дольше удерживаются ресурсы и тем труднее объяснить конфликт. Сначала выписываем данные, которые участвуют в правиле. Потом решаем, как одна операция увидит и изменит их как единое действие. Только после этого выбираем SQL и isolation level.

Сначала формулируем invariant и его scope

В нашем упражнении invariant звучит так: count(enabled rows for one shift) >= 1. Он не принадлежит одной строке Анны и не помещается в поле enabled. Поэтому проверка CHECK (enabled) не решает задачу. Документация PostgreSQL 13 прямо предупреждает: CHECK не должен постоянно ссылаться на другие строки таблицы. Для ограничений между строками иногда подходит UNIQUE, EXCLUDE или FOREIGN KEY; если правило не выражается ими, его нужно защищать согласованной transaction strategy.

Почему read → решение → write без связи не годится

Небезопасный фрагмент ниже выглядит разумно, если смотреть на один запрос. Он читает количество, затем меняет одну строку. Ошибка проявляется между запросами. T1 и T2 могут увидеть один и тот же committed набор до изменения другого. Обе операции сохранят разные строки, поэтому простой конфликт записи «одна строка против одной строки» не обязан их остановить. Это не lost update одной колонки; это write skew — несовместимые решения на общем predicate.

-- Учебный anti-pattern: два session могут пройти условие до commits друг друга.
BEGIN;
SELECT count(*) AS active_count
FROM on_call
WHERE shift = 'night' AND enabled = true;
-- Если результат = 2, приложение отдельно решает снять себя с дежурства.
UPDATE on_call SET enabled = false WHERE shift = 'night' AND doctor = :current_doctor;
COMMIT;

-- Две разные UPDATE-строки не доказывают правило active_count >= 1.

Дополнительный SELECT count(*) перед COMMIT не исправляет конфликт сам по себе: это ещё один statement со своей видимостью. Проверка нужна внутри границы, где operation ещё можно отменить, а все writers инварианта соблюдают один protocol.

Явная граница строк: что она обещает и что не обещает

Для маленького и известного набора строк можно взять их в одном порядке через SELECT ... FOR UPDATE, посчитать состояние и выполнить изменение в той же transaction. В PostgreSQL 13 FOR UPDATE блокирует другие UPDATE, DELETE и lock-запросы на возвращённых строках до завершения текущей transaction. Обычный SELECT этот row-level lock не блокирует. Поэтому читателю нужно видеть ровно тот predicate, который возвращает scope, а не красивое слово «пессимистичная блокировка».

-- Учебный protocol для fixed row set; команды этой статьёй не запускались.
BEGIN;
SELECT doctor, enabled
FROM on_call
WHERE shift = 'night'
ORDER BY doctor
FOR UPDATE;
-- Проверяем invariant по возвращённому набору в этой же transaction.
UPDATE on_call SET enabled = false WHERE shift = :shift AND doctor = :current_doctor;
COMMIT;

-- Все writers этого правила должны брать тот же scope и тот же order.

У этого приёма есть две жёсткие предпосылки. Первая: все операции, которые могут изменить это правило, берут тот же набор строк. Если другой путь изменит Бориса без этой границы, доказательство разорвётся. Вторая: несколько объектов берутся в одном стабильном порядке. PostgreSQL 13 предупреждает, что разный порядок блокировок может привести к deadlock; сервер отменит одну transaction, но это не заменяет договор о порядке. В учебном SQL порядок задаёт ORDER BY doctor; в приложении он должен быть частью протокола, а не привычкой одного метода.

Даже корректный SELECT FOR UPDATE не означает «строка навсегда защищена». PostgreSQL отдельно отмечает: после commit или rollback ожидающая конфликтная transaction продолжит работу, если удерживавшая операция не сделала фактического UPDATE строки. Поэтому реальное решение надо проверять по своему write path. Здесь мы не делаем такой запуск; мы только формулируем, какую гарантию следует подтвердить на разрешённом стенде.

Фикстура: один schedule, три честных результата

Revision-модуль не пытается сыграть PostgreSQL. У него нет SQL parser, MVCC, lock manager, драйвера или сети. Он хранит два флага в объекте памяти и выполняет заранее записанные шаги. В наивной ветке оба snapshot содержат два активных дежурных; затем T1 и T2 выключают разные значения, и activeCount становится нулём. Во второй ветке scheduler помечает T2 ожидающим до завершения T1. После wake T2 читает уже единицу и возвращает отказ без write.

# Только in-memory schedule из revision-модуля; PostgreSQL не запускается.
node scripts/upgrade-2021-01.mjs --verify-fixture

# Ожидаемые свойства:
# naiveScheduleViolatesInvariant: true
# secondOperationIsBlockedInTeachingSchedule: true
# teachingBoundaryPreservesInvariant: true
# restartModelRetriesWholeOperationAndRejects: true

Третья ветка fixture показывает другой shape: T2 запомнил version учебного состояния, T1 успел изменить её, а T2 обнаружил несовпадение и повторяет всю operation от нового snapshot. После restart precondition становится ложной. Это не реализация PostgreSQL Serializable Snapshot Isolation и не утверждение о том, какой именно SQLSTATE вернёт сервер. Модель проверяет только дисциплину: конфликт не лечат повтором одного UPDATE, а заново получают данные, снова проверяют invariant и затем либо пишут, либо отказываются.

Нумерованный маршрут для одной операции

1. Запишите правило одним проверяемым предложением: что должно быть истинно после успешного commit. Для примера — «в смене остаётся минимум один enabled дежурный».
2. Выпишите все rows и predicate, от которых зависит правило. Не подменяйте их названием таблицы или одним target ID.
3. Найдите каждый write path, который может изменить эти rows. Если paths используют разные протоколы, сначала выравнивайте protocol, а не isolation level.
4. Выберите небольшой механизм: атомарный statement, constraints, Serializable с full retry или явную блокировку возвращённых rows. Зафиксируйте, почему он покрывает именно этот invariant.
5. Поставьте read, проверку и write внутри одной короткой transaction. Не держите её открытой во время HTTP-вызова, ожидания пользователя или тяжёлой фоновой работы.
6. Запустите учебную fixture, затем отдельно подготовьте integration test на разрешённой PostgreSQL версии с двумя sessions и фиксированным schedule.
7. Зафиксируйте результат: какой conflict ожидается, где выполняется rollback/retry и какие внешние действия нельзя публиковать до успешного commit.

Граница заканчивается там, где заканчивается состояние БД

Транзакция PostgreSQL удерживает согласованность данных, которыми она владеет. Она не отменяет уже отправленное письмо, не забирает сообщение из внешней очереди и не делает обратимым ответ другого HTTP-сервиса. Если такое side effect произошло до commit, retry способен повторить не только SQL, но и внешний результат. В январе 2021 для этого материала достаточно назвать границу: сначала формируем решение в БД, затем отдельным проектным механизмом публикуем внешний эффект. Не надо объявлять здесь готовую распределённую платформу.

Итог практики короткий. Не выбирайте «самый сильный» isolation level наугад. Назовите invariant, его rows, все writers и момент, когда решение становится необратимым. Для фиксированного набора строк явная блокировка может дать ясный protocol. Для сложных read/write зависимостей Serializable может быть удобнее, но тогда полный retry является частью контракта. После этого уже есть что проверять двумя сессиями, а не только что объяснять в код-ревью.

Все имена дежурных, schedule, версии, результаты и SQL-фрагменты ниже учебные. Revision-модуль работает только в памяти: он не открывает PostgreSQL, не выполняет SQL, не создаёт настоящее lock, не измеряет wait и не описывает реальную нагрузку.

Проверяемые источники

• PostgreSQL 13.1 released, 12 November 2020 — к январю 2021 ветка 13 уже имела минорный релиз 13.1; статьи фиксируют именно документацию PostgreSQL 13, а не современное поведение другой версии
• PostgreSQL 13: Transaction Isolation — описывает Read Committed как default, snapshot-границы, PostgreSQL Repeatable Read, Serializable и необходимость повторять отменённую транзакцию
• PostgreSQL 13: Explicit Locking — описывает row-level lock modes, конфликтующие операции, освобождение lock при завершении transaction и риск deadlock при разном порядке
• PostgreSQL 13: Data Consistency Checks at the Application Level — разделяет Serializable-подход и explicit blocking locks; отдельно предупреждает, что SELECT FOR UPDATE не сохраняет строку после окончания transaction сам по себе
• PostgreSQL 13: Constraints — объясняет, что CHECK не предназначен для постоянного контроля других строк таблицы; cross-row правило требует иной формулировки и защиты