Уровни изолированности SQL

Уровни изолированности транзакций SQL — это набор правил, определяющих степень видимости изменений, вносимых одной транзакцией, для других параллельно выполняющихся транзакций в системе управления базами данных (СУБД). Данные уровни регламентируют, какие аномалии одновременного доступа к данным (например, «грязное чтение», «неповторяющееся чтение», «фантомное чтение») допускаются в конкретной транзакции. Стандарт SQL-92 (ISO/IEC 9075) определяет четыре уровня изолированности: Read Uncommitted, Read Committed, Repeatable Read и Serializable. Выбор уровня изолированности является компромиссом между производительностью (чем ниже уровень, тем больше параллелизм) и целостностью данных.

История и стандартизация

Проблема согласованности данных при параллельном доступе возникла с появлением многопользовательских СУБД в 1970-х годах. В 1983 году Джим Грей в статье «The Transaction Concept: Virtues and Limitations» впервые формализовал понятие транзакции и её свойств ACID (атомарность, согласованность, изолированность, долговечность). Уровни изолированности как отдельная концепция были введены в 1992 году в стандарте SQL-92, который определил четыре основных уровня и три классические аномалии. В последующие годы стандарт уточнялся (SQL:1999, SQL:2003, SQL:2016), но базовая классификация осталась неизменной. В 2020-х годах исследователи и разработчики СУБД (например, PostgreSQL, MySQL, Microsoft SQL Server) предложили дополнительные уровни, такие как «Snapshot Isolation» (изоляция снимков) и «Serializable Snapshot Isolation», которые не входят в стандарт SQL, но широко применяются на практике.

Классификация аномалий

Аномалии параллельного доступа возникают, когда одна транзакция читает или изменяет данные, которые в этот момент модифицируются другой транзакцией. Стандарт SQL-92 выделяет три основных типа аномалий:

Грязное чтение (Dirty Read)

Ситуация, при которой транзакция A читает данные, изменённые транзакцией B, но ещё не зафиксированные (не завершённые командой COMMIT). Если транзакция B впоследствии откатится (ROLLBACK), данные, прочитанные транзакцией A, окажутся недействительными. Например, транзакция B увеличивает баланс счёта на 1000 рублей, транзакция A читает новый баланс (1000 рублей), а затем транзакция B откатывается — транзакция A остаётся с некорректными данными.

Неповторяющееся чтение (Non-Repeatable Read)

Ситуация, при которой транзакция A дважды читает одну и ту же строку в рамках одного выполнения, но между этими чтениями транзакция B изменяет и фиксирует эту строку. В результате первое и второе чтение дают разные значения. Например, транзакция A читает цену товара (100 рублей), затем транзакция B меняет цену на 120 рублей и фиксирует изменение, после чего транзакция A снова читает ту же строку и получает 120 рублей.

Фантомное чтение (Phantom Read)

Ситуация, при которой транзакция A выполняет запрос, возвращающий набор строк, удовлетворяющих условию (например, SELECT * FROM orders WHERE amount > 100). Между повторными выполнениями этого запроса транзакция B вставляет или удаляет строки, которые соответствуют условию, и фиксирует изменения. В результате при повторном запросе транзакция A получает другой набор строк (появляются «фантомы»). Фантомное чтение отличается от неповторяющегося тем, что затрагивает не изменение существующих строк, а добавление или удаление строк, меняющих состав результирующего набора.

Уровни изолированности

Стандарт SQL-92 определяет четыре уровня, каждый из которых предотвращает определённый набор аномалий. Уровни перечислены от самого низкого (наименьшая изоляция, наибольшая производительность) к самому высокому (максимальная изоляция, наименьшая производительность).

Read Uncommitted (чтение незафиксированных данных)

Самый низкий уровень изолированности. Транзакция может читать данные, изменённые другими транзакциями, даже если те ещё не зафиксированы. На этом уровне допускаются все три классические аномалии: грязное чтение, неповторяющееся чтение и фантомное чтение. На практике Read Uncommitted используется редко, так как целостность данных не гарантируется. В некоторых СУБД (например, PostgreSQL) этот уровень реализован как Read Committed из-за архитектурных ограничений. Применяется в системах, где допустимы временные неточности, например, в мониторинге или сборе статистики.

Read Committed (чтение зафиксированных данных)

Уровень, при котором транзакция видит только те изменения, которые были зафиксированы (COMMIT) другими транзакциями на момент выполнения запроса. Грязное чтение предотвращается, но неповторяющееся и фантомное чтение возможны. Это уровень по умолчанию в большинстве современных СУБД, включая PostgreSQL, Oracle, Microsoft SQL Server (в некоторых конфигурациях) и MySQL (с движком InnoDB). Read Committed обеспечивает баланс между производительностью и согласованностью, поэтому он наиболее распространён в веб-приложениях, интернет-магазинах и системах управления контентом.

