Транзакции, уровни изоляции, блокировки и аномалии

Транзакция — самая недооценённая абстракция в инженерии данных. Её обычно объясняют через банковский перевод («списали у Алисы, зачислили Бобу — либо оба, либо ничего»), и на этом объяснение заканчивается. Проблема в том, что атомарность — самая простая часть. Настоящая сложность — в изоляции: что видит ваша транзакция, пока рядом работают ещё двести таких же, и какие именно неправды она при этом может увидеть.

Практический факт, с которого стоит начать: почти все приложения работают на уровне изоляции, который не гарантирует корректности их бизнес-логики. Read Committed по умолчанию в PostgreSQL, Repeatable Read по умолчанию в MySQL, Read Committed в Oracle и SQL Server. Ни один из них не сериализуемый. Это не баг и не халатность вендоров — это осознанный размен производительности на строгость, о котором большинство разработчиков просто не знает, что он был сделан за них.

Эта статья — про то, как устроен этот размен. Мы разберём аномалии по определениям (а не по фольклору), три семейства реализаций изоляции (блокировки, версии, оптимистичная проверка), реальное поведение PostgreSQL и InnoDB, работу с дедлоками и — отдельно и подробно — то, что действительно ломается в проде: длинные транзакции и очереди блокировок.

Предполагается, что вы знакомы с MVCC в PostgreSQL из статьи PostgreSQL: возможности, индексы, MVCC и с устройством InnoDB из MySQL и MariaDB.

ACID: что из этих букв настоящая гарантия

Аббревиатуру ACID придумали Хаэрдер и Ройтер в 1983 году (Principles of Transaction-Oriented Database Recovery), и она получилась скорее удачной мнемоникой, чем строгой классификацией. Разберём честно.

Ключевой вывод: C в ACID — это не гарантия, а обязанность. Как заметил Мартин Клеппман в «Designing Data-Intensive Applications», букву C добавили в аббревиатуру во многом ради произносимости. Согласованность обеспечивают atomicity + isolation + ваши ограничения и ваш код.

Вторая тонкость — долговечность не абсолютна. D означает «переживёт крах процесса», а не «переживёт пожар в дата-центре». Настоящая долговечность — это ещё и репликация с подтверждением, о чём в статье Репликация, шардирование и высокая доступность.

Жизненный цикл транзакции

Разница в поведении после ошибки — источник настоящих багов при переносе кода между СУБД. В PostgreSQL любая ошибка отравляет всю транзакцию; лечится SAVEPOINT (или блоками BEGIN ... EXCEPTION в PL/pgSQL, которые внутри и есть savepoint’ы). В MySQL и Oracle упавшая команда откатывается сама, транзакция продолжает жить.

-- PostgreSQL: как пережить ожидаемую ошибку внутри транзакции
BEGIN;
INSERT INTO accounts(id, owner) VALUES (1, 'alice');
SAVEPOINT sp1;
INSERT INTO accounts(id, owner) VALUES (1, 'dup');  -- нарушение PK
-- ERROR: duplicate key value violates unique constraint
ROLLBACK TO SAVEPOINT sp1;   -- транзакция снова работоспособна
INSERT INTO accounts(id, owner) VALUES (2, 'bob');
COMMIT;                       -- alice и bob сохранены

Осторожно: каждый SAVEPOINT — это подтранзакция со своим xid. Больше 64 подтранзакций на транзакцию — и в PostgreSQL начинается переполнение кэша подтранзакций (SubtransSIDs), которое проявляется как загадочное падение производительности на всём инстансе. Не ставьте savepoint в цикле на миллион итераций.

Аномалии: строгие определения

Классификация из стандарта ANSI SQL-92 оказалась дырявой — это показали Беренсон, Бернштейн, Грей и соавторы в работе A Critique of ANSI SQL Isolation Levels (1995). Стандарт описывал аномалии через конкретные сценарии, а не через классы конфликтов, и потому пропустил половину интересного. Ниже — рабочая классификация.

