Понятия в БД, часть 2. Уровни изоляции

Изоляция в ACID говорит: транзакция должна выполняется так, как будто других транзакций нет.
Единственный надёжный способ добиться этого — запускать транзакции последовательно. Это медленно, поэтому БД поддерживает менее строгие модели изоляции. База работает быстрее, но возможны аномалии данных.

В этом посте углубимся в детали: что за аномалии, что за уровни изоляции, и какие проблемы они решают.

Проблемы давно известны — dirty reads, write skews и тд. Чем больше проблем решает БД, тем больше кода нужно выполнить, и тем медленнее она работает. Уровни изоляции позволяют найти баланс между скоростью и корректностью.

В SQL стандарте их 4:

▫️ READ_UNCOMMITED
▫️ READ_COMMITED
▫️ REPEATABLE_READ
▫️ SERIALIZABLE

Каждый уровень гарантирует решение чёткого списка проблем. Остальные решаются либо в коде сервиса, либо никак (если проблема не актуальна).

В стандарте SQL три основные проблемы:

🔸 Dirty reads — грязные чтения

Транзакция 1 обновляет поле Х. Другие транзакции видят новое значения Х до того, как транзакция 1 завершится.

В вопросе с переводом денег как раз возникла такая ситуация. Транзакция перевода ещё не завершилась, а другая транзакция прочитала промежуточные значения.

Проблема возникает только на уровне READ_UNCOMMITED.

🔸 Nonrepeatable reads — неповторяющиеся чтения

Транзакция 2 читает поле X и работает с ним. В это время транзакция 3 обновляет поле Х. В итоге транзакция 2 работает с устаревшим значением.

Более формально, "неповторяющееся чтение" означает, что чтение одного поля в начале и конце транзакции даёт разные результаты. Но редко кто читает одно поле дважды, на практике получается либо бесполезная транзакция с устаревшими данными, либо несогласованные данные внутри транзакции.

Проблема остро проявляется для долгих запросов, например, бэкапов или аналитики. Решается на уровне REPEATABLE_READ и выше.

🔸 Фантомные чтения

Транзакция 3 проверяет условие по большому количеству записей. Транзакция 4 меняет выборку, например, добавляет новую запись. Если условие в транзакции 3 перестанет выполнятся, транзакция 3 этого не заметит.

Важно, что условие касается не одного поля, а многих. В этом разница с неповторяющимся чтением. Там меняется одно конкретное поле, а в фантомном чтении меняется вся выборка, по которому проверяется условие.

Проблема решается на уровне SERIALIZABLE.

Подробные примеры и схемы аномалий есть в Википедии. У многих проблем есть вариации, поэтому аномалий получается больше, чем уровней изоляции.

Каждая БД сама решает, какие проблемы и вариации на каких уровнях решать. У MS SQL 5 уровней изоляции, у Oracle 3. Многие NoSQL базы не поддерживают транзакции, поэтому для них указывать тип изоляции бессмысленно. В универсальных адаптерах типа JDBC, Hibernate и Spring Data уровней столько, сколько в стандарте — 4.

Ещё одна проблема, которой нет в SQL стандарте, но которая встречается на практике:

🔸 Потерянный апдейт

Транзакции работают с одними данными и не учитывают друг друга.

Пример: транзакция 5 и транзакция 6 одновременно прочитали значение счётчика. Каждая транзакция прибавила к значению единицу и обновила поле счётчика. Вначале они прочитали одно значение, и получается, что один инкремент потерялся.

Проблема решается не только уровнями изоляции, но и SQL конструкциями:

🔹 Атомарный апдейт:

UPDATE test SET x=x-1 where id=1;

🔹 Блокировка строки:

SELECT * FROM test WHERE id = 1 FOR UPDATE;

Итого. Как учитывать внутрянку БД в написании кода:

⭐️ Выбирать уровень изоляции с учётом вероятности и критичности проблем
⭐️ Уточнить в документации БД, какие проблемы решает выбранный уровень
⭐️ Писать код с учётом возможных аномалий
⭐️ Помнить о потерянных апдейтах