Транзакции — не про «магическое ACID», а про конкретную механику согласованного доступа к данным под нагрузкой.

Эта статья объясняет как реально работают уровни изоляции и чем отличаются популярные СУБД на практике.

Мы разберём:

• базовые и расширенные уровни (ANSI SQL-92 и вне стандарта),

• MVCC, snapshot isolation и serializable snapshot isolation,

• аномалии (dirty read, non-repeatable, phantom, lost update, out-of-order read, write skew),

• переключения уровня в коде и «пересечение» разных уровней между конкурентными транзакциями.

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

Isolation говорит, какие эффекты параллельности допустимы. Стандарт SQL-92 определяет 4 уровня:

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

Но современные СУБД используют MVCC (многоверсионность) и/или блокировки; поверх этого появились Snapshot Isolation (SI) и Serializable Snapshot Isolation (SSI). Формально названия те же, но поведение различается между СУБД.

Аномалии конкурентного доступа (к чему вообще все эти уровни)

Как разные СУБД реализуют изоляцию

• PostgreSQL — чистый MVCC. READ COMMITTED и REPEATABLE READ — снимки (statement/transaction-level). SERIALIZABLE реализован как SSI (граф зависимостей; возможны откаты конфликтов).

• MySQL/InnoDB — MVCC + next-key locks (диапазонные блокировки). По умолчанию REPEATABLE READ (снимок транзакции + защита от большинства фантомов при «locking reads»).

• Oracle — исторически «консистентное чтение» (undo) со statement-level snapshot на READ COMMITTED, SERIALIZABLE близок к SI с конфликтными откатами.

• SQL Server — по умолчанию блокировки (READ COMMITTED с read committed locking). Дополнительно есть RCSI (версионирование на уровне READ COMMITTED) и SNAPSHOT (transaction-level snapshot). SERIALIZABLE — через key-range locks.

• IBM DB2 — уровни вне стандарта: Cursor Stability (CS), Read Stability (RS), Repeatable Read (RR), Uncommitted Read (UR); гибкие блокировки курсоров и диапазонов.

Сводная таблица (анти-анатомия аномалий)

Условные обозначения: ✅ возможно; ❌ предотвращено; ⚠️ зависит от вида запроса/блокировки/реализации.

Примечания:

• Lost Update почти всегда предотвращается явными блокировками FOR UPDATE/SELECT ... FOR SHARE/UPDATE или оптимистичным контролем версий (WHERE version=...). Без этого — может случиться даже при «высоких» режимах.

• Write Skew — классическая «дырка» SI. Устраняется SSI (PG) или явными инвариантными блокировками (диапазоны, уникальные индексы, триггеры-проверки, advisory locks).

MVCC простыми словами

MVCC хранит несколько версий строк. Запрос видит те версии, которые «видимы» в его снимке.

Это позволяет читать без блокировок писателей, а писателям не мешать читателям.

Snapshot Isolation и почему он не «серилизуем»

Snapshot Isolation (SI) — транзакция видит «фото» базы на момент BEGIN. Это устраняет dirty/non-repeatable/phantom, но допускает write skew.

Инвариант: «всегда хотя бы один дежурный врач».

-- A:
BEGIN (SI)
SELECT count(*) FROM doctors WHERE on_duty = true; -- → 2
-- (A считает, что все хорошо)
UPDATE doctors SET on_duty = false WHERE id = 1;

-- B:
BEGIN (SI)
SELECT count(*) FROM doctors WHERE on_duty = true; -- → 2 (тот же снимок)
UPDATE doctors SET on_duty = false WHERE id = 2;

-- Коммиты:
A COMMIT; B COMMIT;  -- инвариант нарушен, оба ушли

Таймлайн

t0: BEGIN A (snapshot S0)
t1: BEGIN B (snapshot S0)
t2: A проверяет инвариант (видит двоих)
t3: B проверяет инвариант (видит двоих)
t4: A снимает 1-го
t5: B снимает 2-го
t6: A COMMIT
t7: B COMMIT    -> инвариант нарушен

Вывод: SI не обеспечивает сериализуемость. Нужен SSI (PG) или явные блокировки на диапазоны/инварианты.

Serializable Snapshot Isolation (SSI) в PostgreSQL

SSI отслеживает граф зависимостей между транзакциями.

Когда возникает потенциальный цикл (указывающий на несериализуемость), одна из транзакций откатывается (ERROR: could not serialize access due to read/write dependencies).

Практический эффект: вы часто получаете производительность близкую к SI, но с гарантиями SERIALIZABLE. Цена — редкие откаты, которые код обязан уметь повторить.

Расширенные уровни вне стандарта: Read Stability и Cursor Stability (DB2)

• Cursor Stability (CS) — защищает «текущую» строку под курсором от изменений другими, но не удерживает диапазоны. Лёгкая изоляция для OLTP.

