←Все статьи

Уровни изоляции транзакций: грязное чтение, фантомы, snapshot

Базы данных5 мин чтения
  • #sql
  • #transactions
  • #isolation
  • #mvcc
  • #acid

«Грязное чтение», «неповторяющееся чтение», «фантом» — термины из стандарта SQL, которые на собеседовании повторяют, но редко понимают, как они выглядят в реальном коде. Между двумя параллельными транзакциями возникает целый зоопарк аномалий, и уровни изоляции придуманы ровно для того, чтобы ими управлять. Разберём все четыре уровня с конкретными сценариями.

Зачем вообще изоляция

Транзакция — это группа операций, которая выполняется как единое целое: либо все успешны (commit), либо ни одна не применяется (rollback). Это часть ACID (Atomicity, Consistency, Isolation, Durability). Изоляция — буква «I» — отвечает за то, как параллельные транзакции видят изменения друг друга.

Если бы все транзакции выполнялись строго по очереди, проблем бы не было — но это убило бы производительность. На практике транзакции перекрываются во времени, и тут возникают аномалии. Стандарт SQL определяет четыре уровня изоляции, каждый из которых допускает свой набор аномалий.

Аномалии — на конкретных сценариях

Представим таблицу accounts(id, balance) и две параллельные транзакции T1 и T2.

Грязное чтение (dirty read)

T1 меняет баланс, но ещё не закоммитила. T2 читает это незакоммиченное значение и принимает решение на его основе. Если T1 потом делает rollback — T2 работала с данными, которых никогда не существовало.

T1: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- не коммитит
T2: BEGIN; SELECT balance FROM accounts WHERE id = 1;  -- видит -100
T1: ROLLBACK;

Допускается на уровне READ UNCOMMITTED. На практике почти нигде не используется; в PostgreSQL фактически недоступен и ведёт себя как READ COMMITTED.

Неповторяющееся чтение (non-repeatable read)

Один и тот же SELECT внутри транзакции возвращает разные строки, потому что другая транзакция успела закоммитить изменение существующей строки между двумя чтениями.

T1: BEGIN; SELECT balance FROM accounts WHERE id = 1;  -- 100
T2: BEGIN; UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
T1:       SELECT balance FROM accounts WHERE id = 1;  -- 50 (другое!)
T1: COMMIT;

T1 видит несогласованную картину: будто баланс изменился «во время разговора». Допускается на READ COMMITTED (дефолт в PostgreSQL и Oracle), запрещается на REPEATABLE READ и выше.

Фантом (phantom)

T1 выполняет запрос с условием, потом ещё раз с тем же условием — и получает другое количество строк, потому что другая транзакция добавила или удалила подходящие строки.

T1: BEGIN; SELECT COUNT(*) FROM accounts WHERE balance > 100;  -- 5
T2: BEGIN; INSERT INTO accounts VALUES (..., 150); COMMIT;
T1:       SELECT COUNT(*) FROM accounts WHERE balance > 100;  -- 6 (фантом!)
T1: COMMIT;

Отличие от неповторяющегося чтения: там менялись существующие строки, тут — множество строк (появились новые / пропали старые). Допускается на REPEATABLE READ по стандарту; запрещается на SERIALIZABLE.

Четыре уровня

УровеньГрязноеНеповтор.Фантом
READ UNCOMMITTEDдадада
READ COMMITTEDнетдада
REPEATABLE READнетнетда*
SERIALIZABLEнетнетнет

* — в PostgreSQL REPEATABLE READ дополнительно защищает и от фантомов (через MVCC), что идёт дальше стандарта. То есть PostgreSQL на REPEATABLE READ не допускает ни одной из трёх аномалий. В MySQL InnoDB — аналогично. Это «укреплённый» REPEATABLE READ, не совсем то, что описывает стандарт.

READ COMMITTED (дефолт в PostgreSQL)

Видит только закоммиченные данные. Внутри одной транзакции два SELECT могут вернуть разные строки, если кто-то успел закоммитить изменение между ними. Балансировка между изоляцией и параллелизмом — отсюда дефолт.

REPEATABLE READ

Один SELECT стабилен на протяжении всей транзакции: если в начале вы увидели баланс 100, он останется 100, что бы ни делали другие транзакции (пока вы не закоммитите и не начнёте новую). В PostgreSQL/MySQL — ещё и защита от фантомов. Чуть ниже параллелизм (дольше держит снапшот), выше согласованность.

SERIALIZABLE

Транзакции ведут себя так, будто выполняются строго последовательно — никакого перекрытия. Самый безопасный, но самый медленный. При конфликте одна из транзакций падает с ошибкой serialization failure и её надо перезапустить. На практике применяется в системах, где согласованность дороже скорости (банкинг, биллинг).

Что под капотом: MVCC

Multi-Version Concurrency Control — механизм, которым реализована изоляция в PostgreSQL, MySQL InnoDB, Oracle. Вместо того чтобы блокировать строки при чтении (что убило бы параллелизм), СУБД хранит несколько версий каждой строки. Каждая транзакция видит снапшот данных — то есть те версии, которые были согласованы на момент её старта (или на момент запроса — зависит от уровня).

Отсюда свойство: читатели не блокируют писателей, и наоборот. Чтение никогда не ждёт записи, запись — никогда не ждёт чтения. Цена — старые версии строк хранятся, пока есть активные транзакции, которые могут их видеть. Это раздувает таблицу (table bloat), и от него периодически чистит VACUUM в PostgreSQL (или аналогичные процессы в других СУБД).

Уровень изоляции определяет, какой снапшот видит транзакция:

  • READ COMMITTED — новый снапшот на каждый оператор;
  • REPEATABLE READ — один снапшот на всю транзакцию;
  • SERIALIZABLE — снапшот плюс отслеживание зависимостей между транзакциями (SSI — Serializable Snapshot Isolation).

Практический выбор

Для большинства веб-приложений READ COMMITTED — оптимальный дефолт. Хорошая производительность, приемлемая согласованность. Если логика чувствительна к тому, что данные «едут» под ногами в течение одной транзакции (расчёты, агрегации, где важна согласованность нескольких чтений) — поднимают до REPEATABLE READ.

SERIALIZABLE включают точечно, для критичных операций (перевод денег между счетами), и обязательно с обработкой serialization failure — повтором транзакции.

Короткое summary

Четыре уровня изоляции SQL — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — последовательно закрывают аномалии параллелизма: грязное чтение, неповторяющееся чтение, фантом. Дефолт в PostgreSQL и Oracle — READ COMMITTED (видит только закоммиченное, но допускает «едущие» данные внутри одной транзакции). REPEATABLE READ в PostgreSQL/MySQL дополнительно защищает от фантомов. SERIALIZABLE даёт полную изоляцию ценой конфликтов, требующих повтора. Под капотом всё работает через MVCC — несколько версий строк и снапшоты на транзакцию, поэтому читатели не блокируют писателей, а ценой является раздутие таблиц, которое чистит VACUUM.

Что почитать