Перевод 100 рублей со счёта 1 на счёт 2 состоит из трёх операций: списание, зачисление, запись в журнал переводов.

A - Atomicity (атомарность)

Применяются либо все три операции, либо ни одной. Без атомарности сценарий «сервер упал между UPDATEами» оставляет деньги списанными, но не зачисленными - они исчезают из системы. Механизм: WAL плюс правило, что изменения становятся видимыми только после записи о коммите. Откат в PostgreSQL почти бесплатен: новые версии строк просто никто никогда не увидит, потому что их xmin принадлежит откаченной транзакции.

C - Consistency (согласованность)

Транзакция переводит базу из одного корректного состояния в другое: CHECK (balance >= 0) не нарушен, FK на счета целы, сумма денег в системе не изменилась. Без неё баланс уходит в минус. Но есть оговорка: СУБД проверяет только объявленные ей инварианты. Инвариант «сумма денег постоянна» ей неизвестен, и если разработчик забыл второй UPDATE, транзакция будет атомарной, изолированной, долговечной и при этом бизнес-несогласованной. Поэтому C часто называют «половина ответственности приложения».

I - Isolation (изоляция)

Параллельный отчёт «сколько всего денег в банке» не должен увидеть момент, когда со счёта 1 уже списали, а на счёт 2 ещё не зачислили, иначе он покажет на 100 рублей меньше. Без изоляции получаем весь набор аномалий: грязное чтение, потерянные обновления, фантомы. В PostgreSQL для чтения работают MVCC-снапшоты, для записи блокировки строк и SSI. Изоляцию настраивают чаще других букв: у неё четыре уровня, и это сознательный размен строгости на пропускную способность. Своя ручка есть и у D - о ней ниже.

D - Durability (долговечность)

После того как клиент получил «перевод выполнен», выключение питания не должно его отменить. Для этого перед подтверждением коммита запись WAL сбрасывается на диск через fsync. Отсюда практические ручки: synchronous_commit = off даёт кратный прирост скорости записи ценой потери транзакций за последние сотни миллисекунд при аварии (по умолчанию до ~600 мс) (база при этом остаётся согласованной - теряются целые транзакции, а не половинки), а synchronous_standby_names расширяет durability до «переживёт потерю всего сервера».

1. Грязное чтение (dirty read)

T1 списала 100 рублей и ещё не закоммитила. T2 читает баланс и видит списание. T1 делает ROLLBACK. T2 приняла решение по данным, которых никогда не существовало. Запрещено начиная с Read Committed; в PostgreSQL невозможно вообще ни на каком уровне.

2. Неповторяющееся чтение (non-repeatable read)

T1 читает баланс - 500. T2 обновляет его на 400 и коммитит. T1 читает тот же баланс ещё раз - 400. Один и тот же запрос в одной транзакции дал разные ответы. Ломает любой отчёт из нескольких запросов: итоговая строка не сходится с детализацией. Запрещено с Repeatable Read.

3. Фантомное чтение (phantom read)

T1 считает count(*) WHERE dept = 5 - 10. T2 вставляет новую строку с dept = 5 и коммитит. T1 повторяет запрос - 11. Отличие от предыдущей аномалии: изменилась не строка, а множество строк, попадающих под предикат. По стандарту разрешено на Repeatable Read, но в PostgreSQL на Repeatable Read фантомов нет - снапшот берётся один на транзакцию. В MySQL/InnoDB для этого используются gap locks.

4. Потерянное обновление (lost update)

T1 читает qty = 100, T2 читает qty = 100, T1 пишет 150 и коммитит, T2 пишет 130 и коммитит. Прибавка T1 бесследно исчезла, при этом обе транзакции завершились успешно и ошибок не было. Это самая частая аномалия в реальном коде, потому что паттерн «прочитал в приложении → посчитал → записал» пишут все. Лечится не уровнем изоляции, а одним из трёх: атомарным UPDATE x = x + n, SELECT FOR UPDATE или version-колонкой.

5. Аномалия записи (write skew)