• Read Stability (RS) — гарантирует, что строки, которые вы уже прочли, не изменятся до конца транзакции (но фантомы возможны). Хорошо для отчётности без тяжёлых диапазонных блокировок.

Они исторически важны и помогают понять компромиссы «произв-ть vs изоляция».

Реальные сценарии: какую изоляцию брать

1) Финансовые переводы (пересылка баланса, двойной списание)

• PostgreSQL: SERIALIZABLE или READ COMMITTED + SELECT ... FOR UPDATE на вовлечённых счетах, либо оптимистичные версии.

• MySQL: REPEATABLE READ + SELECT ... FOR UPDATE / UPDATE ... WHERE ... по индексам → задействуются next-key locks.

• Oracle: SERIALIZABLE (ловите ORA-08177 и ретраи) или строгое блокирование нужных строк.

• SQL Server: SERIALIZABLE (key-range) или SNAPSHOT/RCSI + блокировки при записи.

2) Отчётность и аналитика (стабильный срез данных)

• PostgreSQL: REPEATABLE READ (SI на транзакцию) — идеально для «одной транзакции отчёта».

• MySQL: при REPEATABLE READ — транзакционный снимок, но помните, что plain SELECT — это «consistent read», а не блокирующий диапазон.

• Oracle: READ COMMITTED уже даёт statement-snapshot, но для сложных отчётов лучше SERIALIZABLE.

• SQL Server: включите SNAPSHOT для базы, отчёты в одной транзакции.

3) Конкурентные обновления, борьба с lost update

• Везде: либо пессимистические блокировки (SELECT ... FOR UPDATE/UPDATE ... по индексу), либо оптимистичный контроль (версионирование row_version, xmin, WHERE id=? AND version=?).

• PG: в RC/RR используйте FOR UPDATE на критичных сущностях.

• MySQL: InnoDB сам «продержит» диапазоны при locking-read; без него — легко поймать гонку.

• Oracle/SQL Server: стандартные блокировки/версии.

Non-Repeatable Read (на RC)

A: BEGIN (READ COMMITTED)
A: SELECT balance FROM accounts WHERE id=1  -- → 100

B: BEGIN
B: UPDATE accounts SET balance=50 WHERE id=1
B: COMMIT

A: SELECT balance FROM accounts WHERE id=1  -- → 50 (другой результат)
A: COMMIT

Как избежать: REPEATABLE READ/SNAPSHOT или пессимистичная блокировка строки в A.

Phantom Read (на RC, без блокировок диапазонов)

A: BEGIN (READ COMMITTED)
A: SELECT count(*) FROM orders WHERE amount > 1000  -- → 3

B: BEGIN
B: INSERT INTO orders(amount) VALUES(1500)
B: COMMIT

A: SELECT count(*) FROM orders WHERE amount > 1000  -- → 4 (фантом)
A: COMMIT

Как избежать: REPEATABLE READ/SNAPSHOT (в PG/MySQL для plain SELECT’ов) или key-range/next-key locks (locking-read).

Lost Update (без блокировок/версий)

A: BEGIN
A: SELECT qty FROM stock WHERE id=1  -- → 10

B: BEGIN
B: SELECT qty FROM stock WHERE id=1  -- → 10

A: UPDATE stock SET qty=9 WHERE id=1  -- −1
B: UPDATE stock SET qty=8 WHERE id=1  -- −2 (перетёр)
A: COMMIT
B: COMMIT

Как избежать:

• Пессимистично: SELECT ... FOR UPDATE в A и B.

• Оптимистично: UPDATE ... SET qty=? , version=version+1 WHERE id=? AND version=? и проверка rowcount.

Как «пересекаются» разные уровни между сессиями

Когда одна транзакция идёт в SERIALIZABLE, а другая — в READ COMMITTED, что будет?

• PostgreSQL (SSI): возможен откат SERIALIZABLE-транзакции «по вине» читающих/пишущих в RC, если образуется предсериализационный цикл. Готовьте ретраи.

• MySQL: если одна сессия делает locking-read/UPDATE, вторая в RC может блокироваться на тех же индексных диапазонах.

• SQL Server: SERIALIZABLE применит key-range locks, «душа» конкурента в RC.

• Oracle: при SERIALIZABLE возможны ошибки сериализации у «опоздавших» транзакций.

Практика: установки уровней изоляции в PostgreSQL (SQL и Python)

SQL (psql)

-- Уровень по умолчанию для сессии
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Для конкретной транзакции
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- работа...
COMMIT;

Python (psycopg2: уровень на соединение и на транзакцию)

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED, ISOLATION_LEVEL_SERIALIZABLE

