Уровни изоляции PostgreSQL: READ COMMITTED и SERIALIZABLE

Уровень изоляции определяет, какие изменения конкурентных транзакций увидит запрос и какие опасные результаты PostgreSQL отвергнет. По умолчанию PostgreSQL работает в Read Committed: каждая команда получает новый снимок данных. Repeatable Read удерживает один снимок для всей транзакции, а Serializable дополнительно проверяет, можно ли объяснить результат последовательным выполнением транзакций.

Чем строже уровень, тем меньше аномалий может попасть в успешно зафиксированный результат, но тем важнее корректно повторять транзакции. Ошибка 40001 serialization_failure в PostgreSQL — не повреждение базы, а способ отменить один из несовместимых конкурентных сценариев. Приложение должно повторить всю бизнес-операцию с начала и считать её успешной только после COMMIT.

SQL / 01koddo

Повторное чтение зависит от изоляции

BEGIN, COMMIT и ROLLBACK задают атомарную границу

Транзакция объединяет несколько SQL-команд в одну атомарную операцию: фиксируются все её изменения либо ни одно. BEGIN открывает блок, COMMIT делает изменения постоянными и доступными последующим снимкам данных, а ROLLBACK отменяет незакоммиченную работу.

Например, перевод между счетами нельзя оставлять в промежуточном состоянии:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE id = 2;

COMMIT;

Если второй UPDATE или прикладная проверка завершается ошибкой, выполните ROLLBACK: списание с первого счёта тоже отменится. После SQL-ошибки явная транзакция обычно остаётся в проваленном состоянии и не принимает обычные команды до отката. Как найти первичную ошибку, а не вторичный 25P02, разобрано в справочнике по current transaction is aborted.

Без явного BEGIN каждая SQL-команда выполняется как отдельная транзакция. Поэтому два самостоятельных UPDATE не становятся атомарным переводом: между ними может завершиться процесс, оборваться соединение или сработать ошибка.

Атомарность относится к транзакционным изменениям базы. Внешний HTTP-запрос, отправленное письмо или сообщение брокеру PostgreSQL откатить не может. Даже значения sequence, полученные через nextval, не возвращаются назад при ROLLBACK, поэтому пропуски в идентификаторах нормальны.

Read Uncommitted в PostgreSQL работает как Read Committed

PostgreSQL принимает стандартную запись READ UNCOMMITTED, но не реализует отдельный режим грязного чтения. Внутри сервер использует поведение Read Committed: запрос не видит незакоммиченные изменения другой транзакции.

Поэтому выбирать Read Uncommitted ради более свежих данных или меньшего числа блокировок бессмысленно. Для PostgreSQL это не четвёртый практический уровень, а совместимое имя режима Read Committed.

Read Committed: новый snapshot для каждой команды

Read Committed — уровень PostgreSQL по умолчанию. Обычный SELECT видит строки, зафиксированные до начала именно этой команды, а также собственные предыдущие изменения транзакции. Следующая команда получает новый snapshot и может увидеть чужой COMMIT, случившийся между запросами.

Воспроизведите это в тестовой базе. Один раз подготовьте общую таблицу, доступную двум сеансам:

CREATE TABLE isolation_accounts (
  id integer PRIMARY KEY,
  balance integer NOT NULL
);

INSERT INTO isolation_accounts (id, balance) VALUES (1, 100);

В сеансе A начните транзакцию и прочитайте баланс:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT balance
FROM isolation_accounts
WHERE id = 1;
-- 100

Пока A остаётся открытым, в сеансе B измените ту же строку и зафиксируйте результат:

BEGIN;

UPDATE isolation_accounts
SET balance = 80
WHERE id = 1;

COMMIT;

Повторный запрос в A получает уже другой результат:

SELECT balance
FROM isolation_accounts
WHERE id = 1;
-- 80

COMMIT;