Инвариант: на дежурстве должен остаться хотя бы один врач; сейчас дежурят двое. T1 читает «дежурных 2», решает, что можно уйти, и снимает с дежурства Аню. T2 параллельно читает «дежурных 2» и снимает Борю. Обе транзакции изменили разные строки, поэтому конфликта записи нет и обе коммитятся. Дежурных ноль - инвариант нарушен. Snapshot isolation (Repeatable Read) это не ловит принципиально; нужен SERIALIZABLE с SSI, либо явная блокировка того, что читали, либо материализация инварианта в одну строку-счётчик.

В стандарте классическая таблица содержит только первые три колонки: потерянное обновление и write skew в ANSI SQL-92 вообще не упомянуты, их добавили позже в статье «A Critique of ANSI SQL Isolation Levels» (Berenson et al., 1995). Там же появились термины snapshot isolation и write skew, поэтому «уровень стандарта» и «уровень PostgreSQL» не одно и то же.

Как это выглядит в коде

-- на транзакцию
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- или уже внутри, до первого запроса
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- посмотреть текущий
SHOW transaction_isolation;

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
    ReadOnly:  true,   // read-only на Serializable дешевле: SSI почти не отслеживает такие транзакции
})

Почему READ UNCOMMITTED = READ COMMITTED

Грязное чтение возможно только там, где читатель может увидеть данные, изменённые незавершённой транзакцией. В PostgreSQL каждая версия строки помечена xmin, номером создавшей её транзакции, и правило видимости требует, чтобы эта транзакция была закоммичена. Незакоммиченная версия невидима технически, показать её просто нечем. Поэтому SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED принимается без ошибки (для совместимости со стандартом), но ведёт себя как Read Committed. Уровней у PostgreSQL фактически три.

Почему на REPEATABLE READ нет фантомов

RC и RR в PostgreSQL различаются моментом, когда берётся снапшот. На Read Committed снапшот берётся заново на каждый оператор: следующий SELECT увидит всё, что закоммитили соседи. На Repeatable Read снапшот берётся один раз, при первом операторе транзакции, и живёт до конца. У строки, вставленной и закоммиченной после этого момента, xmin больше границы снапшота, и она невидима, сколько раз запрос ни повторяй. Фантом не может появиться просто потому, что база для этой транзакции «застыла во времени». Стандарт фантомы на RR разрешает, PostgreSQL их запрещает: реализация здесь строже спецификации.

Что взамен появляется на RR

Запись. Если транзакция на Repeatable Read пытается обновить строку, изменённую и закоммиченную другой транзакцией после её снапшота, PostgreSQL не может корректно применить изменение (её снапшот описывает устаревший мир) и откатывает транзакцию с ERROR: could not serialize access due to concurrent update, SQLSTATE 40001. На Read Committed в этой же ситуации сработал бы EvalPlanQual: перечитал бы строку и заново проверил WHERE. Поэтому переход на RR всегда идёт в комплекте с ретраями.

SERIALIZABLE = SSI

С версии 9.1 PostgreSQL реализует настоящую сериализуемость через Serializable Snapshot Isolation: поверх обычных снапшотов движок отслеживает предикатные блокировки и ищет в графе зависимостей опасную структуру из двух подряд идущих rw-конфликтов. Найдя её, откатывает одну из участниц с could not serialize access due to read/write dependencies among transactions. Читатели по-прежнему никого не блокируют: платишь не ожиданием, а вероятностью отката и памятью под отслеживание предикатов (max_pred_locks_per_transaction).

В PostgreSQL по умолчанию стоит READ COMMITTED: снапшот на каждый оператор. Компромисс «достаточно строго для большинства задач, минимум откатов». Глобально его почти никогда не меняют, уровень поднимают точечно на конкретную транзакцию.

В MySQL/InnoDB по умолчанию REPEATABLE READ, и дело не в заботе о строгости. Исторически MySQL использовал statement-based репликацию: на реплику передавался текст запроса, который там выполнялся заново. При Read Committed порядок применения операторов мог дать на реплике другой результат, поэтому потребовался более строгий уровень плюс gap locks - блокировки «промежутков» между значениями индекса, которые не дают вставить строку в диапазон, прочитанный чужой транзакцией. Gap locks и закрывают фантомы в InnoDB, и они же порождают знаменитые дедлоки MySQL на, казалось бы, невинных вставках.