P0 — Dirty Write (грязная запись). T1 изменила строку, не зафиксировалась; T2 изменяет ту же строку. При откате T1 непонятно, что восстанавливать. Запрещено на всех уровнях изоляции во всех практических СУБД — именно поэтому запись всегда берёт эксклюзивную блокировку строки до конца транзакции.

P1 — Dirty Read (грязное чтение). T2 видит незафиксированные изменения T1. Возможно только на Read Uncommitted. В PostgreSQL невозможно в принципе: READ UNCOMMITTED там — синоним READ COMMITTED.

P2 — Non-Repeatable Read (неповторяющееся чтение). Два одинаковых SELECT одной строки в одной транзакции вернули разное. Живёт на Read Committed.

P3 — Phantom Read (фантом). Повторный запрос по предикату вернул новый набор строк — кто-то вставил подходящую строку.

P4 — Lost Update (потерянное обновление). Классика: SELECT balance → посчитали в приложении → UPDATE ... SET balance = 42. Две транзакции читают 100, обе пишут 110 вместо 120.

A5A — Read Skew (перекос чтения). T1 читает x, T2 меняет x и y, T1 читает y. Получилась картина, которой никогда не существовало. Пример: бэкап, сделанный без снимка, где половина файлов «до», половина «после».

A5B — Write Skew (перекос записи). Две транзакции читают пересекающееся множество строк, принимают решение и пишут в разные строки. Каждая по отдельности корректна, вместе — нарушают инвариант. Это единственная аномалия, которую нельзя поймать проверкой конфликтов записей.

Классический пример — дежурства. Инвариант: хотя бы один врач на смене. Алиса и Боб одновременно проверяют «нас двое, значит я могу уйти» и оба уходят.

-- Оба сеанса на REPEATABLE READ (snapshot isolation). Оба COMMIT проходят.
-- Сеанс A                                  -- Сеанс B
BEGIN ISOLATION LEVEL REPEATABLE READ;      BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors                SELECT count(*) FROM doctors
  WHERE on_call AND shift_id = 42;           WHERE on_call AND shift_id = 42;
-- 2, можно уходить                          -- 2, можно уходить
UPDATE doctors SET on_call = false           UPDATE doctors SET on_call = false
  WHERE name = 'alice' AND shift_id = 42;     WHERE name = 'bob' AND shift_id = 42;
COMMIT;  -- ok                                COMMIT;  -- ok. Дежурных ноль.

A3 — Phantom при агрегатах / Serialization Anomaly. Обобщение: итоговое состояние не соответствует ни одному последовательному порядку выполнения транзакций. Только SERIALIZABLE это исключает.

Уровни изоляции: стандарт против реальности

Стандарт определяет четыре уровня через то, какие аномалии запрещены:

А вот что происходит на самом деле у конкретных движков — таблица, которую стоит запомнить:

Три вывода, которые ломают привычные ожидания:

1. SERIALIZABLE в Oracle не сериализуемый — это snapshot isolation. Write skew в Oracle воспроизводится на самом строгом уровне. То же исторически верно для многих SI-движков.
2. REPEATABLE READ в PostgreSQL строже стандарта — фантомов нет, это полноценный SI.
3. REPEATABLE READ в InnoDB — гибрид: обычный SELECT читает из снимка, а SELECT ... FOR UPDATE, UPDATE, DELETE читают свежие данные («current read»). Отсюда фирменная неожиданность MySQL: SELECT count(*) вернёт 5, а следующий UPDATE ... WHERE тронет 6 строк.

Подробнее о специфике корпоративных движков — в статье Microsoft SQL Server и Oracle, о SQLite — в SQLite и встраиваемые БД.

Три способа реализовать изоляцию

Двухфазная блокировка (2PL)

Классический результат теории конкурентного доступа: если каждая транзакция сначала только захватывает блокировки (фаза роста), а после первого освобождения больше ничего не захватывает (фаза сжатия), то любое расписание сериализуемо. Момент последнего захвата называют lock point, и порядок lock point’ов задаёт эквивалентный последовательный порядок.