conn = psycopg2.connect("dbname=app user=app password=secret host=localhost")
conn.set_session(isolation_level=ISOLATION_LEVEL_READ_COMMITTED, autocommit=False)

with conn:
    with conn.cursor() as cur:
        # Повысим уровень только для этой транзакции
        cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
        cur.execute("SELECT balance FROM accounts WHERE id=%s", (1,))
        bal, = cur.fetchone()
        # ... логика перевода ...
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id=%s", (100, 1))

Python (asyncpg: явная установка уровня)

import asyncio
import asyncpg

async def run():
    conn = await asyncpg.connect("postgres://app:secret@localhost/app")
    # Уровень по умолчанию — READ COMMITTED (в PG)
    async with conn.transaction(isolation='serializable'):
        bal = await conn.fetchval("SELECT balance FROM accounts WHERE id=$1", 1)
        # ... логика ...
        await conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id=$1", 1)
    await conn.close()

asyncio.run(run())

Замечание про SERIALIZABLE (PG/SSI): обязательно ловите исключение сериализации и делайте ретрай.

Пример (упрощённо для psycopg2):

import time
import psycopg2
from psycopg2.errors import SerializationFailure

def transfer(cur, from_id, to_id, amount):
    cur.execute("SELECT balance FROM accounts WHERE id=%s FOR UPDATE", (from_id,))
    bal_from, = cur.fetchone()
    if bal_from < amount:
        raise ValueError("Insufficient funds")
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id=%s", (amount, from_id))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id=%s", (amount, to_id))

def with_retry(conn, fn, max_retries=5):
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    fn(cur)
            return
        except SerializationFailure:
            time.sleep(0.05 * (attempt + 1))
    raise RuntimeError("Too many serialization failures")

Практика: MySQL/InnoDB (о «locking reads» и next-key locks)

Plain SELECT в InnoDB — «consistent read» из Undo (MVCC).

Для борьбы с фантомами/гонками при модификациях используйте locking read:

-- Блокируем прочитанные строки (и диапазоны при наличии индекса) до конца транзакции
SELECT * FROM orders WHERE amount > 1000 FOR UPDATE;

-- Альтернатива мягче:
SELECT * FROM orders WHERE amount > 1000 LOCK IN SHARE MODE; -- (MySQL<8.0: FOR SHARE)

Важно: чтобы диапазон «держался», нужен индекс под условие. Иначе диапазонные блокировки будут «широкими» или неэффективными.

Практика: SQL Server (RCSI/SNAPSHOT)

Включить READ_COMMITTED_SNAPSHOT (statement-snapshot) для базы:

ALTER DATABASE AppDB SET READ_COMMITTED_SNAPSHOT ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
-- ...
COMMIT;

SERIALIZABLE — через key-range locks. Готовьтесь к блокировкам на индексных диапазонах.

Практика: Oracle (statement-snapshot и SERIALIZABLE)

Oracle по умолчанию читает «как было на момент начала запроса» (undo).

SERIALIZABLE — снимок на транзакцию с возможными конфликтными ошибками (ORA-08177), которые нужно повторять.

Как выбирать уровень: инженерные рекомендации

• По умолчанию (OLTP, веб-запросы):

  • PostgreSQL: READ COMMITTED + локальные блокировки там, где важны инварианты (FOR UPDATE) или оптимистичная версия.

  • MySQL: REPEATABLE READ (дефолт) + locking reads для критичных последовательностей.

  • SQL Server: рассмотрите RCSI для снижения блокировок чтения.

  • Oracle: READ COMMITTED достаточно для большинства CRUD.

• Отчёты/долгие аналитические транзакции:

  • PostgreSQL: REPEATABLE READ (SI) — устойчивый срез на время транзакции.

  • SQL Server: SNAPSHOT.

  • Oracle: иногда проще разбить отчёт на шаги под READ COMMITTED; для «строгого» — SERIALIZABLE.

• Денежные переводы/взаиморасчёты/инварианты:

  • PostgreSQL: SERIALIZABLE (SSI) или строгие блокировки по ключам/диапазонам + инвариант в БД (уникальные индексы, CHECK + триггер).

  • MySQL/SQL Server/Oracle: используйте пессимистичные блокировки на целевых строках/диапазонах.

  • Всегда закладывайте ретраи при SERIALIZABLE/SSI/SI.

• Высокая конкуренция и масштабирование:

  • Вынесите инварианты в единую точку синхронизации: очередь, сервис-аггрегатор, «ledger-таблицу» с монотонной нумерацией, advisory locks.

  • Применяйте идемпотентность записей и «outbox»-паттерн.

Подводные камни PostgreSQL (которые часто упускают)

1. REPEATABLE READ в PG = SI, фантомов нет, но есть write skew.

2. SERIALIZABLE (SSI) может откатывать транзакции — без ретраев получите «случайные» ошибки в проде.

