Транзакции в реляционных БД на практике: PostgreSQL, изоляция и нагрузочный стенд

PostgreSQL — то место, где управление изоляцией перестаёт быть теорией: уровень можно выбрать осознанно, и последствия выбора предсказуемы и измеримы. Есть честный SERIALIZABLE через SSI, есть MVCC-снимок под Repeatable Read, есть пессимистичные блокировки строк — и у каждого инструмента понятная цена. Но именно потому, что по умолчанию стоит не самый строгий уровень, аномалию легко получить на ровном месте: конкурентный инкремент баланса, проверка-затем-запись с инвариантом на две строки.

Это вторая статья серии «Транзакции и изоляция». В первой мы разобрали словарь — ACID/BASE, каталог аномалий (lost update, write skew), ANSI-уровни, snapshot isolation и SSI. Здесь теория не повторяется: вместо неё — Стенд №1, живое воспроизведение тех же аномалий под конкуренцией на PostgreSQL 18.4, с числами и лечением.

В статье

• MVCC и снимок в PostgreSQL
• Lost update: воспроизводим и чиним
• Write skew: где snapshot isolation бессилен
• SERIALIZABLE и обязательный retry
• Инструменты управления блокировками
• Контраст: MySQL/InnoDB
• Java: то же в JDBC
• Что дальше
• Источники

MVCC и снимок в PostgreSQL

Три уровня изоляции, которые реально различимы в PostgreSQL (Read Uncommitted синонимичен Read Committed), ложатся на MVCC по-разному, и разница — целиком в том, когда именно фиксируется снимок, против которого идёт чтение.

Read Committed (уровень по умолчанию) снимка на всю транзакцию не берёт: каждый отдельный запрос внутри транзакции видит свежие закоммиченные данные на момент своего старта, а не на момент начала транзакции. Отсюда non-repeatable read — это не баг, а прямое следствие контракта уровня: два SELECT подряд в одной транзакции могут увидеть разные значения одной строки, если между ними кто-то закоммитил UPDATE.

Repeatable Read в PostgreSQL — это snapshot isolation в терминологии первой статьи: снимок фиксируется на момент первого запроса транзакции и не меняется до конца, независимо от того, что коммитят другие транзакции параллельно. Отсюда — не только защита от non-repeatable read, но и от phantom read (PostgreSQL здесь строже буквы стандарта), и от lost update при read-modify-write в пределах одной строки, если её пишут через SELECT ... FOR UPDATE. Но SI не защищает от write skew — про это ниже, и это ключевая тема раздела.

Serializable использует тот же MVCC-снимок, что и Repeatable Read, но вдобавок отслеживает опасные rw-зависимости между конкурентными транзакциями (SSI, см. первую статью) и откатывает одну из транзакций с SQLSTATE 40001, если граф зависимостей складывается в паттерн, доказанно ведущий к нарушению сериализуемости. Это не блокировка на чтение — это постфактум-проверка при коммите.

Дальше — не пересказ этой механики, а её поведение на реальном стенде под конкуренцией.

Lost update: воспроизводим и чиним

Сценарий предельно простой: одна строка accounts.balance, 16 конкурентных воркеров, каждый выполняет 100 инкрементов. Ожидаемый итог — 16 × 100 = 1600.

Наивная реализация на Read Committed: читаем баланс, прибавляем единицу в приложении, записываем обратно — без какой-либо блокировки строки.

tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.ReadCommitted})
if err != nil {
    return
}
var bal int64
if err := tx.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = 1").Scan(&bal); err != nil {
    tx.Rollback(ctx)
    return
}
tx.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = 1", bal+1)
tx.Commit(ctx)

На Стенде №1 (PostgreSQL 18.4, 16 воркеров × 100 итераций) это даёт итог 200 вместо 1600 — потеряно 1400 инкрементов, 87.5%. Каждая пара конкурентных транзакций читает одно и то же значение, и более поздний COMMIT безусловно затирает более ранний — ровно та lost update из первой статьи, но теперь не в таблице на бумаге, а с числом на реальной нагрузке.

