Уровни изоляции транзакций

Уровень изоляции транзакций — настройка СУБД, определяющая, насколько параллельно выполняющиеся транзакции изолированы друг от друга: видит ли одна транзакция незафиксированные или только что зафиксированные изменения другой. Полная изоляция — результат как при последовательном выполнении — дорога, поэтому стандарт SQL определяет несколько уровней, на которых ради производительности допускаются те или иные аномалии.

Уровень изоляции — буква «I» в ACID и частый источник тонких ошибок: код, правильно работающий в тестах с одним пользователем, под нагрузкой теряет обновления или нарушает бизнес-правила.

Аномалии

Классический пример перекоса записи: в больнице должен дежурить хотя бы один врач. Два дежурных одновременно проверяют, что дежурных двое, и каждый снимает себя с дежурства. Каждая транзакция по отдельности корректна, а вместе они оставили больницу без врача.

Уровни стандарта

Уже в 1995 году Беренсон и соавторы показали, что определения стандарта неполны и неоднозначны: они не учитывают потерянные обновления и перекос записи и не описывают широко используемую изоляцию снимков (snapshot isolation), при которой транзакция видит согласованный снимок базы на момент начала.^[1] Поэтому реальные СУБД под одинаковыми названиями дают разные гарантии.

Как это реализовано

PostgreSQL

Благодаря MVCC читатели никогда не блокируют писателей.^[2]

• Read Committed (по умолчанию) — каждый оператор видит данные, зафиксированные к моменту его начала. Грязного чтения нет, но два SELECT в одной транзакции могут увидеть разное.
• Repeatable Read — изоляция снимков: вся транзакция видит один снимок. При попытке изменить строку, которую уже изменила параллельная транзакция, возникает ошибка сериализации. Фантомов нет, но перекос записи возможен.
• Serializable — сериализуемая изоляция снимков (SSI, с версии 9.1): поверх снимков СУБД отслеживает опасные зависимости чтения и записи и прерывает одну из транзакций, если результат не мог бы получиться при последовательном выполнении.^[3]
• Read Uncommitted ведёт себя как Read Committed.

Другие СУБД

• MySQL InnoDB по умолчанию использует Repeatable Read: обычные чтения идут по снимку, а блокирующие чтения и изменения — с блокировками промежутков индекса (gap locks), предотвращающими фантомы.^[4]
• Oracle по умолчанию — Read Committed; его уровень Serializable на деле является изоляцией снимков.
• SQLite выполняет записывающие транзакции по одной, поэтому фактически сериализуема.

Сравнение гарантий разных систем и формальные модели согласованности собраны проектом Jepsen.^[5]

Как выбирать и что делать

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call AND shift_id = 42;
UPDATE doctors SET on_call = false WHERE id = 7 AND shift_id = 42;
COMMIT;   -- может завершиться ошибкой 40001: could not serialize access

1. Read Committed подходит для большинства запросов, если изменения выражены атомарно: UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100 вместо «прочитать в приложении — посчитать — записать».
2. Явные блокировки для критичных мест: SELECT ... FOR UPDATE блокирует прочитанные строки до конца транзакции и предотвращает потерянное обновление.
3. Ограничения целостности — UNIQUE, CHECK, исключающие ограничения — защищают инварианты на любом уровне изоляции.
4. Serializable — когда правила затрагивают несколько строк и их трудно выразить ограничениями. Приложение обязано уметь повторять транзакцию при ошибке сериализации (код 40001) — это нормальная часть работы, а не авария.
5. Держите транзакции короткими: чем дольше транзакция, тем выше вероятность конфликтов.

Подробный разбор аномалий с примерами — в книге Мартина Клеппмана «Высоконагруженные приложения».^[6]

См. также

• Транзакция и ACID
• MVCC
• PostgreSQL
• Состояние гонки и блокировки
• Репликация баз данных

Источники

1. H. Berenson et al. — A Critique of ANSI SQL Isolation Levels (SIGMOD 1995)
2. PostgreSQL Documentation — Transaction Isolation
3. D. Ports, K. Grittner — Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)
4. MySQL — Transaction Isolation Levels (InnoDB)
5. Jepsen — Consistency Models
6. M. Kleppmann — Designing Data-Intensive Applications