Repeatable Read (повторяемое чтение)

Уровень, гарантирующий, что если транзакция прочитала строку, то при повторном чтении той же строки в рамках этой транзакции она получит то же значение, даже если другие транзакции изменят и зафиксируют эту строку. Неповторяющееся чтение предотвращается, но фантомное чтение остаётся возможным (если СУБД не использует блокировки диапазонов). Грязное чтение также предотвращается. Repeatable Read часто реализуется через блокировку прочитанных строк или через механизм многоверсионности (MVCC). Этот уровень применяется в финансовых системах, где требуется согласованность данных в рамках одной транзакции, например, при расчёте баланса или формировании отчётов.

Serializable (сериализуемый)

Самый строгий уровень изолированности. Транзакции выполняются так, как если бы они были последовательными (одна за другой), даже если на самом деле они выполняются параллельно. Предотвращаются все три классические аномалии: грязное чтение, неповторяющееся чтение и фантомное чтение. Serializable достигается либо полной блокировкой таблиц и диапазонов строк, либо использованием механизма сериализуемой изоляции снимков (SSI), как в PostgreSQL. Этот уровень гарантирует максимальную целостность данных, но значительно снижает производительность и увеличивает вероятность взаимоблокировок (deadlocks). Применяется в критически важных системах, таких как банковские расчёты, системы бронирования авиабилетов и управление запасами.

Реализация в различных СУБД

Хотя стандарт SQL определяет четыре уровня, конкретные реализации в СУБД могут отличаться из-за архитектурных особенностей.

PostgreSQL

PostgreSQL по умолчанию использует Read Committed. Уровень Read Uncommitted в PostgreSQL ведёт себя как Read Committed, так как грязное чтение архитектурно невозможно. Уровень Repeatable Read реализован через многоверсионность (MVCC) и предотвращает не только неповторяющееся, но и фантомное чтение (в отличие от стандарта). Serializable в PostgreSQL реализован через Serializable Snapshot Isolation (SSI) и обеспечивает полную сериализуемость.

MySQL (InnoDB)

MySQL с движком InnoDB по умолчанию использует Repeatable Read. Read Uncommitted допускает грязное чтение, но на практике встречается редко. Read Committed и Repeatable Read реализованы через MVCC. Serializable реализуется через блокировки на уровне строк и таблиц, что может приводить к снижению производительности.

Microsoft SQL Server

По умолчанию в SQL Server используется Read Committed. Уровень Read Uncommitted допускает грязное чтение. Repeatable Read и Serializable реализуются через блокировки, что может вызывать взаимоблокировки. Также SQL Server поддерживает изоляцию снимков (Snapshot Isolation) как дополнительный уровень, не входящий в стандарт SQL.

Oracle

Oracle по умолчанию использует Read Committed, но реализует его через MVCC, что предотвращает неповторяющееся чтение на уровне запросов. Уровень Serializable в Oracle также основан на MVCC и предотвращает фантомное чтение, но может приводить к ошибкам сериализации при конфликтах.

Критика и ограничения

Стандартные уровни изолированности имеют ряд недостатков. Во-первых, они не охватывают все возможные аномалии. Например, аномалия «потерянное обновление» (lost update) — когда две транзакции одновременно читают и изменяют одну строку, и одно изменение теряется — не предотвращается на уровне Read Committed и Repeatable Read, если не используются явные блокировки. Во-вторых, реализация уровней в разных СУБД может существенно различаться, что создаёт проблемы при переносе приложений между системами. В-третьих, уровень Serializable, хотя и гарантирует максимальную изоляцию, часто приводит к снижению производительности и требует тщательного проектирования запросов. В результате на практике разработчики часто выбирают Read Committed или Repeatable Read, а для критических операций используют явные блокировки (SELECT ... FOR UPDATE) или оптимистическую блокировку.

Источники

1. ISO/IEC 9075:1992, Information technology — Database languages — SQL.
2. Gray, J. (1983). The Transaction Concept: Virtues and Limitations. Proceedings of the 7th International Conference on Very Large Data Bases.
3. Berenson, H., Bernstein, P., Gray, J., Melton, J., O'Neil, E., & O'Neil, P. (1995). A Critique of ANSI SQL Isolation Levels. Proceedings of the 1995 ACM SIGMOD International Conference on Management of Data.
4. PostgreSQL Documentation. Chapter 13: Concurrency Control.
5. MySQL Documentation. InnoDB Transaction Model and Locking.
6. Microsoft SQL Server Documentation. Transaction Isolation Levels.
7. Oracle Database Concepts. Data Concurrency and Consistency.