Базы данных/Продвинутый ~ 65 мин

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

Транзакция группирует несколько операций с базой в одну логическую единицу. Для backend-разработчика это не абстрактная теория: почти каждый денежный перевод, смена статуса, списание лимита, запись прогресса и публикация события требует ответа на вопрос: "Что будет, если в это же время придет второй запрос?"

Простой пример:

BEGIN;

UPDATE accounts
SET balance_cents = balance_cents - 1000
WHERE id = 1;

UPDATE accounts
SET balance_cents = balance_cents + 1000
WHERE id = 2;

COMMIT;

Если между двумя UPDATE случится сбой, нельзя оставить деньги списанными с одного счета и не зачисленными на другой. Транзакция делает изменение атомарным: либо оба UPDATE сохранены, либо оба отменены.

ACID

Свойство	Смысл	Пример
Atomicity	Все операции транзакции применяются целиком или не применяются	Перевод денег не остается наполовину
Consistency	Транзакция переводит базу из одного корректного состояния в другое	Constraints не нарушены
Isolation	Параллельные транзакции не мешают друг другу сверх выбранных гарантий	Два запроса не ломают счетчик
Durability	После commit данные переживают сбой	Запись не исчезает после ответа клиенту

Consistency в ACID - не то же самое, что "eventual consistency" в распределенных системах. Здесь речь о том, что constraints и инварианты базы не должны быть нарушены успешно завершенной транзакцией.

Transaction control

Базовые команды:

BEGIN;
-- SQL statements
COMMIT;

Откат:

BEGIN;

UPDATE orders
SET status = 'paid'
WHERE id = $1;

ROLLBACK;

Savepoint:

BEGIN;

INSERT INTO users (email, name) VALUES ('a@example.com', 'Anna');

SAVEPOINT before_optional_profile;

INSERT INTO user_profiles (user_id, bio) VALUES (42, '...');

ROLLBACK TO SAVEPOINT before_optional_profile;

COMMIT;

В Go не забывайте передавать context.Context, всегда завершать транзакцию и не смешивать tx.ExecContext с db.ExecContext внутри одного use case.

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

if _, err := tx.ExecContext(ctx, debitQuery, fromID, amount); err != nil {
    return err
}
if _, err := tx.ExecContext(ctx, creditQuery, toID, amount); err != nil {
    return err
}

return tx.Commit()

defer tx.Rollback() после успешного Commit обычно вернет ошибку, которую можно игнорировать. Зато при раннем return транзакция не останется открытой.

Изоляция и аномалии

Что такое изоляция

Isolation отвечает на вопрос: какие изменения других транзакций видит текущая транзакция.

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

PostgreSQL использует MVCC: читатели обычно не блокируют писателей, а писатели не блокируют обычных читателей. Запрос видит snapshot данных, подходящий выбранному уровню изоляции. Но MVCC не отменяет блокировки полностью: UPDATE, DELETE, SELECT FOR UPDATE, unique checks, DDL и explicit locks все равно конфликтуют.

ANSI phenomena

Классические уровни изоляции описывают, какие явления запрещены.

Аномалия	Что происходит
Dirty read	Транзакция читает незакоммиченные данные другой транзакции
Non-repeatable read	Повторное чтение той же строки дает новое значение
Phantom read	Повторный запрос по условию видит новый набор строк
Serialization anomaly	Итог нельзя объяснить никаким последовательным порядком транзакций

Таблица для PostgreSQL:

Уровень	Dirty read	Non-repeatable read	Phantom read	Serialization anomaly
Read Uncommitted	Не происходит в PostgreSQL	Возможен	Возможен	Возможна
Read Committed	Не происходит	Возможен	Возможен	Возможна
Repeatable Read	Не происходит	Не происходит	Не происходит в PostgreSQL	Возможна
Serializable	Не происходит	Не происходит	Не происходит	Не происходит

Важно для собеседований: SQL standard задает минимальные запреты, а конкретная СУБД может давать более сильные гарантии. В PostgreSQL READ UNCOMMITTED ведет себя как READ COMMITTED, а REPEATABLE READ реализован через snapshot isolation и не допускает phantom read в обычном смысле.

Dirty read

Dirty read: одна транзакция читает данные, которые другая еще не закоммитила.