Откуда берётся

• Repeatable Read: транзакция пытается обновить строку, изменённую и закоммиченную после её снапшота, и получает could not serialize access due to concurrent update.
• Serializable: SSI находит в графе зависимостей опасную структуру и отвечает could not serialize access due to read/write dependencies among transactions.
• Рядом стоит 40P01 deadlock_detected - причина другая, обработка та же.

Откат при этом полный: база осталась согласованной, ничего наполовину не применилось, повтор той же транзакции корректен по определению. Поэтому SSI и работает: движок обещает «либо результат будет как при последовательном выполнении, либо я откачу». За вторую половину обещания отвечает приложение.

Как это выглядит правильно

// Оборачиваем всю бизнес-операцию, а не отдельный запрос
err := WithRetry(ctx, pool, func(tx pgx.Tx) error {
    var seats int
    if err := tx.QueryRow(ctx,
        `SELECT count(*) FROM bookings WHERE flight_id = $1`, flightID).Scan(&seats); err != nil {
        return err
    }
    if seats >= capacity {
        return ErrNoSeats           // бизнес-ошибка, не ретраим
    }
    _, err := tx.Exec(ctx,
        `INSERT INTO bookings(flight_id, user_id) VALUES ($1, $2)`, flightID, userID)
    return err
})
// Письмо отправляем только здесь, после успешного коммита:
// внутри fn оно ушло бы столько раз, сколько было попыток.

Что должно быть в реализации ретрая

1. Повторять всю транзакцию с нуля - старый снапшот невалиден.
2. Экспоненциальная пауза с джиттером. Без разброса конкуренты синхронно повторят конфликт и снова столкнутся.
3. Ограничение числа попыток и осмысленная финальная ошибка.
4. Никаких внешних побочных эффектов внутри: ни писем, ни платежей, ни сообщений в Kafka. Только чистая работа с БД; всё остальное после коммита или через outbox.
5. Различать ретраибельные и нет. 23505 unique_violation или 23514 check_violation повторять бессмысленно, они просто повторятся.
6. Метрика на число ретраев. Растёт доля конфликтов, значит, горячая точка разрастается и пора менять схему (шардировать счётчик, перейти на атомарный UPDATE).

FOR UPDATE и его слабые формы

SELECT ... FOR UPDATE берёт эксклюзивную блокировку на каждую возвращённую строку до конца транзакции. Другие транзакции не смогут её обновить, удалить или тоже заблокировать; обычные SELECT при этом работают свободно - в PostgreSQL читатели никогда не ждут писателей. Есть более слабые режимы: FOR NO KEY UPDATE (берётся неявно обычным UPDATE неключевых колонок), FOR SHARE («пусть читают, но менять нельзя») и FOR KEY SHARE (запрещает только удаление и смену ключа; его и берёт проверка внешнего ключа).

SKIP LOCKED - очередь на таблице

UPDATE tasks SET status = 'processing', locked_at = now()
WHERE id IN (
    SELECT id FROM tasks
    WHERE status = 'new' AND run_after <= now()
    ORDER BY priority DESC, id
    LIMIT 10
    FOR UPDATE SKIP LOCKED
)
RETURNING id, payload;

Без SKIP LOCKED десять воркеров выстроились бы в очередь за одной и той же первой строкой - параллелизм нулевой. С ним каждый пропускает занятое и забирает свою пачку. Это стандартный способ сделать очередь без брокера: плюс - задачи ставятся транзакционно вместе с бизнес-данными (никаких «в базе есть, в очереди нет»), минус - нагрузка на БД и bloat от постоянных UPDATE статуса, поэтому на десятках тысяч задач в секунду нужен настоящий брокер.

NOWAIT

FOR UPDATE NOWAIT вместо ожидания сразу отдаёт ошибку 55P03 lock_not_available. Нужен там, где ждать хуже, чем отказать: интерактивное редактирование документа («сейчас редактирует другой пользователь»), административная операция, которую можно повторить. Родственная настройка - SET LOCAL lock_timeout = '2s': ждать, но не бесконечно.