3. SELECT ... FOR UPDATE блокирует конкретные строки. Чтобы заблокировать диапазон, нужен «якорь»: индекс + предикатный лок (или техника «range-guard» через вспомогательные строки/уникальные ключи).

4. Долгие транзакции задерживают vacuum (видимость старых версий), что может приводить к росту таблиц и bloat.

Где вставить диаграммы (и что на них показать)

1. [Диаграмма: MVCC timeline]

   • Оси: время; подписи транзакций A/B; версии строк v1, v2 с xmin/xmax.

   • Показать, почему A видит v1, а B — v2.

2. [Диаграмма: Write Skew]

   • Две параллельные транзакции на одном снимке; проверка инварианта; итоговое нарушение.

   • Отдельный кадр — SSI с откатом одной транзакции.

3. [Диаграмма: Next-Key Locks (InnoDB)]

   • Индексированные ключи и «полуинтервалы» под блокировкой.

   • Отличие plain SELECT от locking-read.

4. [Диаграмма: Key-Range Locks (SQL Server SERIALIZABLE)]

   • Как удерживается диапазон (предотвращая фантомы).

Промпты для генерации диаграмм (скопируй в свой графический/AI-инструмент)

MVCC timeline (SVG):

Draw a clean, technical SVG timeline diagram showing MVCC in PostgreSQL: two transactions A and B on a time axis, two versions of a row (v1, v2) with xmin/xmax labels, arrows indicating visibility rules for READ COMMITTED vs REPEATABLE READ. Use neutral colors, thin lines, monospace labels.

Write Skew vs SSI (SVG):

Create a side-by-side SVG diagram: left panel shows Snapshot Isolation write skew with two transactions (A, B) reading the same invariant and updating disjoint rows; right panel shows Serializable Snapshot Isolation with a conflict cycle detection and one transaction aborted. Include captions “SI (write skew)” and “SSI (abort)”.

InnoDB next-key locks (SVG):

Produce an SVG index-range diagram for InnoDB next-key locks: show index keys, gap locks, and next-key intervals for a locking read “SELECT … FOR UPDATE WHERE amount > 1000”. Highlight locked ranges and explain why phantoms are prevented for locking reads.

SQL Server key-range locks (SVG):

Build an SVG illustrating key-range locks under SERIALIZABLE in SQL Server: show index B-Tree, a searched range, and key-range locks holding between keys to prevent phantoms. Include brief labels for lock modes.

Чеклист перед продом

• Поймите, где вам действительно нужна сериализация; в остальном не поднимайте уровень без причины.

• Для инвариантов — блокируйте ровно те строки/диапазоны, которые определяют инвариант.

• На SERIALIZABLE (PG/SSI, Oracle) — ретраи — must-have.

• В отчётах — транзакционный срез (REPEATABLE READ, SNAPSHOT).

• Оптимистичные версии — отличный компромисс против lost update.

• Следите за долгоживущими транзакциями в PG: они тормозят вакуум.

Вывод

Изоляция — это выбор компромисса.

MVCC и snapshot-подходы снимают боль с блокировками чтения, но вносят «скрытые» гонки (write skew).

SERIALIZABLE в PostgreSQL — это SSI с детектированием конфликтов и редкими откатами, а в SQL Server — это диапазонные блокировки. InnoDB закрывает фантомы для locking-reads за счёт next-key locks. Oracle живёт на statement-snapshot и откатах сериализации.

Понимая эти различия, вы проектируете систему не «по названиям уровней», а по механике: где нужен снимок, где — диапазонная блокировка, где — оптимистичная версия, где — ретраи. Это и есть инженерный подход.

Приложение: короткие «шпаргалки» коду

PostgreSQL: оптимистичная версия

ALTER TABLE accounts ADD COLUMN version bigint NOT NULL DEFAULT 0;

-- обновляем с контролем версии
UPDATE accounts
SET balance = balance - $1, version = version + 1
WHERE id = $2 AND version = $3;

-- проверяем rowcount: если 0 — был конфликт, повторяем

MySQL: безопасное бронирование слота

BEGIN;
SELECT id FROM slots
WHERE start_ts = ? AND status = 'free'
FOR UPDATE;  -- next-key locks держит диапазон (если есть индекс)
UPDATE slots SET status = 'booked' WHERE id = ?;
COMMIT;

SQL Server: отчёт в SNAPSHOT

ALTER DATABASE AppDB SET ALLOW_SNAPSHOT_ISOLATION ON;
-- затем:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
-- ... все SELECT видят один срез ...
COMMIT;

Oracle: ретраи сериализации

<<retry>>
BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- ... логика ...
  COMMIT;
EXCEPTION WHEN ORA_08177 THEN
  ROLLBACK;
  -- подождать и goto retry
END;