T1: BEGIN;
T1: UPDATE accounts SET balance = 0 WHERE id = 1;

T2: SELECT balance FROM accounts WHERE id = 1; -- видит 0

T1: ROLLBACK;

Если T2 приняла решение на основе 0, она использовала данные, которых никогда не было в committed-состоянии.

PostgreSQL не допускает dirty read даже при READ UNCOMMITTED.

Non-repeatable read

Одна транзакция дважды читает одну строку и получает разные значения.

T1: BEGIN ISOLATION LEVEL READ COMMITTED;
T1: SELECT balance FROM accounts WHERE id = 1; -- 1000

T2: UPDATE accounts SET balance = 500 WHERE id = 1;
T2: COMMIT;

T1: SELECT balance FROM accounts WHERE id = 1; -- 500
T1: COMMIT;

В READ COMMITTED каждый statement получает новый snapshot, поэтому это нормально. В REPEATABLE READ T1 продолжила бы видеть старую версию строки.

Phantom read

Phantom read: повторный запрос по условию видит другой набор строк.

T1: BEGIN ISOLATION LEVEL READ COMMITTED;
T1: SELECT count(*) FROM orders WHERE status = 'paid'; -- 10

T2: INSERT INTO orders(user_id, status, total_cents)
    VALUES (1, 'paid', 1000);
T2: COMMIT;

T1: SELECT count(*) FROM orders WHERE status = 'paid'; -- 11
T1: COMMIT;

Это отличается от non-repeatable read тем, что меняется не уже прочитанная строка, а множество строк, подходящих под условие.

Lost update

Lost update: две транзакции читают одно значение, обе считают новое, одна перезаписывает другую.

balance = 100

T1: SELECT balance FROM accounts WHERE id = 1; -- 100
T2: SELECT balance FROM accounts WHERE id = 1; -- 100

T1: UPDATE accounts SET balance = 70 WHERE id = 1;
T2: UPDATE accounts SET balance = 50 WHERE id = 1;

Итог: 50, хотя ожидали 20

Лучше писать атомарный UPDATE, где вычисление происходит в базе:

UPDATE accounts
SET balance_cents = balance_cents - $1
WHERE id = $2
  AND balance_cents >= $1
RETURNING balance_cents;

Если RETURNING не вернул строку, денег недостаточно или аккаунта нет. Такой запрос не требует сначала читать balance в Go, считать, а потом писать.

Другой вариант - optimistic locking:

UPDATE accounts
SET balance_cents = $new_balance,
    version = version + 1
WHERE id = $id
  AND version = $old_version;

Если обновлено 0 строк, кто-то изменил запись раньше. Use case перечитывает данные и повторяет операцию или возвращает conflict.

Write skew

Write skew особенно любят на собеседованиях, потому что он показывает разницу между "строки не конфликтуют" и "бизнес-инвариант нарушен".

Инвариант: в дежурстве должна быть хотя бы одна активная смена врача.

CREATE TABLE shifts (
    doctor_id bigint PRIMARY KEY,
    on_call boolean NOT NULL
);

Начальное состояние:

doctor_id	on_call
1	true
2	true

Две транзакции:

T1: видит, что on_call врачей = 2
T2: видит, что on_call врачей = 2

T1: UPDATE shifts SET on_call = false WHERE doctor_id = 1;
T2: UPDATE shifts SET on_call = false WHERE doctor_id = 2;

Обе коммитятся.
Итог: on_call врачей = 0

Они обновляли разные строки, поэтому row lock не конфликтовал. Но инвариант был про набор строк.

Варианты защиты:

SERIALIZABLE и retry при SQLSTATE 40001;
explicit lock на общий ресурс, например строку departments;
моделировать инвариант так, чтобы конфликт был на одной строке;
использовать constraint, если правило выражается декларативно.

Пример с общей строкой:

BEGIN;

SELECT *
FROM departments
WHERE id = $department_id
FOR UPDATE;

-- Теперь проверка и изменение смен происходят под lock общего ресурса.

COMMIT;

Read skew

Read skew: транзакция читает несколько связанных значений, но видит их из разных моментов времени.

Пример: деньги переводят между счетами, а отчет читает счета двумя запросами.

