Уровни изоляции транзакций SQL: аномалии и выбор

Когда сотни транзакций работают с базой одновременно, возникает вопрос: насколько они видят промежуточные результаты друг друга. Полная изоляция гарантирует корректность, но убивает производительность; слабая изоляция быстра, но допускает аномалии. Стандарт ANSI SQL решает этот компромисс через четыре уровня изоляции транзакций - от самого слабого READ UNCOMMITTED до строгого SERIALIZABLE. Ниже разберём, какие именно аномалии допускает каждый уровень и как осознанно выбрать нужный в конкретной СУБД.

Зачем нужны уровни изоляции

Изоляция - буква I в наборе свойств ACID. В идеальном мире каждая транзакция выполнялась бы так, будто она в системе одна: это называют сериализуемостью. Но если буквально выстраивать транзакции в очередь, параллелизм пропадает, а с ним и пропускная способность сервера.

Поэтому стандарт SQL вводит градацию: разработчик сам решает, чем пожертвовать. Чем выше уровень изоляции, тем меньше аномалий он допускает, но тем дороже обходится блокировками или версионированием строк. Чем ниже - тем выше параллелизм, но тем больше странных эффектов придётся учитывать в коде приложения.

Рассчитать для своей задачи

Формально уровень изоляции задаётся перед началом транзакции:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
  -- запросы
COMMIT;

Три аномалии параллелизма

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

Грязное чтение (dirty read). Транзакция $T_2$ читает строку, которую $T_1$ изменила, но ещё не зафиксировала. Если затем $T_1$ откатится по ROLLBACK, то $T_2$ оперирует данными, которых никогда «официально» не существовало.

Неповторяемое чтение (non-repeatable read). $T_2$ читает одну и ту же строку дважды. Между чтениями $T_1$ изменяет эту строку и фиксирует COMMIT. Два чтения внутри одной транзакции $T_2$ дают разные значения, хотя сама $T_2$ строку не трогала.

Фантомное чтение (phantom read). $T_2$ дважды выполняет запрос с условием WHERE, например «все заказы дороже 1000». Между запросами $T_1$ вставляет новую строку, попадающую под условие. Второй запрос возвращает строку-«фантом», которой не было в первом.

Четыре уровня по стандарту ANSI

Каждый уровень изоляции описывается тем, какие из трёх аномалий он ещё допускает. Чем выше уровень, тем меньше допущено.

READ UNCOMMITTED - самый слабый. Транзакция видит чужие незафиксированные изменения. На практике почти не используется: цена ошибки выше выигрыша в скорости.

READ COMMITTED - видны только зафиксированные данные, грязное чтение исключено. Это уровень по умолчанию в PostgreSQL, Oracle и SQL Server. Каждый оператор внутри транзакции видит свежий снимок, поэтому повторное чтение может дать другой результат.

REPEATABLE READ - в течение транзакции одна и та же строка читается одинаково. Это уровень по умолчанию в MySQL (InnoDB). Защищает от неповторяемого чтения, но классический стандарт всё ещё допускает фантомы.

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

Рассчитать для своей задачи

Как это устроено в PostgreSQL и MySQL

Стандарт описывает поведение, а не реализацию, поэтому разные СУБД достигают одних и тех же гарантий по-разному. Исторически уровни реализовывались блокировками: чтобы запретить грязное чтение, на строку ставили блокировку до конца транзакции. Современные СУБД чаще используют MVCC - многоверсионное управление параллелизмом (Multiversion Concurrency Control).

При MVCC каждая транзакция работает со снимком данных на определённый момент времени. Вместо того чтобы блокировать строку, СУБД хранит несколько её версий: читатели видят старую согласованную версию, писатель создаёт новую. Так чтение не блокирует запись, а запись не блокирует чтение.