На практике используют строгую версию, SS2PL: все блокировки держатся до COMMIT. Строгость не нужна для сериализуемости — она нужна для восстановимости: если отпустить блокировку до фиксации и потом откатиться, другая транзакция уже прочитала грязные данные и её тоже придётся откатывать (каскадный откат). SS2PL реализуют SQL Server (в блокировочных уровнях), DB2 и InnoDB на SERIALIZABLE.

Цена: читатели блокируют писателей. Один отчёт на 20 минут — и OLTP-нагрузка встала.

MVCC

Вместо блокировки читателю дают версию, актуальную на момент старта. PostgreSQL хранит версии прямо в таблице (xmin/xmax в заголовке кортежа), InnoDB и Oracle — в undo-логе, восстанавливая старую версию задом наперёд по цепочке rollback-указателей.

-- PostgreSQL: смотрим служебные поля MVCC своими глазами
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;
-- xmin = xid транзакции-создателя версии
-- xmax = xid транзакции-удалителя (0, если версия жива)

-- Кто мешает уборке мусора прямо сейчас:
SELECT pid, state, age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 10;

Разница в реализации даёт разные проблемы. PostgreSQL: раздувание таблиц (bloat) и зависимость от autovacuum. InnoDB/Oracle: рост undo-сегмента и знаменитый Oracle ORA-01555: snapshot too old, когда длинный запрос попросил версию, которую undo уже перезаписал.

Оптимистичная проверка и SSI

Serializable Snapshot Isolation (Кэхилл, Рёр, Фекете, 2008; реализация в PostgreSQL 9.1) — красивая идея: работаем как SI, но следим за конфликтами чтение-запись. Известно (Фекете и др.), что любая аномалия SI содержит структуру из двух подряд идущих rw-конфликтов — «опасную структуру». Обнаружив её, движок откатывает одну из транзакций с кодом 40001.

Это делает SERIALIZABLE в PostgreSQL честным и дешёвым — но требует от приложения умения повторять транзакции.

import time, random
import psycopg

# Универсальный retry для сериализационных сбоев.
# 40001 = serialization_failure, 40P01 = deadlock_detected.
RETRYABLE = {"40001", "40P01"}

def run_serializable(conn_factory, body, attempts: int = 5):
    for attempt in range(attempts):
        try:
            with conn_factory() as conn:
                conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE
                with conn.transaction():
                    return body(conn)
        except psycopg.errors.Error as e:
            if e.sqlstate not in RETRYABLE or attempt == attempts - 1:
                raise
            # экспоненциальная задержка с джиттером — без неё
            # конфликтующие транзакции синхронно бьются снова
            time.sleep((2 ** attempt) * 0.01 * (0.5 + random.random()))
    raise RuntimeError("недостижимо")

Три правила ретраев, которые нарушают все:

1. Тело транзакции должно быть идемпотентным относительно внешнего мира. Если внутри вы шлёте письмо или дёргаете платёжный API — при ретрае это произойдёт дважды. Внешние эффекты — только после COMMIT, через outbox-таблицу.
2. Джиттер обязателен. Без него две конфликтующие транзакции повторяются синхронно и конфликтуют снова.
3. Ограничивайте число попыток и логируйте частоту. Резкий рост 40001 — сигнал о горячей точке в данных, а не повод увеличить attempts.

Блокировки в PostgreSQL: два независимых уровня

Главная путаница новичков: блокировки таблиц и блокировки строк — разные механизмы с разными матрицами конфликтов.

Табличные режимы (от слабых к сильным): ACCESS SHARE (обычный SELECT), ROW SHARE (SELECT FOR UPDATE), ROW EXCLUSIVE (INSERT/UPDATE/DELETE), SHARE UPDATE EXCLUSIVE (VACUUM, CREATE INDEX CONCURRENTLY, ANALYZE), SHARE (CREATE INDEX), SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE (ALTER TABLE, DROP, TRUNCATE, VACUUM FULL).