accounts:
  A = 100
  B = 100

T1: SELECT balance FROM accounts WHERE id = 'A'; -- 100

T2: BEGIN;
T2: UPDATE accounts SET balance = 50 WHERE id = 'A';
T2: UPDATE accounts SET balance = 150 WHERE id = 'B';
T2: COMMIT;

T1: SELECT balance FROM accounts WHERE id = 'B'; -- 150

T1 видит сумму 250, хотя committed-состояния с такой суммой не было.

В READ COMMITTED это возможно, потому что каждый statement видит новый snapshot. Для консистентного отчета используйте REPEATABLE READ или один statement, который читает все нужное сразу.

Уровни изоляции на практике

Read Committed

Это default в PostgreSQL. Каждый statement видит данные, committed до начала statement. Два SELECT внутри одной транзакции могут увидеть разные snapshots.

Подходит:

простые CRUD-операции;
update конкретной строки;
операции, где каждый statement самодостаточен;
большинство web-request'ов при правильных constraints и атомарных UPDATE.

Опасно:

сложные проверки по набору строк;
отчеты из нескольких запросов;
"сначала SELECT, потом UPDATE" без lock или version;
бизнес-инварианты вроде "не больше N активных записей".

Repeatable Read

Транзакция видит snapshot на момент начала первой команды. Повторные чтения стабильны. В PostgreSQL этот уровень не допускает phantom read, но возможны serialization anomalies вроде write skew.

Подходит:

консистентные отчеты;
операции, где нужно стабильное чтение набора данных;
длинные read-only задачи, если они не держат лишние ресурсы слишком долго.

Нужно помнить: update строки, измененной после snapshot, может завершиться ошибкой сериализации. Приложение должно уметь retry.

Serializable

Самая сильная изоляция: результат успешно закоммиченных транзакций должен быть эквивалентен некоторому последовательному выполнению.

В PostgreSQL это Serializable Snapshot Isolation. СУБД отслеживает опасные зависимости и может откатить одну из транзакций с SQLSTATE 40001.

Подходит:

сложные инварианты, которые трудно закрыть простыми constraints;
финансовые или лимитные операции, где проще написать логику как для последовательного выполнения;
небольшие критичные транзакции с нормальным retry-механизмом.

Цена:

возможны serialization failures;
нужен общий retry-wrapper;
длинные транзакции повышают риск конфликтов;
плохие планы и последовательные сканы могут увеличить число predicate locks.

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

Блокировки

Блокировки отвечают за координацию конкурентных операций.

Row-level locks

SELECT ... FOR UPDATE блокирует выбранные строки для последующего изменения.

BEGIN;

SELECT id, balance_cents
FROM accounts
WHERE id = $1
FOR UPDATE;

UPDATE accounts
SET balance_cents = balance_cents - $2
WHERE id = $1;

COMMIT;

Пока транзакция не завершится, другая транзакция, которая хочет обновить эту же строку или взять несовместимый lock, будет ждать.

Варианты:

SELECT ... FOR UPDATE;
SELECT ... FOR NO KEY UPDATE;
SELECT ... FOR SHARE;
SELECT ... FOR KEY SHARE;

Для очередей иногда используют SKIP LOCKED:

WITH job AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs
SET status = 'running'
WHERE id IN (SELECT id FROM job)
RETURNING *;

Несколько workers могут параллельно брать разные jobs, не ожидая уже заблокированные строки.

Table-level locks

DDL, TRUNCATE, некоторые ALTER TABLE, CREATE INDEX и explicit LOCK TABLE могут брать table-level locks. Самый сильный ACCESS EXCLUSIVE конфликтует практически со всем.

Для backend-разработчика главный вывод: миграции - это production-код. Нельзя бездумно запускать тяжелый ALTER TABLE на большой таблице днем и удивляться остановке API.

Deadlock

Deadlock возникает, когда транзакции ждут друг друга по кругу.

T1: locked account 1
T2: locked account 2
T1: wants account 2
T2: wants account 1

Защита:

брать locks в одинаковом порядке;
держать транзакции короткими;
не делать сетевые вызовы внутри транзакции;
иметь retry для deadlock errors;
обновлять пачки строк в стабильном порядке.

Для перевода между счетами:

SELECT id
FROM accounts
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;