В PostgreSQL уровень REPEATABLE READ реализован через снимок на момент старта транзакции (snapshot isolation) и за счёт этого попутно избавляет от фантомов - строже, чем требует стандарт. Уровень SERIALIZABLE добавляет механизм SSI (Serializable Snapshot Isolation), который отслеживает опасные пересечения и при необходимости откатывает одну из транзакций с ошибкой сериализации.

В MySQL InnoDB фантомы на REPEATABLE READ блокируются через next-key locks - блокировки не только строк, но и промежутков между ними (gap locks), что мешает вставке «фантомных» строк под условие запроса.

Аномалия записи и потерянное обновление

Помимо трёх классических аномалий чтения существует ещё две, важные для практики.

Потерянное обновление (lost update). Две транзакции читают одно значение счётчика, обе прибавляют единицу и записывают результат. Вместо $+2$ получается $+1$ - одно обновление потеряно. Защищает от него SELECT ... FOR UPDATE или атомарный UPDATE counter = counter + 1.

Аномалия записи (write skew). Каждая транзакция читает данные, проверяет инвариант и пишет в свою строку. По отдельности каждая корректна, но вместе они нарушают общий инвариант. Классический пример - правило «хотя бы один врач на дежурстве»: два врача одновременно снимают себя с дежурства, каждый видит, что второй ещё на месте. От write skew защищает только SERIALIZABLE.

$$\text{уровень изоляции} \uparrow \;\Rightarrow\; \text{аномалий} \downarrow,\; \text{накладные расходы} \uparrow$$

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

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

При SERIALIZABLE будьте готовы повторять транзакции: СУБД может откатить одну из конфликтующих с ошибкой сериализации, и приложение должно её перезапустить. Это нормальная часть паттерна, а не баг.

Частые ошибки

• Считать READ COMMITTED достаточным для счётчиков. Без FOR UPDATE или атомарного инкремента возникает потерянное обновление даже на этом уровне.
• Путать уровни по умолчанию. Код, протестированный на MySQL (REPEATABLE READ), может вести себя иначе на PostgreSQL (READ COMMITTED).
• Думать, что SERIALIZABLE всегда блокирует. В PostgreSQL он не блокирует, а откатывает конфликтующие транзакции - нужен retry-цикл в приложении.
• Игнорировать write skew. Эта аномалия не входит в три классические и не предотвращается уровнем REPEATABLE READ.
• Ставить SERIALIZABLE «на всякий случай». Лишние откаты и блокировки убивают пропускную способность под нагрузкой.

FAQ

Какой уровень изоляции стоит по умолчанию? Зависит от СУБД: PostgreSQL, Oracle и SQL Server используют READ COMMITTED, MySQL (InnoDB) - REPEATABLE READ. Стандарт ANSI не предписывает конкретный уровень по умолчанию.

Чем отличается REPEATABLE READ от SERIALIZABLE? REPEATABLE READ гарантирует одинаковое чтение существующих строк, но по стандарту допускает фантомы и write skew. SERIALIZABLE исключает все аномалии, делая результат эквивалентным последовательному исполнению, ценой откатов при конфликтах.

Что такое MVCC и как оно связано с изоляцией? MVCC (многоверсионное управление параллелизмом) хранит несколько версий строки, чтобы читатели видели согласованный снимок без блокировки писателей. Это способ реализовать уровни изоляции без блокировок на чтение, используемый в PostgreSQL и MySQL InnoDB.

Коротко

Уровни изоляции транзакций SQL - это компромисс между корректностью и скоростью. Стандарт ANSI определяет четыре уровня через набор допустимых аномалий: READ UNCOMMITTED допускает всё, READ COMMITTED убирает грязное чтение, REPEATABLE READ добавляет защиту от неповторяемого чтения, SERIALIZABLE исключает все аномалии. Реализуются они блокировками или MVCC; уровни по умолчанию в PostgreSQL и MySQL различаются. На практике выбирайте минимальный уровень, при котором логика остаётся корректной.