Уровни изоляции транзакций: ACID, аномалии, MVCC и SELECT FOR UPDATE

Коротко

• Изоляция — это компромисс между корректностью и параллелизмом. Чем строже уровень, тем больше блокировок, ожиданий или откатов.
• Стандарт SQL описывает уровни через три аномалии, но в реальных системах важнее ещё две: lost update и write skew.
• PostgreSQL по умолчанию — Read Committed, MySQL InnoDB — Repeatable Read. Одно и то же название уровня в них ведёт себя по-разному.
• MVCC даёт читателям снимок данных без блокировок: читатели не ждут писателей, писатели не ждут читателей.
• SELECT ... FOR UPDATE и атомарный UPDATE закрывают большинство гонок без перехода на Serializable.

ACID за минуту

• Atomicity — транзакция применяется целиком или не применяется вовсе. Упали посреди перевода денег — откат, а не списание без зачисления.
• Consistency — после транзакции соблюдены ограничения: внешние ключи, CHECK, уникальность, инварианты приложения. Это скорее обязанность разработчика, чем СУБД.
• Isolation — параллельные транзакции не видят промежуточных состояний друг друга. Насколько «не видят» — и определяет уровень изоляции.
• Durability — после COMMIT данные переживут падение процесса. Обычно за счёт журнала упреждающей записи (WAL в PostgreSQL, redo log в InnoDB), который сбрасывается на диск до ответа клиенту.

Буква C в ACID не имеет ничего общего с C в CAP — об этом подробно в статье про CAP-теорему. Здесь разбираем букву I.

Идеал: сериализуемость

Самая сильная гарантия — сериализуемость: результат параллельного выполнения такой же, как если бы транзакции шли строго по одной в каком-то порядке. Программист может думать, что он один в базе.

Выполнять транзакции действительно по одной дорого, поэтому СУБД предлагают уровни послабее. Каждый уровень разрешает определённые аномалии — ситуации, невозможные при последовательном выполнении.

Аномалии на примерах

Во всех примерах две транзакции, T1 и T2, работают с таблицей:

CREATE TABLE accounts (id int PRIMARY KEY, balance int);
INSERT INTO accounts VALUES (1, 100);

Время идёт сверху вниз. Все сценарии ниже прогнаны на PostgreSQL 16.

Dirty read — грязное чтение

T1 читает данные, которые T2 изменила, но ещё не зафиксировала. Если T2 откатится, T1 приняла решение на основе значения, которого никогда не существовало.

-- T2
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- T1 (Read Uncommitted)
SELECT balance FROM accounts WHERE id = 1;  -- 0 в MySQL, 100 в PostgreSQL
-- T2
ROLLBACK;

В PostgreSQL грязного чтения не бывает вообще: READ UNCOMMITTED принимается синтаксически, но работает как READ COMMITTED. В InnoDB на Read Uncommitted грязное чтение возможно.

Non-repeatable read — неповторяющееся чтение

T1 дважды читает одну строку и получает разные значения, потому что между чтениями T2 её изменила и зафиксировала.

-- T1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 100
-- T2
UPDATE accounts SET balance = 50 WHERE id = 1;  -- autocommit
-- T1
SELECT balance FROM accounts WHERE id = 1;  -- Read Committed: 50, Repeatable Read: 100
COMMIT;

Сам по себе это не баг, а свойство: на Read Committed каждый запрос видит последние зафиксированные данные. Проблема начинается, когда код считает, что прочитанное значение не изменится до конца транзакции. Например, отчёт: сумма по счетам, потом детализация — и цифры не сходятся.

Phantom — фантомное чтение

То же самое, но для множества строк: T1 повторяет запрос с условием, а T2 тем временем вставила строку, которая под это условие подходит.

-- T1
BEGIN;
SELECT count(*) FROM orders WHERE user_id = 1;  -- 2
-- T2
INSERT INTO orders (user_id, amount) VALUES (1, 30);
-- T1
SELECT count(*) FROM orders WHERE user_id = 1;  -- Read Committed: 3, Repeatable Read в PostgreSQL: 2

Заблокировать существующие строки недостаточно: новой строки в момент первого чтения ещё не было, блокировать нечего. Поэтому фантомы лечат либо снимками (MVCC), либо блокировкой диапазонов индекса.

Lost update — потерянное обновление

Классика read-modify-write. Обе транзакции читают баланс, считают новое значение в приложении и записывают его.