Пессимистичная

Блокировка берётся до начала работы и держится до конца транзакции. Плюс: операция гарантированно проходит с первого раза, логика линейная, ретраи не нужны. Минусы: конкуренты стоят в очереди (пропускная способность по горячей строке падает до «одна транзакция за раз»), растёт риск дедлоков, и она принципиально не работает через «длинный» бизнес-процесс - блокировку нельзя держать, пока пользователь заполняет форму.

Оптимистичная через version-колонку

CREATE TABLE documents (
    id      bigint PRIMARY KEY,
    body    text   NOT NULL,
    version int    NOT NULL DEFAULT 1
);

-- 1) читаем без блокировки, отдаём пользователю body и version = 7
-- 2) через 5 минут он присылает изменения; пишем условно:
UPDATE documents
   SET body = $1, version = version + 1
 WHERE id = $2 AND version = $3;   -- $3 = 7

res, err := db.ExecContext(ctx,
    `UPDATE documents SET body=$1, version=version+1 WHERE id=$2 AND version=$3`,
    body, id, version)
if err != nil {
    return err
}
n, _ := res.RowsAffected()
if n == 0 {
    // Ноль строк = либо документа нет, либо version изменилась.
    // Это не ошибка БД, конфликт разбирает бизнес-логика:
    // перечитать, показать пользователю расхождение или смержить.
    return ErrConcurrentModification
}

Вместо целочисленной версии можно использовать updated_at (хуже: разрешение времени и переводы часов) или хеш содержимого (годится для HTTP ETag / If-Match: по сути та же оптимистичная блокировка, только вынесенная в протокол).

Табличные

Берутся автоматически под каждую команду. Запомни минимум: SELECT берёт ACCESS SHARE и конфликтует только с ACCESS EXCLUSIVE; INSERT/UPDATE/DELETE берут ROW EXCLUSIVE и не мешают друг другу на уровне таблицы (конфликты разрешаются построчно); VACUUM, ANALYZE и CREATE INDEX CONCURRENTLY берут SHARE UPDATE EXCLUSIVE и не мешают DML; ACCESS EXCLUSIVE (DROP, TRUNCATE, большинство ALTER TABLE, VACUUM FULL) блокирует вообще всё, включая SELECT.

Строчные

Их четыре: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE. Хранятся они не в общей памяти, а прямо в заголовке версии кортежа (плюс структура multixact, когда строку держат несколько транзакций). Выходит, число строчных блокировок не ограничено: можно заблокировать миллион строк и не бояться исчерпать память блокировок. А вот табличных блокировок конечное число (max_locks_per_transaction).

Advisory

pg_advisory_lock(key) и pg_try_advisory_xact_lock(key) вешают замок по произвольному числу, ни с какой строкой не связанный. Удобен как распределённый мьютекс: «только один инстанс сервиса выполняет эту фоновую задачу». Транзакционная форма (_xact_) снимается автоматически при завершении транзакции, её и используй; обычная требует явного unlock и легко теряется.

-- Кто кого блокирует прямо сейчас: первый запрос при инциденте
SELECT pid, now() - xact_start AS tx_age, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by, left(query, 90)
FROM   pg_stat_activity
WHERE  backend_type = 'client backend' AND state <> 'idle'
ORDER  BY tx_age DESC;

-- Что именно удерживается и в каком режиме
SELECT l.pid, l.locktype, l.mode, l.granted, c.relname
FROM   pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation
ORDER  BY l.granted, l.pid;

SELECT pg_cancel_backend(12345);     -- мягко: отменить запрос
SELECT pg_terminate_backend(12345);  -- жёстко: закрыть соединение

Как возникает

T1 обновляет строку A и получает на неё блокировку, затем хочет строку B. T2 в это же время обновила B и хочет A. Каждая ждёт ресурс, который держит другая, и получается цикл. Классический источник: перевод денег «со счёта X на счёт Y» и одновременно «с Y на X»; или два процесса, обновляющие набор строк в разном порядке (например, потому что один шёл ORDER BY id, а второй - ORDER BY created_at).