Сначала блокируем обе строки в одном порядке, затем меняем balances.

Optimistic и pessimistic concurrency

Pessimistic

Пессимистический подход заранее блокирует данные:

SELECT *
FROM orders
WHERE id = $1
FOR UPDATE;

Подходит, если конфликтов много или цена конфликта высокая. Минус - ожидание, deadlocks, снижение параллелизма.

Optimistic

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

UPDATE orders
SET status = 'paid',
    version = version + 1
WHERE id = $1
  AND version = $2
  AND status = 'draft';

Подходит, если конфликтов мало. Минус - нужно обрабатывать "0 rows affected" и повторять операцию или возвращать клиенту 409 Conflict.

Типичная Go-модель:

type Order struct {
    ID      int64
    Status  string
    Version int64
}

И repository:

res, err := tx.ExecContext(ctx, `
    UPDATE orders
    SET status = $1, version = version + 1
    WHERE id = $2 AND version = $3
`, nextStatus, order.ID, order.Version)
if err != nil {
    return err
}

affected, err := res.RowsAffected()
if err != nil {
    return err
}
if affected == 0 {
    return ErrConcurrentModification
}

Constraints как concurrency primitive

Частая ошибка: проверять уникальность через SELECT, потом вставлять.

-- Ненадежно при конкуренции.
SELECT id FROM users WHERE email = $1;
INSERT INTO users(email) VALUES ($1);

Два запроса могут одновременно увидеть, что email свободен. Правильная защита - unique constraint:

CREATE UNIQUE INDEX users_email_unique
ON users (lower(email));

И затем:

INSERT INTO users(email, name)
VALUES ($1, $2)
ON CONFLICT DO NOTHING
RETURNING id;

Здесь пример намеренно использует общий DO NOTHING: он сработает при любом unique/exclusion conflict. Если нужен точный conflict target для expression index, синтаксис должен совпасть с индексным выражением. Иногда проще завести нормализованную колонку email_normalized или generated column и повесить unique constraint на нее.

База должна быть последней линией защиты. Go-код может дать красивую ошибку раньше, но constraint гарантирует корректность при гонке.

Retry

При SERIALIZABLE, deadlock или optimistic conflict retry должен повторять всю транзакцию, а не только последний statement.

Псевдокод:

func WithTxRetry(ctx context.Context, maxAttempts int, fn func(tx Tx) error) error {
    for attempt := 1; attempt <= maxAttempts; attempt++ {
        err := runInTx(ctx, fn)
        if err == nil {
            return nil
        }
        if !isRetryable(err) {
            return err
        }
        sleepBackoff(attempt)
    }
    return ErrTooMuchContention
}

Retryable обычно:

serialization failure, SQLSTATE 40001;
deadlock detected, SQLSTATE 40P01;
optimistic conflict, если use case допускает повтор.

Не все ошибки можно повторять. Unique violation при регистрации email - это бизнес-конфликт, а не повод бесконечно retry.

Retry, idempotency и side effects

Production retry опасен тем, что повторяет не только SQL, но и бизнес-намерение. Перед тем как добавить retry-wrapper, senior reviewer спрашивает: "Если эта операция выполнится дважды, что именно сломается?"

Матрица решений:

Ситуация	Retry?	Что требуется
`40001` / `40P01` до внешних side effects	Да	Повторить весь use case в новой transaction
Optimistic conflict при смене статуса	Иногда	Перечитать состояние, проверить, что намерение всё ещё валидно
Timeout клиента после неизвестного `COMMIT`	Не слепо	Idempotency key или повторное чтение результата операции
Unique violation на email/order number	Обычно нет	Вернуть business conflict или существующий ресурс
HTTP-вызов провайдеру уже отправлен	Только идемпотентно	Provider idempotency key, outbox/inbox или reconciliation
Ошибка после записи в БД, но до публикации event	Не простым retry	Transactional outbox или восстановимый publisher

Idempotency key должен жить в базе рядом с результатом операции, а не только в Redis/cache. Типичный паттерн:

CREATE TABLE idempotency_keys (
    key text PRIMARY KEY,
    operation text NOT NULL,
    request_hash text NOT NULL,
    response_json jsonb,
    status text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

Если повторный запрос приходит с тем же key и тем же request hash, API возвращает сохранённый результат или продолжает безопасное завершение. Если key тот же, а тело другое, это 409 Conflict: клиент переиспользовал ключ для другого намерения.

Side effects нельзя делать "внутри надежной транзакции" простым HTTP-вызовом. База может откатиться, сеть может зависнуть, клиент может не получить ответ, а внешний сервис уже принял команду. Для событий и интеграций часто используют transactional outbox:

BEGIN
  update order status
  insert into outbox_events(order_id, type, payload, dedupe_key)
COMMIT

background publisher:
  read unpublished events
  publish with dedupe key
  mark published

Outbox не делает внешний мир exactly-once. Он делает запись факта и намерения атомарной в вашей базе, а потребителей заставляет быть идемпотентными по dedupe_key/event id.

Retry budget тоже часть корректности:

ограничьте maxAttempts, иначе contention превратится в retry storm;
проверяйте ctx.Err() перед новой попыткой и перед sleep;
используйте exponential backoff с jitter, а не одинаковый sleep для всех запросов;
не повторяйте после истечения request deadline;
логируйте число попыток, SQLSTATE и финальный исход;
считайте метрики retry/abort отдельно от обычных ошибок.

Если retry нужен только чтобы "скрыть" постоянные deadlocks, это не лечение. Нужно искать порядок lock'ов, долгие transactions, плохие планы, hot rows и missing indexes.

Практические правила

Не держите транзакцию дольше нужного

Плохой use case:

BEGIN
  SELECT order FOR UPDATE
  HTTP call to payment provider
  UPDATE order
COMMIT

Пока внешний HTTP-запрос висит, база держит locks. Это увеличивает latency, deadlocks и contention.

Лучше:

BEGIN
  mark payment attempt as pending
COMMIT

HTTP call to payment provider

BEGIN
  finalize payment attempt idempotently
  update order
COMMIT

Да, код сложнее. Зато база не ждет сеть.

Все изменения, которые должны быть атомарными, находятся в одной транзакции?
Транзакция короткая и не делает внешние HTTP/RPC-вызовы?
Все SQL внутри use case выполняется через tx, а не случайно через db?
Бизнес-уникальность защищена UNIQUE, а не только SELECT before INSERT?
Для read-modify-write есть atomic update, FOR UPDATE или version?
Для инварианта по множеству строк выбран подход: constraint, lock общего ресурса или SERIALIZABLE?
Retry повторяет всю транзакцию?
Ошибки 40001, 40P01, unique violation и foreign key violation маппятся осмысленно?
Миграции не держат сильные locks дольше ожидаемого?
Есть timeout через context.Context?

Что запомнить

Транзакция защищает атомарность, но длинная транзакция сама становится production-проблемой.
READ COMMITTED видит новый snapshot на каждый statement; REPEATABLE READ держит snapshot транзакции.
Lost update лечится atomic update, FOR UPDATE, optimistic locking или более строгой изоляцией.
Write skew часто требует SERIALIZABLE, constraint или lock общего ресурса.
Serialization failure и deadlock - повод повторить всю транзакцию, а не один последний запрос.

Практика

Смоделируйте lost update на таблице accounts и исправьте через atomic UPDATE.
Реализуйте optimistic locking через колонку version.
Напишите транзакцию перевода денег и перечислите, какие ошибки можно retry.
Найдите во внешнем HTTP/RPC-вызове место, которое нельзя держать внутри transaction.

Интерактивная практика

Quiz+10 XP

Что нужно повторять после serialization_failure в PostgreSQL?

Только последний SELECT
Только последний UPDATE
Только COMMIT
Весь use case в новой транзакции

Predict+15 XP

Что выведет этот код?

WITH errors(sqlstate) AS (
    VALUES ('40001'), ('23503')
)
SELECT CASE
    WHEN sqlstate IN ('40001', '40P01') THEN 'retry'
    ELSE 'fail'
END AS decision
FROM errors;

Полезные источники

PostgreSQL Documentation: Transaction Isolation
PostgreSQL Documentation: Explicit Locking
PostgreSQL Documentation: LOCK
PostgreSQL Documentation: Transaction Processing

ACIDtransactionsisolationanomalieslocksMVCC