-- T1                                   -- T2
BEGIN;                                  BEGIN;
SELECT balance FROM accounts            SELECT balance FROM accounts
  WHERE id = 1;  -- 100                   WHERE id = 1;  -- 100
UPDATE accounts SET balance = 70
  WHERE id = 1;
COMMIT;
                                        UPDATE accounts SET balance = 50
                                          WHERE id = 1;
                                        COMMIT;
-- Итог: 50. Списание 30 пропало.

На Read Committed так и будет — в PostgreSQL, и в MySQL. На Repeatable Read в PostgreSQL второй UPDATE упадёт с ошибкой could not serialize access due to concurrent update (SQLSTATE 40001), и транзакцию нужно повторить. А вот InnoDB на Repeatable Read такой конфликт не обнаруживает: UPDATE работает с последней зафиксированной версией строки, и обновление тихо теряется.

Стандарт SQL lost update отдельно не называет. Его, как и write skew, ввели в обиход авторы статьи «A Critique of ANSI SQL Isolation Levels» (Berenson и соавторы, 1995), где показали, что определения стандарта неполны.

Write skew — косое чтение

Самая коварная аномалия. Каждая транзакция читает общие данные, проверяет инвариант и меняет разные строки. По отдельности обе корректны, вместе — нарушают инвариант.

Пример: в больнице должен дежурить хотя бы один врач. Сейчас дежурят двое, и оба одновременно просят снять их с дежурства.

CREATE TABLE doctors (name text PRIMARY KEY, on_call bool);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

-- T1 (alice)                           -- T2 (bob)
BEGIN ISOLATION LEVEL REPEATABLE READ;  BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors            SELECT count(*) FROM doctors
  WHERE on_call;  -- 2                    WHERE on_call;  -- 2
UPDATE doctors SET on_call = false      UPDATE doctors SET on_call = false
  WHERE name = 'alice';                   WHERE name = 'bob';
COMMIT;                                 COMMIT;
-- Итог: дежурных ноль.

Транзакции пишут в разные строки, конфликта записи нет, поэтому Repeatable Read его не видит. На Serializable в PostgreSQL второй COMMIT упадёт с could not serialize access due to read/write dependencies among transactions, и в базе останется один дежурный.

Write skew прячется в бронированиях («слот свободен?» → вставка), уникальных никнеймах без уникального индекса, лимитах расходов по нескольким счетам. Если проверка и запись касаются разных строк — подозревай его.

Уровни изоляции по стандарту и в жизни

Стандарт SQL-92 определяет четыре уровня через то, какие из трёх аномалий запрещены: Read Uncommitted разрешает всё, Read Committed запрещает грязное чтение, Repeatable Read — ещё и неповторяющееся, Serializable — ещё и фантомы. Это минимальные требования: СУБД вправе давать больше. И реальные системы реализуют уровни очень по-разному.

PostgreSQL

Важная деталь: снимок на Repeatable Read берётся не в момент BEGIN, а при первом запросе внутри транзакции.

Serializable в PostgreSQL не блокирует читателей, а работает оптимистично: транзакции идут параллельно, а при обнаружении опасного графа зависимостей одна откатывается. Отсюда главное правило — код обязан уметь повторять транзакцию.

MySQL InnoDB

В InnoDB два вида чтения, и это любимая тема собеседований:

• Consistent read — обычный SELECT. Читает из снимка MVCC, ничего не блокирует.
• Locking read (current read) — SELECT ... FOR UPDATE, SELECT ... FOR SHARE, а также UPDATE и DELETE. Читает последнюю зафиксированную версию, а не снимок, и блокирует строки.

Поэтому в одной транзакции на Repeatable Read обычный SELECT может вернуть 100, а SELECT ... FOR UPDATE сразу после него — 50. Фантомы для блокирующих чтений InnoDB предотвращает next-key блокировками: блокируется не только найденная запись индекса, но и промежуток перед ней, так что вставить подходящую строку в просканированный диапазон нельзя. Для поиска по уникальному индексу с точным условием блокируется только сама запись.

Serializable в InnoDB — пессимистичный, на блокировках. Транзакции чаще ждут друг друга и чаще ловят дедлоки, которые InnoDB обнаруживает и разрешает откатом одной из транзакций (ошибка 1213).

Сравнение в одной таблице

MVCC: как читатели не мешают писателям

MVCC (Multi-Version Concurrency Control) хранит несколько версий строки. Запись не перезаписывает строку на месте, а создаёт новую версию. Каждая транзакция читает ту версию, которая была актуальна для её снимка.