Ключевое: ACCESS EXCLUSIVE конфликтует со всем, включая обычный SELECT. Отсюда — раздел про DDL ниже.

Строчные режимы и их матрица конфликтов:

Практический смысл разделения: обычный UPDATE, не трогающий колонки уникальных индексов, берёт FOR NO KEY UPDATE и потому не блокирует проверку внешнего ключа (FOR KEY SHARE) из дочерней таблицы. До версии 9.3 в PostgreSQL этого не было, и вставка в дочернюю таблицу конфликтовала с любым обновлением родителя — источник огромного количества дедлоков в старых системах.

-- Диагностика: кто кого ждёт прямо сейчас (PG 9.6+)
SELECT blocked.pid            AS blocked_pid,
       blocked.query          AS blocked_query,
       blocking.pid           AS blocking_pid,
       blocking.query         AS blocking_query,
       blocking.state,
       now() - blocking.xact_start AS blocking_xact_age
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = bp.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

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

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

-- Сессионная: живёт до отпускания или разрыва соединения
SELECT pg_try_advisory_lock(hashtext('nightly-billing-job'));
-- ... работа ...
SELECT pg_advisory_unlock(hashtext('nightly-billing-job'));

-- Транзакционная: снимается автоматически на COMMIT/ROLLBACK — предпочтительна
SELECT pg_advisory_xact_lock(42, tenant_id) FROM ...;

Важно: с пулером в режиме transaction (PgBouncer) сессионные advisory-блокировки ломаются — следующая транзакция может попасть в другой backend. Используйте только _xact_ варианты.

Очередь задач без дедлоков

Канонический паттерн, ради которого стоило читать всё выше:

-- Атомарно забрать пачку задач; конкурирующие воркеры не ждут друг друга
WITH picked AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending' AND run_after <= now()
    ORDER BY priority DESC, run_after
    LIMIT 20
    FOR UPDATE SKIP LOCKED          -- пропустить занятые другими строки
)
UPDATE jobs j
   SET status = 'running', started_at = now(), worker = pg_backend_pid()
  FROM picked
 WHERE j.id = picked.id
RETURNING j.id, j.payload;

SKIP LOCKED (PostgreSQL 9.5+, есть и в MySQL 8.0, и в Oracle) превращает очередь из точки сериализации в честно параллельную структуру. Без него сто воркеров выстраиваются в очередь на первой же строке. Альтернатива NOWAIT — мгновенная ошибка вместо ожидания, полезна для интерактивных операций.

Блокировки в InnoDB: record, gap и next-key

InnoDB блокирует записи индекса, а не строки таблицы. Это следствие того, что таблица InnoDB и есть кластеризованный индекс. Три вида блокировок:

• Record lock — на конкретную запись индекса.
• Gap lock — на промежуток между записями, не включая их. Существует только чтобы не дать вставить строку в промежуток, то есть против фантомов.
• Next-key lock — record + gap перед ним, полуинтервал (prev, current]. Это режим по умолчанию на REPEATABLE READ.

-- Таблица: id PK, значения 10, 20, 30
-- Сеанс A на REPEATABLE READ:
BEGIN;
SELECT * FROM t WHERE id BETWEEN 15 AND 25 FOR UPDATE;
-- Взяты next-key locks: (10,20] и (20,30]
-- Сеанс B:
INSERT INTO t VALUES (22);   -- ЖДЁТ: попал в gap (20,30]
INSERT INTO t VALUES (35);   -- проходит

Самая частая беда InnoDB — блокировки по неиндексированному условию. Если UPDATE t SET x=1 WHERE non_indexed_col = 5 не может использовать индекс, InnoDB сканирует всё и берёт next-key lock на каждую просмотренную запись. Формально после фильтрации лишние снимаются, но в момент сканирования вы заблокировали таблицу целиком. Это одна из главных причин, почему индексы — тема не только производительности; см. Индексы и планы выполнения.

