PostgreSQL транзакции и уровни изоляции: практические ловушки под нагрузкой

В PostgreSQL транзакции выглядят «просто» на уровне интерфейса: BEGIN, набор SELECT/INSERT/UPDATE/DELETE, COMMIT. Но под нагрузкой начинаются проблемы, которые сложно отладить постфактум: пропадающие строки в отчётах, повторяющиеся записи, неверные итоговые суммы, гонки между задачами очереди, «невозможные» состояния, которые появляются только при конкуренции.

Центр этой боли — уровни изоляции и аномалии, которые допускаются на каждом уровне. PostgreSQL по умолчанию использует Read Committed — он хорош в большинстве случаев, но не гарантирует того, что ожидают авторы бизнес-логики из других систем (или после прочтения старых статей, где акцент на одной «теоретически идеальной» настройке).

Ниже разберём Read Committed, Repeatable Read, Serializable, посмотрим на конкретные аномалии и реальные паттерны чтения/записи, которые чаще всего ломаются. В конце будет практический чек-лист.

Как PostgreSQL понимает изоляцию: не только «уровень», но и модель MVCC

PostgreSQL использует MVCC (Multi-Version Concurrency Control): строки версионируются, и каждый запрос видит «снимок» данных в зависимости от уровня изоляции.

Ключевой практический момент: в PostgreSQL «изоляция» означает, какие аномалии возможны при параллельных транзакциях. В реальности вы сталкиваетесь не с абстрактными «теоретическими» эффектами, а с тем, что ваши бизнес-вычисления и проверочные запросы перестают соответствовать ожиданиям.

Термины аномалий, которые встречаются чаще всего

• Dirty read — чтение незакоммиченных данных. В PostgreSQL на всех стандартных уровнях изоляции этого нет (из-за MVCC).
• Non-repeatable read — повторное чтение одной и той же строки даёт разные значения (например, после коммита другой транзакции).
• Phantom read — «призрачные» строки: повторный запрос, который возвращает набор строк по условию, начинает возвращать новые строки, появившиеся из-за параллельных вставок/изменений.
• Write skew / lost update — потеря логической целостности при конкурентных проверках и последующих действиях (часто это не «ошибка СУБД», а ошибка композиции проверок и записи).
• Serialization failure — на Serializable транзакция может быть прервана для предотвращения «невозможных» аномалий.

Read Committed: быстрый и «коварный» по отношению к повторным чтениям

Read Committed (по умолчанию) обеспечивает: каждый отдельный оператор (statement) видит консистентный снимок на момент начала этого statement. Между двумя запросами в одной транзакции параллельная транзакция может закоммитить изменения — и второй запрос увидит их.

Аномалия 1: Non-repeatable read

Рассмотрим ситуацию: вы в транзакции дважды читаете счётчик и ожидаете, что он не изменится.

-- Транзакция A (уровень Read Committed)
BEGIN;

SELECT balance FROM accounts WHERE id = 1;  -- statement #1

-- В этот момент транзакция B меняет balance и делает COMMIT

SELECT balance FROM accounts WHERE id = 1;  -- statement #2

COMMIT;

На уровне Read Committed второе чтение может вернуть другое значение, даже если логически вы ожидали «тот же мир». Это и есть non-repeatable read.

Почему это важно в бизнесе: отчёты, расчёты комиссий, проверки «если баланс >= X, то …» могут стать логически несогласованными, если вы делаете несколько шагов через отдельные SELECT и при этом рассчитываете на неизменность данных во время транзакции.

Аномалия 2: Phantom read (в наборе)

BEGIN;

-- statement #1
SELECT sum(amount) 
FROM payments 
WHERE user_id = 42 AND status = 'pending';

-- параллельно другая транзакция добавляет/меняет pending-платежи и делает COMMIT

-- statement #2
SELECT sum(amount) 
FROM payments 
WHERE user_id = 42 AND status = 'pending';

COMMIT;

sum(...) может отличаться в зависимости от времени выполнения statement #2. Это особенно заметно в задачах биллинга, проверки лимитов, построении инкрементальных представлений.

Практический вывод для Read Committed

Если вы используете Read Committed, то в транзакции нужно исходить из правила:

Поведение оператора — консистентно, поведение всей транзакции — нет.

Соответственно:

• нельзя полагаться на то, что повторный SELECT даст одинаковый результат;
• нужно либо фиксировать данные на уровне логики (например, делать один запрос вместо двух), либо использовать блокировки/дизайн транзакции.

Repeatable Read: «один снимок на всю транзакцию» — но не магия

Repeatable Read обеспечивает: в рамках одной транзакции все statement видят один и тот же снимок данных (snapshot), сделанный на момент старта транзакции.

Это уже значительно меняет картину: non-repeatable read и классические phantom read внутри одного транзакционного снимка устраняются на уровне «видимости версий».

Не всё, что кажется «фантомом», устранено полностью

Важно различать:

• фантомы из-за новых строк, появившихся после старта;
• фантомы из-за взаимодействия нескольких условий и обновлений (write skew).

Поскольку на Repeatable Read снимок один на транзакцию, простые сценарии «повторил SELECT — набор другой» обычно исчезают. Но появляется другая категория проблем — логическая несовместимость, когда транзакции проверяют условия на одном снимке и меняют данные так, что итоговая система попадает в недопустимое состояние.

Write skew: пример, который ломает «проверили — значит можно»

Предположим, есть два врача/лаборатории A и B, и правило бизнеса такое: в сумме должно оставаться не меньше 1 доступного ресурса. На уровне базы нет жёсткого ограничения, которое гарантирует это выражение напрямую.

Схема условная:

• resources(id, available boolean)
• Транзакции выбирают свою запись, проверяют условие «в другом месте всё ещё занято/доступно», и затем освобождают/занимают.

Сценарий:

• Условие: available должно удовлетворять A + B >= 1 (упрощённо).
• Транзакция T1 делает UPDATE resources SET available=false WHERE id=1 после проверки, что resources.id=2 доступно.
• Транзакция T2 параллельно делает то же для resources.id=2.

На Repeatable Read обе транзакции видят одинаковый снимок: в начале обе записи доступны (available=true). Каждая на основе снимка считает проверку истинной и делает свой UPDATE. В результате обе записи становятся false, и правило нарушается.

Это write skew — классическая аномалия, которую Repeatable Read не предотвращает.

Почему так происходит

Потому что транзакции:

1. читают данные на одном снимке;
2. принимают решение, исходя из прочитанного;
3. пишут так, что ограничение для системы в целом больше не выполняется.

В терминах строгой теории это не «невозможная» последовательность, которую модель может отсечь на Repeatable Read. PostgreSQL гарантирует консистентность чтения, но не «невозможность» таких логических противоречий.

Практический вывод для Repeatable Read

Repeatable Read полезен, когда:

• вы делаете несколько чтений и хотите стабильность видимости;
• вы собираете отчёт/список в рамках одной транзакции.

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

Serializable: защита от невозможных историй — но с учётом отказов

Serializable — это попытка обеспечить эффект, эквивалентный строгой последовательной обработке транзакций. В PostgreSQL это достигается не простым блокированием «всего всего», а с помощью механизма, который отслеживает конфликты и может прервать транзакцию с ошибкой serialization failure.

Что это означает на практике

• СУБД будет стараться предотвратить аномалии вроде write skew и других «неэквивалентных» историй.
• Если конфликт логики не удаётся разрешить автоматически, транзакция будет отменена, и вам нужно повторить её.

Типичная ошибка: ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001).

Минимальный пример перезапуска

В приложении обычно делают цикл с повтором для serializable:

import time
import psycopg

def run_serializable(conn, work):
    for attempt in range(1, 6):
        try:
            with conn.transaction(isolation_level="serializable"):
                return work()
        except psycopg.errors.SerializationFailure:
            # небольшая пауза снижает шанс бесконечного конфликта
            time.sleep(0.05 * attempt)
    raise RuntimeError("Serializable транзакция не завершилась после повторов")

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

Serializable — не замена правильному дизайну

Если у вас бизнес-операция требует строгого ограничения, лучше выражать его максимально явно:

• unique constraints,
• foreign keys,
• check constraints,
• constraints на комбинации полей.

Но когда ограничение сложное и относится к нескольким строкам/условиям, Serializable — один из способов гарантировать корректность.

Цена

Чаще всего Serializable:

• увеличивает накладные расходы на отслеживание зависимостей;
• увеличивает вероятность serialization failure под нагрузкой;
• требует обработки ретраев на уровне приложения.

Поэтому в реальных системах Serializable применяют выборочно:

• там, где ошибка бизнес-логики недопустима;
• где есть конфликтующая конкуренция;
• где корректность важнее пропускной способности.

Как понять, какой уровень выбрать: матрица «что вы делаете» и «какие риски допустимы»

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

Когда достаточно Read Committed

• Операции, где вы делаете один запрос для чтения и сразу на его основе пишете, без «повторного чтения ради контроля».
• Сценарии с жёсткими ограничениями в БД (unique, not null, FK), которые защищают от неконсистентного результата.
• Пайплайны, где итоговый эффект допускает «eventual correctness» (например, переагрегации по расписанию).

Когда нужен Repeatable Read

• Отчёты и выгрузки, где важно, чтобы набор данных не менялся между несколькими запросами.
• Сложные расчёты в одной транзакции, где повторяемость чтения критична.
• Ситуации, где вы понимаете возможность write skew и либо исключаете его другими средствами (блокировки, constraints), либо принимаете риск.

Когда стоит рассматривать Serializable

• Операции «проверь условие и сделай действие» на основе пересекающихся данных, где write skew может нарушить инвариант.
• Очереди/распределение ресурсов при высокой конкуренции, где важно гарантировать «ровно один выигравший» с корректным распределением.
• Критичные финансовые/балансовые операции, особенно при отсутствии прямых constraints.