Это неповторяемое чтение: одна транзакция дважды прочитала строку и получила разные значения. Аналогично повторный запрос по условию может увидеть добавленные или удалённые строки, то есть phantom read.

Read Committed хорошо подходит коротким операциям, где каждая команда опирается на ограничения, атомарный UPDATE или явную блокировку нужной строки. Но результат первого SELECT не резервирует данные для следующего решения. Например, проверку отсутствия ключа и вставку лучше объединить через атомарный INSERT ... ON CONFLICT, а не надеяться на snapshot предварительного чтения.

Repeatable Read: один snapshot и никаких phantom reads

В PostgreSQL Repeatable Read фиксирует snapshot при первой команде чтения или изменения данных в транзакции. Последующие запросы видят тот же набор зафиксированных версий плюс собственные изменения. Чужие транзакции могут продолжать работу и делать COMMIT, но их новые версии не появятся в этом snapshot.

Это реализация сильнее минимального требования стандарта SQL: PostgreSQL на Repeatable Read не допускает phantom reads. Если повторить запрос по условию, новые подходящие строки из конкурентного COMMIT не появятся. Но стабильный снимок ещё не гарантирует, что результат нескольких конкурентных записей соответствует какому-либо последовательному порядку.

Верните учебный баланс к 100, затем откройте сеанс A:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT balance
FROM isolation_accounts
WHERE id = 1;
-- 100

В сеансе B измените строку и сделайте COMMIT:

BEGIN;

UPDATE isolation_accounts
SET balance = 80
WHERE id = 1;

COMMIT;

Второй SELECT сеанса A по-прежнему вернёт 100. Но попытка A изменить версию строки, которую B уже обновил после создания snapshot, завершится ошибкой:

SELECT balance
FROM isolation_accounts
WHERE id = 1;
-- 100

UPDATE isolation_accounts
SET balance = balance - 10
WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
-- SQLSTATE 40001

ROLLBACK;

Repeatable Read сохраняет согласованность своего снимка и отменяет конфликтующую транзакцию вместо незаметного перехода к версии строки со значением 80. Приложение должно повторить весь блок A с новым snapshot. Только чтение на этом уровне не получает serialization failure из-за конкурентного обновления, но может вернуть уже устаревший относительно текущего момента снимок.

Serializable: SSI отклоняет несовместимый результат

Serializable в PostgreSQL основан на Serializable Snapshot Isolation, или SSI. Чтение работает со стабильным snapshot, как на Repeatable Read, а сервер дополнительно отслеживает зависимости чтение-запись между конкурентными serializable-транзакциями. Если их общий результат нельзя получить ни при одном последовательном порядке, PostgreSQL отменяет одну из транзакций.

Для такого контроля PostgreSQL использует predicate locks, видимые в pg_locks как SIReadLock. Они отмечают прочитанные данные и не блокируют запись сами по себе. Это не обычные блокировки строк: SSI старается сохранить конкуренцию, а опасный цикл зависимостей разрывает ошибкой 40001.

Короткий пример — дежурство двух врачей. В таблице on_call у Алисы и Бориса стоит active = true. Две транзакции одновременно читают число активных врачей и получают 2. Затем A выключает Алису, а B — Бориса.

На Repeatable Read обе транзакции могут успешно зафиксироваться: каждая меняет свою строку, а результат с нулём дежурных не соответствует бизнес-правилу. На Serializable обе успешно завершиться не смогут. Одна получит 40001 serialization_failure, потому что ни один последовательный порядок не позволил бы обеим сначала увидеть двух активных врачей.

Гарантия Serializable относится только к успешно зафиксированным транзакциям. Ошибка может появиться на изменяющей команде или при COMMIT, поэтому прочитанные данные и решения на их основе нельзя считать окончательными заранее.

SET TRANSACTION выполняют до первого запроса

Уровень изоляции нельзя менять после первого SELECT, INSERT, UPDATE, DELETE, MERGE, FETCH или COPY в транзакции. Корректная последовательность выглядит так:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT count(*)
FROM on_call
WHERE active;