Три способа вылечить, все проверены на том же стенде:

SELECT ... FOR UPDATE — пессимистичная блокировка строки на чтении, остаёмся на Read Committed:

tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.ReadCommitted})
if err != nil {
    return
}
var bal int64
if err := tx.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = 1 FOR UPDATE").Scan(&bal); err != nil {
    tx.Rollback(ctx)
    return
}
tx.Exec(ctx, "UPDATE accounts SET balance = $1 WHERE id = 1", bal+1)
tx.Commit(ctx)

Вторая транзакция, дошедшая до FOR UPDATE по той же строке, просто ждёт снятия блокировки первой — потерь нет, итог 1600.

Атомарный UPDATE — вообще убираем чтение из приложения, арифметику делает сама СУБД одним оператором:

UPDATE accounts SET balance = balance + 1 WHERE id = 1;

Здесь нечему теряться в принципе — read-modify-write происходит внутри одного statement, под защитой собственной строчной блокировки СУБД, без отдельного захвата на уровне приложения.

SERIALIZABLE + retry — тот же read-modify-write код, что naive, но на уровне SERIALIZABLE, и с обязательной обёрткой повтора на 40001 (код разобран в следующем разделе).

Throughput и доля потерь по стратегиям, 16 воркеров × 100 инкрементов, PostgreSQL 18.4.

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

Полный стенд №1 — Go-нагрузчик и Java-пример — лежит в digital-cookbook, transactions/relational/ (docker compose up + go run .; проверено на PostgreSQL 18.4).

Write skew: где snapshot isolation бессилен

Сценарий — тот самый «два дежурных врача» из первой статьи, но теперь под конкуренцией: два дежурных, инвариант «после снятия с дежурства должен остаться хотя бы один», 200 раундов. В каждом раунде оба воркера синхронизированы барьером — оба читают снимок числа дежурных до того, как любой из них успеет записать, так что снимки гарантированно пересекаются.

На REPEATABLE READ (то есть snapshot isolation в PostgreSQL): оба воркера видят «на дежурстве двое», оба независимо решают, что снять себя можно — и оба пишут в свою строку. Конфликта записей нет (это разные строки), поэтому snapshot isolation ничего не блокирует и ничего не откатывает. Результат на стенде — инвариант нарушен в 200 из 200 раундов (100%), serialization_aborts=0. Это не редкий edge case и не невезение — на этой конструкции барьера аномалия воспроизводится каждый раз.

На SERIALIZABLE: та же логика, тот же барьер, но SSI отслеживает rw-зависимости между двумя транзакциями (каждая читает то, что могла бы записать другая) и на коммите одной из них видит опасный паттерн. Результат — 0 нарушений инварианта (0%), но 200 serialization-абортов на 200 раундов: SSI откатывает ровно одну из двух транзакций в каждом раунде, вторая коммитится и оставляет хотя бы одного дежурного.

Доля раундов с нарушенным инвариантом, 200 раундов, PostgreSQL 18.4.

Вывод здесь принципиально другой, чем в разделе про lost update: write skew чинится только настоящей сериализуемостью — либо SERIALIZABLE, либо явной материализацией конфликта через SELECT ... FOR UPDATE по общей строке-«замку» (например, отдельная строка-счётчик дежурств, которую обе транзакции блокируют перед проверкой инварианта). Заблокировать в этом сценарии «свою» строку бессмысленно — как и написано в первой статье, конфликт лежит между строками, а не внутри одной, и обычная построчная блокировка его попросту не видит.

SERIALIZABLE и обязательный retry

SQLSTATE 40001 (serialization_failure) от PostgreSQL — это не ошибка в обычном смысле, а штатный сигнал «повтори транзакцию целиком заново». Приложение, использующее SERIALIZABLE, обязано иметь retry-обёртку — без неё транзакция просто теряется при первом же конфликте.

// isSerializationFailure — код 40001 (serialization_failure) или 40P01 (deadlock_detected).
func isSerializationFailure(err error) bool {
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) {
        return pgErr.Code == "40001" || pgErr.Code == "40P01"
    }
    return false
}