Ловушки при конкурентной нагрузке: типовые анти-паттерны

Ниже — то, что чаще всего ломается именно в production.

Ловушка 1: «Проверили, потом записали» в несколько statement под Read Committed

BEGIN;

SELECT COUNT(*)
FROM orders
WHERE user_id = 42 AND status = 'active';

-- ожидаем, что результат в момент UPDATE тот же
UPDATE orders
SET status = 'active'
WHERE id = 123;

COMMIT;

Между SELECT и UPDATE другая транзакция может изменить данные, и условие потеряет смысл.

Что делать:

• свести проверку и действие к одной атомарной операции, если возможно;
• использовать SELECT ... FOR UPDATE (или FOR SHARE) по ключевым строкам;
• либо повышать уровень изоляции.

Ловушка 2: отсутствие явных уникальных ограничений

Иногда логика «гарантируем уникальность программно» не выдерживает гонок. Даже при Serializable можно получить проблемные эффекты, если запись не защищена constraint-ом (или если вы делаете несколько таблиц/шагов без единого инварианта).

Обязательные вещи:

• уникальность — через UNIQUE index;
• корректные ссылки — через FK;
• базовые инварианты — через CHECK.

Ловушка 3: блокировки «как попало»

Если вы добавляете FOR UPDATE, но:

• выбираете строки в разном порядке в разных местах,
• держите транзакции дольше,
• блокируете лишнее,

— вы можете устроить deadlock или резко просадить throughput.

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

Ловушка 4: SERIALIZABLE без ретраев и идемпотентности

На Serializable «иногда транзакция падает» — это нормальная часть защиты. Если приложение не повторяет, вы получаете ошибки пользователям и/или ломаете целостность.

Безопасные паттерны чтения и записи в конкурентной среде

Рассмотрим несколько практичных схем.

Паттерн A: Атомарная запись через UPSERT и constraint

Если бизнес-операция по смыслу «создать или обновить» по ключу, используйте INSERT ... ON CONFLICT.

INSERT INTO balances(user_id, amount)
VALUES (42, 100)
ON CONFLICT (user_id)
DO UPDATE
SET amount = balances.amount + EXCLUDED.amount;

Это переносит конкуренцию на уровень индекса и делает поведение детерминированным. Важный эффект: вы избегаете «SELECT then UPDATE».

Паттерн B: Валидация через блокировку строк

Если есть смысл «один клиент на один ресурс» (например, распределить free-лейбл), используйте блокировку выборки:

BEGIN;

-- выбираем одну доступную строку и блокируем её
WITH cte AS (
  SELECT id
  FROM inventory
  WHERE status = 'free'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE inventory i
SET status = 'reserved'
FROM cte
WHERE i.id = cte.id
RETURNING i.id;

COMMIT;

FOR UPDATE SKIP LOCKED помогает строить очередь воркеров: один воркер не ждёт заблокированную строку, а берёт другую. Этот подход часто лучше, чем «постоянно сканировать и пытаться».

Паттерн C: Проверка инварианта через constraints вместо транзакционной магии

Если инвариант формализуется в виде SQL constraint, используйте его. Пример — ограничение суммы в реляционной форме бывает сложно, но часто ограничение сводится к:

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

Когда инвариант выразим, СУБД сделает «правильность» без гонок.

Паттерн D: Serializable для критичных операций + повтор

Когда ваш инвариант зависит от нескольких строк, а write skew — реальная угроза, используйте Serializable точечно, с ретраями.

Пример на SQL уровне (без приложения)

В SQL вы можете вручную повторять, но обычно это делает код.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- критичная бизнес-операция:
-- 1) чтение
-- 2) проверка инварианта
-- 3) обновление

COMMIT;

Если получите serialization failure, транзакцию нужно откатить и запустить снова.

Практический чек-лист: как читать и писать безопасно

Ниже — компактный список, который стоит держать перед глазами при проектировании.

Чтение

• Не предполагайте, что повторный SELECT в одной транзакции даст одинаковые результаты на Read Committed.
• Для консистентных отчётов/выгрузок с несколькими запросами рассмотрите Repeatable Read (но анализируйте возможный write skew при последующих update’ах).
• В критичных местах старайтесь делать «чтение → действие» так, чтобы оно было атомарным (через UPSERT/constraints) или защищённым блокировками.

Запись

• Защитите инварианты, которые можно выразить в БД, constraints/unique indexes.
• Если вы делаете «проверили — решили — записали», подумайте: может ли параллельный процесс изменить данные между шагами?
  • Если да — переходите к атомарной операции, FOR UPDATE, или Serializable.
• Сокращайте время жизни транзакции и объём заблокированных данных.
• Всегда фиксируйте порядок блокировки строк (особенно если вы блокируете несколько таблиц/ключей).

Уровень изоляции

• Начните с