COMMIT;

Короче и безопаснее задать режим прямо в BEGIN:

BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE;

Если сначала выполнить бизнес-запрос, а потом SET TRANSACTION ISOLATION LEVEL ..., PostgreSQL вернёт ошибку. Настройка в произвольном месте функции не «усилит» уже полученный snapshot.

Для длинного отчёта существует специальный режим SERIALIZABLE READ ONLY DEFERRABLE. Он может подождать в начале, пока PostgreSQL получит безопасный snapshot, после чего такой read-only блок не будет отменён из-за serialization failure. Это точечный режим для отчётов и резервного копирования, а не замена обычных read-write транзакций.

SQLSTATE 40001: повторить всю транзакцию

После 40001 serialization_failure нельзя повторять только последний UPDATE или COMMIT. Решения транзакции могли зависеть от старого snapshot, поэтому повторяют всю функцию верхнего уровня: новый BEGIN, все чтения, вычисления, проверки, записи и COMMIT.

Обработчик повтора делает следующее:

1. Начинает новую транзакцию с нужным уровнем изоляции.
2. Заново читает данные и принимает решения, не переиспользуя результаты отменённой попытки.
3. При 40001 откатывает соединение, делает ограниченную паузу с jitter и повторяет весь блок.
4. Ограничивает число попыток и после исчерпания лимита возвращает контролируемую ошибку.
5. Сообщает об успехе только после успешного COMMIT.

Проверяйте SQLSTATE, а не текст сообщения: формулировка ошибки зависит от конкретного конфликта. После сбоя не возвращайте соединение в пул с открытой проваленной транзакцией; сначала выполните ROLLBACK либо позвольте транзакционному контексту драйвера сделать это гарантированно.

Deadlock 40P01 — отдельная причина отката

40P01 deadlock_detected означает цикл обычных ожиданий блокировок: например, A удерживает строку 1 и ждёт строку 2, а B удерживает строку 2 и ждёт строку 1. PostgreSQL прерывает одну транзакцию, чтобы разорвать цикл. Это не проверка SSI и не вариант 40001.

Всю транзакцию после 40P01 часто тоже можно повторить, если бизнес-операция допускает повтор. Но регулярный deadlock надо исправлять: брать ресурсы в одинаковом порядке, сокращать транзакции и не держать блокировки во время сетевых вызовов. Диагностика blocker/blocked и безопасное снятие ожиданий описаны в разборе блокировок PostgreSQL.

Не выполняйте side effects до успешного COMMIT

Повтор транзакции делает опасными необратимые действия внутри её попытки. Если код отправил письмо, списал деньги через внешний API или опубликовал сообщение, а затем получил 40001 на COMMIT, PostgreSQL отменит свои записи, но внешнее действие останется. Следующая попытка может выполнить его второй раз.

Переносите внешний side effect после успешного COMMIT. Когда доставка должна быть связана с изменением базы, сохраните запись outbox в той же транзакции, а отдельный worker отправит её после фиксации. И получатель, и worker должны обрабатывать стабильный ключ идемпотентности: повтор доставки тогда не создаст второе списание или письмо. Если внешний вызов нельзя отложить, его идемпотентность обязательна, а все попытки транзакции должны использовать один и тот же ключ бизнес-операции.

Как выбрать уровень изоляции в PostgreSQL

Уровень выбирают по инварианту, а не по принципу «строже всегда лучше». Сначала перенесите правило в UNIQUE, CHECK, внешний ключ или одну атомарную DML-команду, если это возможно. Затем используйте явную блокировку для конкретного ресурса либо Serializable для зависимости между наборами данных. В любом варианте держите транзакцию короткой, завершайте её COMMIT или ROLLBACK и проектируйте повтор до выхода в production.

После учебных сценариев удалите общую таблицу:

DROP TABLE isolation_accounts;