Снимок — это, по сути, ответ на вопрос «какие транзакции на момент его создания уже зафиксированы». Версия строки видна, если её создала зафиксированная транзакция из снимка и ещё не удалила такая же.

В PostgreSQL версии строк (кортежи) лежат прямо в таблице. У каждого кортежа есть скрытые поля xmin (кто создал) и xmax (кто удалил или заблокировал). Их можно увидеть:

INSERT INTO accounts VALUES (1, 100);
SELECT xmin, xmax, ctid, * FROM accounts;
--  xmin | xmax | ctid  | id | balance
--   815 |    0 | (0,1) |  1 |     100

UPDATE accounts SET balance = 70 WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM accounts;
--   816 |    0 | (0,2) |  1 |      70

UPDATE создал новый кортеж по адресу (0,2), а старый (0,1) остался на странице с xmax = 816. Когда ни одному снимку он не нужен, его убирает VACUUM (обычно autovacuum).

В InnoDB строка в кластерном индексе всегда последняя, а старые версии восстанавливаются по цепочке записей в undo log. Ненужный undo чистит фоновый поток purge.

Практическое следствие для обеих СУБД: долгие транзакции вредны. Транзакция, открытая час назад, держит снимок, и всё, что могло ей понадобиться, нельзя удалить. В PostgreSQL распухают таблицы и индексы, в MySQL — история undo. Типичный виновник — BEGIN в ORM, после которого код ушёл в HTTP-запрос к внешнему сервису.

MVCC решает конфликты «читатель–писатель». Конфликты «писатель–писатель» по-прежнему решаются блокировками строк: второй UPDATE той же строки ждёт, пока первая транзакция завершится.

Как бороться с гонками без Serializable

Атомарный UPDATE

Самое простое и самое недооценённое решение lost update — не читать значение в приложение, а посчитать его в базе:

UPDATE accounts
SET balance = balance - 30
WHERE id = 1 AND balance >= 30;
-- затронута 0 строк → денег не хватило

На Read Committed второй такой UPDATE подождёт первый, а затем пересчитает выражение уже от нового значения. Два параллельных списания 30 и 50 со счёта в 100 дают 20, как и должно.

SELECT ... FOR UPDATE

Когда логика сложнее одного выражения, строки блокируют явно — это пессимистичная блокировка:

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

Вторая транзакция с тем же FOR UPDATE встанет в очередь на этой строке и прочитает значение уже после первой. Этот же приём закрывает write skew с врачами: если обе транзакции выбирают дежурных FOR UPDATE, вторая дождётся первой. В PostgreSQL на Read Committed она перепроверит условие on_call на свежих версиях строк, увидит одного дежурного и откажет.

Write skew через вставку («слот свободен?» → INSERT) так не лечится: блокировать нечего, строки ещё нет. Варианты — уникальный индекс или exclusion constraint, отдельная строка-«замок» (материализация конфликта), Serializable.

Полезные модификаторы:

• FOR SHARE — разделяемая блокировка: другие тоже могут читать с FOR SHARE, но не менять.
• NOWAIT — не ждать, а сразу упасть, если строка занята.
• SKIP LOCKED — пропустить занятые строки. Готовая очередь задач на таблице:

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'new'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- обработать задачу
UPDATE jobs SET status = 'done' WHERE id = 1;
COMMIT;

Несколько воркеров разбирают задачи параллельно и не берут одну и ту же. SKIP LOCKED и NOWAIT есть в PostgreSQL с 9.5 и в MySQL с 8.0. Когда задач становится много, лучше переходить на настоящий брокер — см. очереди сообщений.

Блокировки в разном порядке ведут к дедлокам: T1 держит счёт 1 и ждёт счёт 2, T2 — наоборот. Обе СУБД это обнаружат и откатят одну транзакцию, но лучше не доводить: блокируйте строки в одном порядке, например по возрастанию id.

Оптимистичная блокировка

Без блокировок в базе: у строки есть поле version, и обновление проходит, только если версия не изменилась.

UPDATE documents
SET body = 'новый текст', version = version + 1
WHERE id = 7 AND version = 3;
-- 0 строк → кто-то успел раньше, перечитать и повторить

Подходит, когда конфликты редки, а между чтением и записью проходит много времени — например, пользователь редактирует документ в форме. Держать FOR UPDATE на минуты нельзя, а проверка версии ничего не держит.

Serializable и повтор транзакции

Если инвариантов много и все гонки не отследить, проще поднять уровень до Serializable и повторять транзакцию при ошибке сериализации. Повторять нужно всю транзакцию целиком, включая чтения, а не только упавший запрос:

import random
import time

import psycopg2
from psycopg2 import errors


def run_serializable(dsn, fn, retries=5):
    conn = psycopg2.connect(dsn)
    conn.set_session(isolation_level="SERIALIZABLE")
    try:
        for attempt in range(retries):
            try:
                with conn.cursor() as cur:
                    result = fn(cur)
                conn.commit()
                return result
            except (errors.SerializationFailure, errors.DeadlockDetected):
                conn.rollback()
                time.sleep(random.uniform(0, 0.05 * 2**attempt))
        raise RuntimeError("не удалось после ретраев")
    finally:
        conn.close()


def go_off_call(name):
    def body(cur):
        cur.execute("SELECT count(*) FROM doctors WHERE on_call")
        if cur.fetchone()[0] < 2:
            return False  # уйти нельзя: останется ноль дежурных
        cur.execute("UPDATE doctors SET on_call = false WHERE name = %s", (name,))
        return True

    return body

Две параллельные run_serializable(dsn, go_off_call("alice")) и go_off_call("bob") дают ровно одного снятого с дежурства. Внутри fn не должно быть внешних побочных эффектов — письма, платежа, запроса в другой сервис: при повторе они случатся дважды. Про то, как сделать повтор безопасным, — в статье об идемпотентности.

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

• Read Committed — разумное значение по умолчанию для веб-приложений. Гонки закрываются точечно: атомарными UPDATE, FOR UPDATE, уникальными индексами.
• Repeatable Read — для отчётов и выгрузок, которым нужен согласованный снимок нескольких таблиц. В PostgreSQL ещё и ловит lost update, но требует ретраев.
• Serializable — когда инвариантов много и цена ошибки высока: деньги, бронирования, складские остатки. Плата — ретраи и снижение пропускной способности при частых конфликтах.

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

Вопросы на собеседовании

• «Что такое ACID?» — Четыре буквы с примером на каждую. Уточни, что C — про ограничения, а I — про видимость параллельных изменений.
• «Какие уровни изоляции бывают и что они запрещают?» — Таблица из начала статьи плюс оговорка, что стандарт описывает минимум, а СУБД реализуют по-своему.
• «Чем non-repeatable read отличается от phantom?» — Первое про изменение уже прочитанной строки, второе про появление новых строк под условием. Блокировкой найденных строк лечится только первое.
• «Уровень по умолчанию в PostgreSQL и MySQL?» — Read Committed и Repeatable Read соответственно.
• «Два запроса одновременно списывают деньги со счёта. Что может пойти не так?» — Lost update. Решения: атомарный UPDATE ... SET balance = balance - x WHERE balance >= x, SELECT ... FOR UPDATE, оптимистичная блокировка через версию.
• «Что такое write skew и спасает ли от него Repeatable Read?» — Пример с дежурными врачами. Snapshot isolation не спасает, спасают Serializable, FOR UPDATE на читаемых строках или ограничение в схеме.
• «Как работает MVCC и почему долгие транзакции плохи?» — Версии строк и снимки; долгая транзакция мешает VACUUM/purge, растут таблицы и undo.
• «Чем пессимистичная блокировка отличается от оптимистичной?» — FOR UPDATE держит строку до конца транзакции; поле version ничего не держит, но конфликт обнаруживается при записи и требует повтора.
• «Что делать при ошибке serialization failure?» — Откатить и повторить всю транзакцию с паузой; побочные эффекты вынести за пределы транзакции.

На интервью по системному дизайну эта тема всплывает в задачах про платежи, бронирование и инвентарь. Хороший ответ звучит не как «поставим Serializable», а как «здесь возможна такая-то аномалия, закроем её вот этим, а вот здесь достаточно Read Committed». Базовые вопросы по SQL собраны в статье SQL на собеседовании, а про то, как ускорить сами запросы, — в разборе индексов.

Итог

Уровень изоляции — это список аномалий, с которыми ты согласен жить. Read Committed допускает почти всё, кроме грязного чтения. Repeatable Read в виде snapshot isolation даёт согласованный снимок, но пропускает write skew. Serializable закрывает всё, а платишь ретраями или блокировками. Названия уровней одинаковые, поведение разное: PostgreSQL на Repeatable Read ловит lost update, InnoDB — нет, зато блокирует диапазоны next-key блокировками. Большинство реальных гонок решаются не сменой уровня, а атомарным UPDATE, SELECT ... FOR UPDATE и правильными ограничениями в схеме.