-- Что происходит с блокировками прямо сейчас (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- Последний дедлок и полная картина транзакций:
SHOW ENGINE INNODB STATUS\G

-- Записывать все дедлоки в error log (по умолчанию выключено!):
SET GLOBAL innodb_print_all_deadlocks = ON;

Дедлоки: почему они неизбежны и что с ними делать

Дедлок — цикл в графе ожидания. Обнаруживается обходом графа; PostgreSQL запускает проверку через deadlock_timeout (по умолчанию 1 с) после начала ожидания, InnoDB — сразу.

Классический сценарий на двоих — перевод денег в противоположных направлениях:

-- T1: перевод 1 -> 2            -- T2: перевод 2 -> 1
BEGIN;                            BEGIN;
UPDATE acc SET b=b-100            UPDATE acc SET b=b-50
  WHERE id=1;   -- взял строку 1    WHERE id=2;   -- взял строку 2
UPDATE acc SET b=b+100            UPDATE acc SET b=b+50
  WHERE id=2;   -- ЖДЁТ T2          WHERE id=1;   -- ЖДЁТ T1 -> дедлок

Лечится тривиально и надёжно — глобальным порядком захвата:

-- Всегда трогаем счета в порядке возрастания id.
-- Цикл в графе ожидания становится невозможным по построению.
UPDATE accounts SET balance = balance + delta
 WHERE id = ANY (ARRAY[$1, $2])
   AND ... ;
-- либо явно:
SELECT * FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE;

Что стоит знать про дедлоки в проде:

Важное отличие: PostgreSQL выбирает жертвой ту транзакцию, которая обнаружила цикл; InnoDB — ту, что изменила меньше строк (дешевле откатить). Ни один из движков не гарантирует, кого именно убьёт, — обработчик 40P01/1213 нужен в любом клиентском коде.

Lost update: четыре способа починить, честно сравненные

Задача: инкремент счётчика или списание с баланса, где новое значение зависит от прочитанного.

-- ❌ ЛОМАЕТСЯ на Read Committed и Repeatable Read
SELECT balance FROM accounts WHERE id = 1;   -- 100
-- ... приложение считает 100 - 30 = 70 ...
UPDATE accounts SET balance = 70 WHERE id = 1;

-- ✅ 1. Атомарно. Read Committed перечитывает строку после снятия блокировки
UPDATE accounts SET balance = balance - 30
 WHERE id = 1 AND balance >= 30
RETURNING balance;
-- 0 строк => недостаточно средств, ошибка бизнес-уровня, а не гонка

-- ✅ 2. Пессимистично
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- держим строку
-- сложная логика в приложении
UPDATE accounts SET balance = $new WHERE id = 1;
COMMIT;

-- ✅ 3. Оптимистично (версионирование)
UPDATE accounts SET balance = $new, version = version + 1
 WHERE id = 1 AND version = $read_version;
-- rowcount = 0 => кто-то опередил, читаем заново и повторяем

Тонкость про Read Committed, которую мало кто знает: при UPDATE ... WHERE balance >= 30 PostgreSQL, наткнувшись на строку, заблокированную другой транзакцией, дождётся её и перечитает строку заново, повторно применив условие WHERE к новой версии. Это называется EPQ (EvalPlanQual). Именно поэтому атомарный UPDATE безопасен даже на RC. Но на REPEATABLE READ этот трюк невозможен — там вы получите ERROR: could not serialize access due to concurrent update.

Write skew в проде и как его закрыть

Три рабочих способа, в порядке возрастания стоимости:

-- 1. Материализация конфликта: превратить write skew в write-write конфликт.
--    Заводим строку-«замок» на группу и блокируем её.
BEGIN;
SELECT 1 FROM shifts WHERE id = 42 FOR UPDATE;   -- сериализуем всю смену
SELECT count(*) FROM doctors WHERE on_call AND shift_id = 42;
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;

-- 2. Ограничение уровня БД: инвариант становится обязанностью СУБД.
--    Не всегда выразимо, но когда выразимо — это лучший вариант.
CREATE TABLE shift_coverage (
    shift_id   int PRIMARY KEY REFERENCES shifts(id),
    on_call_ct int NOT NULL CHECK (on_call_ct >= 1)
);
-- триггер поддерживает on_call_ct; конкурентные UPDATE конфликтуют по строке

-- 3. SERIALIZABLE: SSI поймает rw-конфликт и откатит одну из транзакций
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...тот же наивный код...
COMMIT;   -- ERROR: could not serialize access due to read/write dependencies

Чтобы SSI в PostgreSQL работал эффективно, нужно помнить:

• Все участники должны быть SERIALIZABLE. Транзакция на RC рядом с сериализуемыми не участвует в проверке и может нарушить инвариант.
• SET TRANSACTION READ ONLY и DEFERRABLE для длинных отчётов: такая транзакция подождёт безопасного снимка и дальше не будет ни откатываться, ни вызывать откаты у других.
• Предикатные блокировки живут в памяти (max_pred_locks_per_transaction). При переполнении они эскалируются от строк к страницам и к отношению — растёт число ложных откатов. Мониторьте pg_stat_database.xact_rollback и частоту 40001.

-- Идеальный режим для аналитического запроса на реплике или мастере
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT ...;   -- гарантированно консистентный снимок, ноль откатов
COMMIT;

Сколько это стоит: замеры

Порядок величин на pgbench (масштаб 100, 16 клиентов, PostgreSQL 16, NVMe). Абсолютные числа зависят от железа — важны соотношения.

# Воспроизвести самому
pgbench -i -s 100 bench
pgbench -c 16 -j 4 -T 60 -M prepared bench                       # RC
PGOPTIONS='-c default_transaction_isolation=serializable' \
  pgbench -c 16 -j 4 -T 60 -M prepared --max-tries=10 bench       # SSI + ретраи
# --max-tries появился в pgbench 14 и сам повторяет 40001/40P01

Вывод, который важнее самих чисел: SERIALIZABLE дёшев, пока нет горячих точек. Если 90% транзакций трогают одну строку-счётчик, вас убьёт не уровень изоляции, а физика конкуренции за эту строку. Лечится изменением схемы (шардированные счётчики, INSERT вместо UPDATE с последующей агрегацией), а не настройками.

Что реально ломается в проде

Аномалии изоляции — редкая причина инцидентов. Вот настоящий топ.

1. Idle in transaction

Транзакция открыта, запрос не выполняется, а приложение ушло делать HTTP-запрос к внешнему сервису. Пока она жива: не двигается horizon, autovacuum не может убрать мёртвые версии, таблицы пухнут, запросы деградируют. Одна забытая транзакция способна за ночь раздуть базу вдвое.

-- Обязательно в postgresql.conf для любого прода:
idle_in_transaction_session_timeout = '60s'   -- убить забытые транзакции
statement_timeout = '30s'                     -- ни один запрос не вечен
lock_timeout = '3s'                           -- не ждать блокировку бесконечно
transaction_timeout = '120s'                  -- PG 17+: лимит на всю транзакцию

-- Найти виновников:
SELECT pid, state, now() - state_change AS idle_for, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Архитектурное правило: внутри транзакции не должно быть ни одного сетевого вызова наружу. Транзакция — это работа с данными, а не единица бизнес-процесса.

2. Очередь за ACCESS EXCLUSIVE

Самый коварный сценарий, потому что база «просто встаёт» без единого дедлока:

Очередь блокировок в PostgreSQL честная (FIFO): ожидающий сильный режим блокирует всех, кто пришёл после. Поэтому один ALTER TABLE за спиной долгого отчёта кладёт продакшен.

Правильный рецепт миграций:

-- Каждая миграция начинается с этого
SET lock_timeout = '3s';           -- не встать в очередь надолго
SET statement_timeout = '0';       -- но саму работу не обрывать

-- Ретрай на уровне миграционного скрипта: если lock_timeout — подождать и повторить.
ALTER TABLE orders ADD COLUMN promo_code text;   -- метаданные, мгновенно в PG 11+

-- Индексы — всегда конкурентно (не берёт ACCESS EXCLUSIVE, но не работает в транзакции)
CREATE INDEX CONCURRENTLY idx_orders_promo ON orders(promo_code);

-- NOT NULL без переписывания таблицы (PG 12+): сначала CHECK NOT VALID, потом VALIDATE
ALTER TABLE orders ADD CONSTRAINT orders_promo_nn CHECK (promo_code IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_promo_nn;   -- берёт слабый SHARE UPDATE EXCLUSIVE

3. Длинная транзакция на реплике

hot_standby_feedback = on защищает запросы на реплике от отмены — но передаёт xmin реплики на мастер, и теперь длинный отчёт на реплике мешает autovacuum на мастере. Выключите — получите ERROR: canceling statement due to conflict with recovery. Выбор осознанный, третьего варианта нет (кроме отдельной реплики только под аналитику).

4. Пулер и семантика сессии

PgBouncer в режиме transaction рвёт связь между сессией и соединением: SET, временные таблицы, курсоры WITH HOLD, сессионные advisory-локи, подготовленные выражения (до PgBouncer 1.21) перестают работать предсказуемо. Правило: в режиме transaction живёт только то, что помещается в одну транзакцию.

5. ORM, который прячет транзакцию

Django ATOMIC_REQUESTS = True оборачивает весь HTTP-запрос в транзакцию: медленный шаблон или внешний вызов удерживают транзакцию открытой. Hibernate с FlushMode.AUTO откладывает записи до конца, меняя порядок захвата блокировок между запусками и порождая случайные дедлоки. Rails after_commit против after_save — разница между «отправили письмо о заказе, которого нет» и корректным поведением.

Распределённые транзакции: цена согласия

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

2PC — блокирующий протокол: сбой координатора подвешивает участников. Отсюда практика:

-- PostgreSQL умеет 2PC, но по умолчанию выключен
SET max_prepared_transactions = 20;   -- требует рестарта

BEGIN;
UPDATE stock SET qty = qty - 1 WHERE sku = 'X';
PREPARE TRANSACTION 'order-8821';     -- блокировки держатся, транзакция «висит»
-- ...координатор упал...
-- Ручная уборка, иначе горизонт xmin заморожен НАВСЕГДА:
SELECT gid, prepared, age(transaction) FROM pg_prepared_xacts ORDER BY prepared;
ROLLBACK PREPARED 'order-8821';

Забытая prepared-транзакция в PostgreSQL — редкий, но катастрофический инцидент: она блокирует уборку мусора вечно, вплоть до угрозы wraparound. Мониторинг pg_prepared_xacts обязателен, если вы включили 2PC.

Практическая альтернатива в микросервисах — сага: последовательность локальных транзакций с компенсациями. Атомарности нет, есть eventual consistency и явно написанные откаты. Плюс transactional outbox: событие пишется в ту же БД и той же транзакцией, что и бизнес-данные, а отдельный процесс доставляет его в брокер. Это единственный способ атомарно «изменить данные и отправить сообщение» без 2PC.

-- Outbox: одна транзакция, два эффекта, ноль распределённых протоколов
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = $1;
INSERT INTO outbox(aggregate_id, type, payload, created_at)
VALUES ($1, 'OrderPaid', $2::jsonb, now());
COMMIT;
-- Отдельный воркер читает outbox через FOR UPDATE SKIP LOCKED и публикует в Kafka,
-- гарантируя at-least-once. Потребители должны быть идемпотентны.

Как это устроено в распределённых SQL-движках (детерминированные метки времени, Raft-группы, TrueTime) — в статье NewSQL и распределённые БД.

Транзакции за пределами реляционного мира

Общая закономерность: чем шире распределена система, тем уже граница атомарности. Про фундаментальный компромисс — CAP и BASE — в статье NoSQL: таксономия, CAP, BASE.

Чеклист: типичные ошибки

• Транзакция открыта вокруг HTTP-вызова или долгой обработки в приложении. Самая частая и самая дорогая ошибка.
• Нет statement_timeout, lock_timeout, idle_in_transaction_session_timeout. Прод без таймаутов — прод, ждущий инцидента.
• SELECT + вычисление + UPDATE вместо атомарного UPDATE ... SET x = x - $1.
• Нет обработчика 40001/40P01. Любой код, работающий на RR или SERIALIZABLE, обязан уметь повторять транзакцию.
• Ретрай без джиттера и без ограничения попыток.
• Разный порядок захвата строк в разных участках кода — гарантированные дедлоки при росте нагрузки.
• Миграция без lock_timeout. Один ALTER TABLE за долгим отчётом = отказ в обслуживании.
• CREATE INDEX вместо CREATE INDEX CONCURRENTLY на живой таблице.
• Нет индекса на FK-колонке — блокировки родительских строк и дедлоки при каскадах.
• Сессионные advisory-локи под PgBouncer в режиме transaction.
• Уверенность, что SERIALIZABLE в Oracle сериализуемый. Это snapshot isolation.
• Отправка событий в брокер внутри транзакции вместо outbox — либо потеря события, либо событие о том, чего не произошло.
• SAVEPOINT в цикле — переполнение кэша подтранзакций в PostgreSQL.
• 2PC включён, pg_prepared_xacts не мониторится.

Мини-итог

Транзакция даёт четыре обещания, но реально спорным из них является ровно одно — изоляция, и она по умолчанию не полная. Read Committed защищает от грязных чтений и потерянных записей, но допускает неповторяющиеся чтения, фантомы и потерянные обновления. Snapshot Isolation (он же Repeatable Read в PostgreSQL, он же Serializable в Oracle) убирает фантомы, но оставляет write skew. Настоящую сериализуемость дают либо 2PL с его блокирующими читателями, либо SSI с его откатами и обязательными ретраями.

Выбор делается не «по строгости», а по инварианту: если бизнес-правило выражается одним атомарным UPDATE или ограничением БД — берите Read Committed и спите спокойно. Если правило связывает несколько строк («хотя бы один дежурный», «сумма не превышает лимит») — вам нужна либо материализация конфликта, либо SERIALIZABLE.

А в проде вас с вероятностью 90% укусит не аномалия, а длинная транзакция, забытая в очереди блокировок. Таймауты, отсутствие сетевых вызовов внутри транзакций и аккуратные миграции спасают чаще, чем любой уровень изоляции.

Источники

• A Critique of ANSI SQL Isolation Levels — Berenson, Bernstein, Gray et al., 1995. Работа, показавшая дыры в стандарте
• Serializable Isolation for Snapshot Databases — Cahill, Röhm, Fekete. Теория, из которой вырос SSI в PostgreSQL
• PostgreSQL: Transaction Isolation и Explicit Locking — обязательное чтение целиком
• PostgreSQL Wiki: SSI — примеры аномалий и как SSI их ловит
• MySQL: InnoDB Locking и Transaction Isolation Levels
• Martin Kleppmann, «Designing Data-Intensive Applications» — глава 7 «Transactions», лучший разбор аномалий на человеческом языке
• Hermitage — тесты Клеппмана, показывающие реальное поведение изоляции в разных СУБД. Запустите на своей БД
• Bailis et al., Highly Available Transactions: Virtues and Limitations — что из изоляции достижимо в распределённой системе
• Jepsen — проверки заявленных гарантий изоляции в реальных БД. Многие вендоры обещали больше, чем давали
• Postgres Locks Explorer — какая команда какие блокировки берёт
• Егор Рогов, «PostgreSQL изнутри» — разделы про изоляцию и блокировки

Что дальше

Репликация, шардирование и высокая доступность — как только данные копируются на второй узел, все гарантии из этой статьи приходится пересматривать: снимок на реплике отстаёт, «read-your-writes» перестаёт работать само собой, а атомарность упирается в консенсус.