// retryTx выполняет fn в транзакции нужного уровня, повторяя на 40001/40P01.
func retryTx(ctx context.Context, pool *pgxpool.Pool, level pgx.TxIsoLevel, m *metrics, fn func(pgx.Tx) error) {
    for attempt := 0; ; attempt++ {
        tx, err := pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: level})
        if err != nil {
            return
        }
        err = fn(tx)
        if err == nil {
            err = tx.Commit(ctx)
        } else {
            tx.Rollback(ctx)
        }
        if err == nil {
            return
        }
        if isSerializationFailure(err) {
            atomic.AddInt64(&m.retries, 1)
            continue
        }
        return
    }
}

Важная деталь: и 40001 (serialization_failure от SSI), и 40P01 (deadlock_detected) требуют одного и того же ответа приложения — повторить транзакцию с начала. Deadlock в PostgreSQL всегда разрешается принудительным откатом одной из сторон, а не зависанием, поэтому обработка обоих кодов одной retry-обёрткой оправдана.

Цена видна на цифрах из раздела про lost update: на горячей строке (16 воркеров, один и тот же accounts.balance) SERIALIZABLE даёт 16764 retry на 1600 успешных транзакций — то есть около 10 повторов на каждую дошедшую до коммита транзакцию, и throughput проседает до 179 tx/s против 322 tx/s у атомарного UPDATE. Это не брак SSI, а прямое следствие того, что конфликтующие транзакции здесь пишут в буквально одну и ту же строку — граф rw-зависимостей переполнен, и SSI откатывает почти всё подряд, включая часть ложноположительных случаев.

Отсюда практическое правило выбора. SERIALIZABLE оправдан, когда инвариант сложный (зависит от нескольких строк или таблиц, как в write skew) и конкуренция именно на конфликтующих строках невысокая — тогда retry редкие, а корректность бесплатна по сравнению с ручным проектированием блокировок. SERIALIZABLE не оправдан на горячем счётчике или другой одиночной строке под высокой конкуренцией — там дешевле и быстрее атомарный UPDATE или SELECT ... FOR UPDATE, потому что конфликт тривиально сводится к одной строке и не нуждается в отслеживании зависимостей между транзакциями.

Инструменты управления блокировками

Помимо уровней изоляции, у PostgreSQL есть отдельный набор явных инструментов для управления блокировками.

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

Advisory locks (pg_advisory_xact_lock(key)) — прикладная блокировка по произвольному числовому ключу, которая существует независимо от того, есть ли вообще строка в базе. Полезны, когда критическая секция не привязана к конкретной существующей записи — например, блокировка на этапе создания записи, которой ещё нет, или на составной бизнес-ключ, для которого нет отдельного индекса. pg_advisory_xact_lock снимается автоматически при коммите или откате транзакции — в отличие от сессионной версии, которую нужно снимать явно.

statement_timeout и lock_timeout — защита от того, что транзакция зависнет в ожидании блокировки навсегда. lock_timeout ограничивает именно время ожидания блокировки (после которого запрос завершается ошибкой, а не всё выполнение statement), statement_timeout — общее время выполнения запроса. Оба стоит выставлять на уровне сессии или роли, а не полагаться на дефолтное «ждать бесконечно».

Deadlock в PostgreSQL детектится автоматически — но не отдельным фоновым процессом, который «по расписанию» осматривает систему: проверку запускает сам backend, который ждёт блокировку. Прождав deadlock_timeout (по умолчанию 1 с), он строит граф ожиданий и, если находит цикл, принудительно откатывает одну из сторон с кодом 40P01, освобождая вторую. Отсюда практическое следствие: сама проверка не бесплатна, поэтому слишком маленький deadlock_timeout заставляет ждущие backend’ы регулярно тратиться на неё там, где блокировка освободилась бы сама. В логе сервера при этом видны обе вовлечённые транзакции, запросы, которые они выполняли, и какие именно блокировки каждая удерживала и на какие ждала — этого обычно достаточно, чтобы понять, в каком порядке транзакции захватывали ресурсы, и выровнять этот порядок в коде (самый надёжный способ вообще избежать deadlock — единый порядок захвата блокировок во всех транзакциях, которые трогают одни и те же строки).

