Уровни изоляции транзакций в PostgreSQL: аномалии на живом примере

Шесть аномалий: что именно может пойти не так

Стандарт SQL описывает уровни изоляции через три феномена. Грязное чтение — транзакция видит данные, которые другая транзакция ещё не зафиксировала. Неповторяемое чтение — повторный SELECT той же строки возвращает другое значение, потому что другая транзакция успела её изменить и зафиксировать. Фантом — повторный запрос по условию возвращает другой набор строк: другая транзакция вставила, удалила или изменила подходящие строки. Документация PostgreSQL добавляет аномалию сериализации: результат параллельных транзакций не совпадает ни с одним порядком их последовательного выполнения.

В 1995 году Беренсон и соавторы в статье «A Critique of ANSI SQL Isolation Levels» показали, что трёх феноменов мало, и добавили к ним ещё несколько. Самые важные для практики — два. Потерянное обновление: обе транзакции прочитали значение, вычислили новое и записали, и одна запись затёрла другую. Write skew: транзакции читают пересекающиеся данные, меняют разные строки и вместе нарушают инвариант, который каждая проверила по отдельности. На собеседовании стоит назвать все шесть: именно последние две ломают приложения на уровнях, которые выглядят надёжными.

• PostgreSQL 17: Transaction Isolation
• Berenson et al. A Critique of ANSI SQL Isolation Levels (1995)

Стандарт SQL и реальное поведение PostgreSQL

В PostgreSQL можно запросить любой из четырёх стандартных уровней, но внутри реализованы только три: Read Uncommitted ведёт себя как Read Committed. Мы проверили это напрямую: сессия B выполнила UPDATE баланса до 999 без COMMIT, а сессия A на уровне READ UNCOMMITTED прочитала прежние 100. По умолчанию используется Read Committed, и SHOW default_transaction_isolation в свежем кластере вернул read committed. На этом уровне каждый оператор видит снимок на момент своего начала, поэтому два одинаковых SELECT в одной транзакции могут вернуть разные результаты.

Repeatable Read берёт снимок в момент первого оператора транзакции, а не BEGIN, и держит его до конца. Стандарт на этом уровне разрешает фантомы, PostgreSQL их не допускает. В нашем прогоне сессия B подняла баланс счёта до 150, добавила второй счёт с 50 и зафиксировала изменения, а повторный SELECT count(*), sum(balance) в транзакции A вернул прежние 1 и 100; на Read Committed тот же сценарий дал 2 и 200. Одноимённый уровень в MySQL устроен иначе: InnoDB использует REPEATABLE READ по умолчанию, обычные SELECT читают снимок первого чтения, а блокирующие чтения и изменения по диапазону ставят gap- и next-key-блокировки.

• PostgreSQL 17: Transaction Isolation
• MySQL 8.4: InnoDB Transaction Isolation Levels

Сценарий 1: потерянное обновление в Read Committed

Классика: приложение читает баланс, прибавляет сумму в своём коде и записывает результат. Сессия A пополняет счёт на 10, сессия B — на 20, обе начинают с баланса 100. Метки /* A */ и /* B */ показывают, в каком окне psql выполнять строку, а порядок строк — порядок во времени. Итог — 120: пополнение A пропало без единой ошибки. База здесь ни при чём: каждый UPDATE записал ровно то значение, которое ему передали.

На Repeatable Read тот же сценарий заканчивается иначе: UPDATE в сессии B получил «ERROR: could not serialize access due to concurrent update» с SQLSTATE 40001, баланс остался 110, и транзакцию B нужно повторить. Если же отдать арифметику базе — SET balance = balance + 10, — безопасен даже Read Committed: второй UPDATE ждёт фиксации первого и применяется к обновлённой версии строки, и в нашем прогоне 100 + 10 + 20 дали 130. Тот же итог получился с SELECT ... FOR UPDATE: сессия B ждала, затем прочитала 110. Третий вариант — оптимистическая блокировка: колонка версии с проверкой в WHERE и повтором, если UPDATE не затронул ни одной строки.

CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);
INSERT INTO accounts VALUES (1, 100);

/* A */ BEGIN ISOLATION LEVEL READ COMMITTED;
/* A */ SELECT balance FROM accounts WHERE id = 1;       -- 100
/* B */ BEGIN ISOLATION LEVEL READ COMMITTED;
/* B */ SELECT balance FROM accounts WHERE id = 1;       -- 100
/* A */ UPDATE accounts SET balance = 110 WHERE id = 1;  -- приложение: 100 + 10
/* A */ COMMIT;
/* B */ UPDATE accounts SET balance = 120 WHERE id = 1;  -- приложение: 100 + 20
/* B */ COMMIT;
SELECT balance FROM accounts WHERE id = 1;               -- 120, пополнение A потеряно

• PostgreSQL 17: Explicit Locking

Сценарий 2: write skew проходит даже в Repeatable Read

Инвариант: в больнице всегда дежурит хотя бы один врач. Алиса и Боб одновременно хотят уйти. Каждая транзакция считает дежурных, видит двоих и снимает с дежурства только себя. Строки разные, конфликта записи нет, и Repeatable Read ничего не замечает: оба COMMIT прошли, финальный SELECT вернул 0. Каждая транзакция по отдельности корректна — сломалась их комбинация.

