Уровни изоляции транзакций: от dirty read до serializable

Коротко

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

По умолчанию PostgreSQL использует Read Committed, MySQL InnoDB — Repeatable Read.

Четыре аномалии

В примерах две сессии, A и B, и таблица accounts.

Грязное чтение (dirty read): A читает данные, которые B изменила, но ещё не закоммитила. Если B откатится, A приняла решение по значению, которого никогда не было.

-- B
BEGIN; UPDATE accounts SET balance = 0 WHERE id = 1;
-- A (Read Uncommitted в MySQL) видит balance = 0
-- B
ROLLBACK;

Неповторяющееся чтение: A дважды читает строку внутри одной транзакции и получает разные значения, потому что между чтениями B закоммитила изменение.

Фантомное чтение: A дважды выполняет один и тот же запрос с WHERE и получает разный набор строк, потому что B вставила или удалила подходящие строки.

Потерянное обновление (lost update): обе транзакции читают одно значение, вычисляют новое в приложении и записывают. Вторая запись молча затирает первую.

-- A и B прочитали balance = 100
-- A пишет 100 + 50 = 150, коммитит
-- B пишет 100 - 30 = 70, коммитит  -> +50 от A потеряно

Как MVCC и уровни изоляции их предотвращают

PostgreSQL и InnoDB используют MVCC (многоверсионность): обновление создаёт новую версию строки, а читатели видят снимок (snapshot) и не ждут писателей. Уровень изоляции определяет, как часто берётся снимок.

• Read Committed: новый снимок на каждый запрос. Незакоммиченные данные не видны, но два запроса в одной транзакции могут увидеть разные состояния.
• Repeatable Read: один снимок на всю транзакцию. Повторное чтение возвращает те же данные. В PostgreSQL этот уровень скрывает и фантомы, а при попытке обновить строку, изменённую параллельной закоммиченной транзакцией, выдаёт ошибку сериализации вместо потери обновления. В InnoDB обычное чтение идёт по снимку, а блокирующие чтения и обновления работают с последней версией и используют gap-блокировки против фантомов.
• Serializable: результат должен совпадать с каким-то последовательным порядком. PostgreSQL использует Serializable Snapshot Isolation и откатывает транзакцию, если видит опасный паттерн, например write skew. MySQL добивается этого в основном дополнительными блокировками.
• Read Uncommitted: в PostgreSQL ведёт себя как Read Committed, грязного чтения там нет.

Главное следствие: на Repeatable Read и Serializable код обязан повторять транзакции, упавшие с ошибкой сериализации (SQLSTATE 40001).

SELECT FOR UPDATE и атомарные обновления

Большинство реальных багов — потерянные обновления в коде «прочитал-изменил-записал» на уровне Read Committed. Есть три практичных решения.

1. Атомарный UPDATE, если логика помещается в SQL:

UPDATE accounts SET balance = balance - 30
WHERE id = 1 AND balance >= 30;

2. Пессимистичная блокировка через SELECT ... FOR UPDATE — строка заблокирована до коммита:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- приложение проверяет и считает
UPDATE accounts SET balance = 70 WHERE id = 1;
COMMIT;

Другие транзакции, которые пытаются заблокировать или обновить ту же строку, ждут. Варианты: FOR UPDATE NOWAIT сразу выдаёт ошибку, FOR UPDATE SKIP LOCKED пропускает занятые строки — удобно для очередей задач.

3. Оптимистичная блокировка через колонку версии:

UPDATE orders SET status = 'paid', version = version + 1
WHERE id = 42 AND version = 7;
-- обновлено 0 строк -> кто-то успел раньше, перечитайте и повторите

Как выбрать

• Read Committed плюс атомарные обновления или FOR UPDATE закрывают потребности большинства веб-приложений.
• Repeatable Read подходит для согласованных отчётов и многошаговых чтений в одной транзакции.
• Serializable — для сложных инвариантов на несколько строк (например, «на смене всегда хотя бы один врач»), если есть логика повторов.
• Держите транзакции короткими: длинные дольше удерживают блокировки и старые версии строк.