Контраст: MySQL/InnoDB

Здесь стоит на секунду выйти за пределы PostgreSQL — не для полного разбора (это тема отдельного материала), а чтобы напомнить главный тезис первой статьи серии: одно и то же название уровня изоляции — не одна и та же реализация. У InnoDB (MySQL 8.4) уровень по умолчанию — REPEATABLE READ, и это часто удивляет разработчиков, ожидающих READ COMMITTED по аналогии с PostgreSQL. Но и «механизм другой» надо уточнить, иначе получится карикатура. В InnoDB на REPEATABLE READ уживаются два разных пути. Обычный, неблокирующий SELECT читает consistent snapshot — это MVCC без единой блокировки, ровно как в PostgreSQL, и phantom read на этом пути исключён именно снимком. А вот locking reads (SELECT ... FOR UPDATE / FOR SHARE) и DML (UPDATE/DELETE/INSERT) берут next-key locks — комбинацию построчной блокировки и gap lock (блокировки диапазона между соседними индексными записями), не давая вставить строку, которая попала бы в ранее прочитанный диапазон. То есть next-key locks — не описание «всех чтений на RR», а механика блокирующего пути. Итоговый эффект по phantom read формально похож на PostgreSQL, но побочные эффекты на конкуренцию — разные: gap locks блокируют диапазоны вставки для других транзакций там, где PostgreSQL не блокирует никого, и паттерны deadlock у InnoDB получаются свои именно из-за gap locks. Возвращаться к этому подробнее — за пределами этой статьи, здесь важно только не переносить интуицию с одного движка на другой автоматически.

Java: то же в JDBC

Тот же паттерн — уровень изоляции плюс обязательный retry на 40001 — переносится в JDBC почти дословно, меняется только API клиента.

static void incrementSerializable(Connection c) throws SQLException {
    c.setAutoCommit(false);
    c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    while (true) {
        try {
            long bal;
            try (ResultSet rs = c.createStatement().executeQuery(
                    "SELECT balance FROM accounts WHERE id = 1")) {
                rs.next();
                bal = rs.getLong(1);
            }
            try (PreparedStatement ps = c.prepareStatement(
                    "UPDATE accounts SET balance = ? WHERE id = 1")) {
                ps.setLong(1, bal + 1);
                ps.executeUpdate();
            }
            c.commit();
            return;
        } catch (SQLException e) {
            c.rollback();
            if ("40001".equals(e.getSQLState())) continue; // сериализационный конфликт — повторяем
            throw e;
        }
    }
}

На небольшом прогоне того же сценария (8 потоков × 50 итераций, ожидаемый итог 400) разница между naive и SERIALIZABLE + retry видна так же наглядно, как в Go-нагрузчике: naive на Read Committed без блокировки дал итог 50 из 400 (потеряно 350), а SERIALIZABLE с retry-обёрткой на 40001 — корректные 400. Механизм ровно тот же, что и на стенде из предыдущих разделов — меняется только то, каким API клиент задаёт уровень изоляции и вычитывает код ошибки.

Что дальше

Дальше в серии — KV и документные хранилища, где само слово «транзакция» начинает значить нечто совсем другое: от MULTI/EXEC в Redis, где очередь исполняется сериализованно, не впуская чужие команды, но rollback нет вовсе (а read-modify-write вне блока требует WATCH или Lua), до multi-document транзакций в MongoDB поверх snapshot isolation.

Источники

• PostgreSQL Documentation: Transaction Isolation
• PostgreSQL Documentation: Explicit Locking
• MySQL 8.4 Reference Manual: InnoDB Locking
• pgx — драйвер PostgreSQL для Go
• Стенд №1: digital-cookbook/databases/transactions/relational