Как разрешает PostgreSQL

Транзакция, прождавшая блокировку дольше deadlock_timeout (по умолчанию 1 с), запускает детектор: тот строит граф «кто кого ждёт» и ищет в нём цикл. Если цикл нашёлся, одна из транзакций (обычно та, что запустила проверку) откатывается с ERROR: deadlock detected, SQLSTATE 40P01, а в лог пишется, какие процессы и запросы участвовали. Остальные продолжают работу. Проверка запускается по таймауту, а не при каждом ожидании, чтобы не жечь процессор - обычное ожидание блокировки ещё не дедлок.

Как проектировать

1. Единый порядок захвата. Главное правило: если операция трогает несколько строк, блокируй их в детерминированном порядке (по возрастанию первичного ключа), независимо от бизнес-направления операции.
2. Захватывать всё сразу одним SELECT ... WHERE id IN (...) ORDER BY id FOR UPDATE, а не последовательно по одной строке.
3. Короткие транзакции и никаких сетевых вызовов внутри - окно пересечения меньше.
4. Осторожно с ON CONFLICT и FK: параллельные вставки в таблицу с уникальным индексом и разным порядком значений тоже дают дедлоки.
5. Всё равно ретраить. Полностью исключить дедлоки в системе со сложными транзакциями нельзя, поэтому обработка 40P01 обязательна.

-- Плохо: порядок зависит от направления перевода
UPDATE accounts SET balance = balance - 100 WHERE id = $from;
UPDATE accounts SET balance = balance + 100 WHERE id = $to;

-- Хорошо: сначала захватываем оба счёта в фиксированном порядке
SELECT id FROM accounts WHERE id IN ($from, $to) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = $from;
UPDATE accounts SET balance = balance + 100 WHERE id = $to;

Пять последствий

1. Блокировка очистки. Autovacuum не имеет права удалить мёртвую версию строки, если она может быть нужна самому старому активному снапшоту. Одна транзакция, висящая часами, останавливает уборку по всей базе. Отсюда table bloat: таблица на 10 ГБ данных занимает 60 ГБ, индексы распухают, всё сканируется медленнее, кэш забит мусором.
2. Удержание блокировок. Все блокировки живут до конца транзакции. Если в начале был ALTER или FOR UPDATE, соседи будут ждать до самого COMMIT - а из-за честной FIFO-очереди за ними встанут и невинные SELECT.
3. Проблемы с репликами. При hot_standby_feedback = on долгий запрос на реплике удерживает xmin на мастере и мешает VACUUM уже там. При выключенном фидбеке запрос на реплике убьют с canceling statement due to conflict with recovery. Выбор между двумя неприятностями, и об этом полезно сказать вслух.
4. Transaction ID wraparound. Счётчик транзакций 32-битный; чтобы старые данные не «стали будущими», VACUUM замораживает старые кортежи. Самая старая транзакция задаёт горизонт заморозки, и если она живёт сутками, база приближается к аварийному режиму, когда PostgreSQL перестаёт принимать новые транзакции.
5. Больше конфликтов. Чем шире окно, тем выше вероятность дедлока и ошибки сериализации.

Особый случай: idle in transaction

Чаще всего транзакцию затягивает не тяжёлый запрос, а BEGIN, после которого приложение ушло делать HTTP-запрос, писать в Kafka или просто забыло закоммитить из-за пропущенного defer tx.Rollback(). Транзакция ничего не выполняет, но вредит точно так же. Это и есть главный аргумент против «сетевых вызовов внутри транзакции».

-- Диагностика
SELECT pid, state, now() - xact_start AS tx_age, now() - state_change AS in_state,
       left(query, 100)
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
ORDER  BY xact_start
LIMIT  20;

-- Профилактика. SET - на сессию; на роль - ALTER ROLE … SET, на сервер - ALTER SYSTEM
SET idle_in_transaction_session_timeout = '60s';
SET statement_timeout = '30s';
SET lock_timeout = '3s';