Это и есть аномалия сериализации: ни при порядке «сначала A, потом B», ни при обратном второй врач уйти бы не смог. Защищаться можно тремя способами. Первый — Serializable, о нём следующий сценарий. Второй — явно заблокировать прочитанное: SELECT ... FOR UPDATE по всем строкам, участвующим в проверке, чтобы вторая транзакция ждала. Третий — выразить инвариант ограничением в схеме, если это возможно. Для проверок вида «вставить, если нет пересечений» блокировка существующих строк не помогает: новой строки ещё нет, заблокировать её нельзя.

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

/* A */ BEGIN ISOLATION LEVEL REPEATABLE READ;
/* A */ SELECT count(*) FROM doctors WHERE on_call;               -- 2
/* B */ BEGIN ISOLATION LEVEL REPEATABLE READ;
/* B */ SELECT count(*) FROM doctors WHERE on_call;               -- 2
/* A */ UPDATE doctors SET on_call = false WHERE name = 'alice';
/* B */ UPDATE doctors SET on_call = false WHERE name = 'bob';
/* A */ COMMIT;
/* B */ COMMIT;                                                   -- тоже успешно
SELECT count(*) FROM doctors WHERE on_call;                       -- 0

Сценарий 3: Serializable и ошибка 40001

Повторяем шаги сценария 2 с BEGIN ISOLATION LEVEL SERIALIZABLE. Чтения и UPDATE проходят без ожиданий, COMMIT сессии A успешен, а COMMIT сессии B возвращает ошибку с SQLSTATE 40001 и подсказкой повторить транзакцию; дежурным остаётся Боб. PostgreSQL реализует этот уровень через Serializable Snapshot Isolation: поверх снимков он отслеживает зависимости чтение–запись с помощью предикатных блокировок SIReadLock. Они никого не блокируют, а лишь отмечают, кто что читал.

Главное правило: 40001 — штатная ситуация, а не авария. Документация подчёркивает, что повторять нужно транзакцию целиком, включая логику, которая решала, какие запросы отправить и какие значения записать; автоматического повтора в PostgreSQL нет. Повторять только последний UPDATE бессмысленно: решение принималось по устаревшему чтению. И даже повтор не гарантирует успеха — попыток может понадобиться несколько. В разделе о Serializable есть и советы по производительности: объявляйте транзакции READ ONLY, где это возможно, не включайте в транзакцию больше, чем нужно для целостности, и не оставляйте соединения в состоянии idle in transaction.

UPDATE doctors SET on_call = true;
\set VERBOSITY verbose

/* A */ BEGIN ISOLATION LEVEL SERIALIZABLE;
/* A */ SELECT count(*) FROM doctors WHERE on_call;               -- 2
/* B */ BEGIN ISOLATION LEVEL SERIALIZABLE;
/* B */ SELECT count(*) FROM doctors WHERE on_call;               -- 2
/* A */ UPDATE doctors SET on_call = false WHERE name = 'alice';
/* B */ UPDATE doctors SET on_call = false WHERE name = 'bob';
/* A */ COMMIT;
/* B */ COMMIT;
-- ERROR:  40001: could not serialize access due to read/write dependencies among transactions
-- DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT:  The transaction might succeed if retried.
-- LOCATION:  PreCommit_CheckForSerializationFailure, predicate.c:4717
SELECT name, on_call FROM doctors ORDER BY name;                  -- alice f, bob t

• PostgreSQL 17: Serialization Failure Handling
• PostgreSQL 17: Transaction Isolation
• Ports, Grittner. Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)

Как выбрать уровень и ответить на собеседовании

Сильный ответ строится от аномалии. Назовите, что может сломаться в конкретной операции, затем самый дешёвый способ это исключить: атомарный UPDATE, блокировку строки, ограничение в схеме или уровень изоляции. Закончите ценой: Repeatable Read и Serializable требуют обработки 40001, явные блокировки — внимания к дедлокам и ожиданиям. Если речь заходит о MySQL, оговорите, что там Repeatable Read включён по умолчанию и устроен иначе.

Частая ошибка — путать изоляцию с блокировками. В PostgreSQL благодаря MVCC чтение не блокирует запись, а запись не блокирует чтение; уровень определяет, какой снимок видит транзакция и какие конфликты приводят к отмене. Вторая ошибка — считать Serializable медленным по определению: документация называет его лучшим по производительности выбором для части нагрузок, если сравнивать с явными блокировками. Готовьтесь к уточнениям:

• Почему в PostgreSQL нет грязного чтения? Читатель видит только зафиксированные версии строк.
• Чем Repeatable Read отличается от Serializable? Первый пропускает write skew, второй ловит его ценой ошибок 40001.
• Как защитить счёт от lost update без Serializable? Атомарный UPDATE, FOR UPDATE или версия строки.
• Что делать с 40001 в коде? Повторить всю транзакцию с ограничением числа попыток.

• PostgreSQL 17: MVCC Introduction

Повторите на своей базе

Поднимите одноразовый кластер, откройте два окна psql и пройдите три сценария, меняя уровень изоляции. Перед каждым COMMIT проговорите, что ожидаете увидеть: так вы проверяете модель в голове, а не память. Затем перепишите сценарий 1 на SELECT ... FOR UPDATE и убедитесь, что сессия B ждёт, а итоговый баланс — 130.

Шесть аномалий и таблица уровней хорошо ложатся на карточки: RecallDeck помогает повторять такие вопросы по расписанию. Рядом стоит прочитать про идемпотентность API — повтор запросов родственен повтору транзакций после 40001.

• 100 вопросов по SQL, PostgreSQL и базам данных
• План подготовки к SQL-собеседованию: аналитик и backend
• Идемпотентность API: ключи, повторы и конкурентные запросы
• Трек подготовки: backend

Коротко

Частые вопросы