Уровни изоляции: пять аномалий в двух окнах psql и пропавшие 50 000 сумов
Оба запроса закоммичены, ошибок нет, а часть денег исчезла. Воспроизводим каждую аномалию от dirty read до write skew в двух сессиях psql и правильно повторяем транзакцию при ошибке сериализации в Django.
Представьте кошелёк, на котором 100 000 сумов. В один и тот же момент приходят два запроса. Первый — webhook платёжного провайдера: на кошелёк зачислено 50 000. Второй — покупка пользователя: нужно списать 30 000. Оба обработчика читают баланс, оба видят 100 000. Один записывает 150 000, другой — 70 000. Что записано последним, то и останется. Если останется 70 000, значит 50 000 исчезли бесследно: ни ошибки, ни exception в логах, всё закоммичено.
Обычная реакция: «но ведь всё было внутри transaction.atomic()». Дело в том, что транзакция гарантирует атомарность — «всё или ничего». А то, насколько параллельные транзакции видят изменения друг друга, определяет уровень изоляции. В PostgreSQL по умолчанию это Read Committed, и от описанной ситуации он не защищает.
В этой статье мы вручную воспроизведём пять классических аномалий в двух окнах psql, посмотрим, что PostgreSQL делает на каждом уровне, и напишем для Django настройку изоляции и повтор транзакции при ошибке сериализации.
Подготовка стенда
Откройте два терминала и подключитесь в обоих через psql к одной базе. Назовём их сессия A и сессия B. Выполняйте команды строго в указанном порядке, по очереди. Сначала создадим таблицы:
CREATE TABLE accounts (
id int PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Ali', 100), (2, 'Vali', 100);
CREATE TABLE orders (
id serial PRIMARY KEY,
status text NOT NULL
);
INSERT INTO orders (status) VALUES ('new'), ('new'), ('paid');
CREATE TABLE doctors (
id int PRIMARY KEY,
name text NOT NULL,
shift_id int NOT NULL,
on_call boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Dilnoza', 1, true), (2, 'Jasur', 1, true);
Каждый пример продолжает с того состояния, которое оставил предыдущий, поэтому значения баланса меняются по цепочке.
Как устроена изоляция: snapshot и MVCC
PostgreSQL построен на MVCC (multi-version concurrency control). UPDATE не меняет строку на месте: он записывает новую версию строки, а старую помечает как удалённую такой-то транзакцией. В каждой версии хранятся ID создавшей (xmin) и удалившей (xmax) транзакции.
При выполнении запроса PostgreSQL берёт snapshot: какие транзакции на этот момент уже закоммичены, а какие ещё идут. Версия строки видна, только если создавшая её транзакция была закоммичена к моменту снимка. Главное различие уровней изоляции — когда берётся снимок:
- Read Committed: новый снимок в начале каждого statement.
- Repeatable Read: один снимок на всю транзакцию, берётся при первом запросе (а не при
BEGIN). - Serializable: снимок Repeatable Read плюс SSI (Serializable Snapshot Isolation) — механизм, отслеживающий опасные зависимости чтения/записи между транзакциями.
Чтение никогда не блокирует запись, запись не блокирует чтение. Но если две транзакции хотят изменить одну строку, вторая ждёт row lock первой. Что происходит после ожидания, тоже зависит от уровня — это мы увидим ниже.
1. Dirty read: в PostgreSQL его нет
Dirty read — чтение незакоммиченных изменений другой транзакции. Стандарт SQL разрешает это на уровне Read Uncommitted, но в PostgreSQL Read Uncommitted ведёт себя как Read Committed:
-- Session A
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- not committed yet
-- Session B
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 100.00: B does not see A's uncommitted change
COMMIT;
-- Session A
ROLLBACK;
В MVCC незакоммиченная версия просто не видна чужим снимкам, поэтому «включить» dirty read в PostgreSQL невозможно никаким уровнем.
2. Non-repeatable read: один запрос, два ответа
В Read Committed каждый statement берёт свой снимок. Значит, прочитав строку дважды в одной транзакции, вы увидите коммит, случившийся между чтениями:
-- Session A
BEGIN; -- READ COMMITTED (default)
SELECT balance FROM accounts WHERE id = 1;
-- 100.00
-- Session B (autocommit)
UPDATE accounts SET balance = 40 WHERE id = 1;
-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- 40.00: same query, same transaction, different answer
COMMIT;
Часто это безобидно, но если отчёт или валидация состоят из нескольких запросов, данные перестают сходиться: первый запрос видит одно состояние, второй — другое. В Repeatable Read вся транзакция работает с одним снимком:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- 40.00 (the snapshot is taken here, at the first query, not at BEGIN)
-- Session B (autocommit)
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- still 40.00
COMMIT;
SELECT balance FROM accounts WHERE id = 1;
-- 100.00: new transaction, new snapshot
Обратите внимание: снимок берётся при первом запросе, а не на BEGIN. Пауза после BEGIN снимок не «замораживает».
3. Phantom read: появляются новые строки
Фантом — это новые (или исчезнувшие) строки при повторном выполнении запроса с тем же условием. В Read Committed это тоже происходит:
-- Session A
BEGIN; -- READ COMMITTED
SELECT count(*) FROM orders WHERE status = 'new';
-- 2
-- Session B (autocommit)
INSERT INTO orders (status) VALUES ('new');
-- Session A
SELECT count(*) FROM orders WHERE status = 'new';
-- 3: a "phantom" row appeared
COMMIT;
Стандарт SQL допускает фантомы на Repeatable Read, но в PostgreSQL этот уровень основан на снимке и фантомов не показывает: повторите пример с BEGIN ISOLATION LEVEL REPEATABLE READ;, и второй count(*) тоже вернёт 2.
4. Lost update: проблема из начала статьи
Теперь самое опасное. Приложение читает баланс, считает новое значение в Python и записывает его (read-modify-write). В Read Committed:
-- Session A
BEGIN; -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1; -- 100.00, app computes 100 + 50
-- Session B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100.00, app computes 100 - 30
-- Session A
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;
-- Session B
UPDATE accounts SET balance = 70 WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1; -- 70.00: A's +50 is gone
UPDATE в сессии B не дал никакой ошибки — изменение A просто перезаписано. В Repeatable Read PostgreSQL это замечает: если строку, которую транзакция хочет изменить, после её снимка изменила и закоммитила другая транзакция, будет ошибка:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 70.00
-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 70.00
-- Session A
UPDATE accounts SET balance = 120 WHERE id = 1;
COMMIT;
-- Session B
UPDATE accounts SET balance = 40 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
SQLSTATE этой ошибки — 40001 (serialization_failure). Смысл: «выполните эту транзакцию заново». Если A ещё не закоммитила, UPDATE в B будет ждать: при коммите A — ошибка, при откате — B продолжит.
Чтобы избавиться от lost update, Repeatable Read не обязателен. В Read Committed UPDATE работает особым образом: если строка заблокирована, он ждёт, затем берёт последнюю закоммиченную версию строки, заново проверяет на ней условие WHERE и вычисляет выражение уже над этой версией. Поэтому если чтение и запись — один statement, всё корректно:
-- Safe even under READ COMMITTED: the read and the write are one statement
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- Withdrawal with a check, also one statement
UPDATE accounts SET balance = balance - 30
WHERE id = 1 AND balance >= 30;
-- "UPDATE 0" means insufficient funds
5. Write skew: Repeatable Read тоже бессилен
Write skew — самая коварная аномалия. Две транзакции читают одни и те же данные, на их основе принимают решение и меняют разные строки. Ни одна строка не записывается дважды, поэтому Repeatable Read конфликта не видит.
Правило: в смене должен оставаться хотя бы один дежурный врач. Дилноза и Жасур одновременно нажимают «уйти с дежурства»:
-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2, so I can leave
-- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2, so I can leave too
-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT; -- succeeds, no error
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 0
Каждая транзакция поступила абсолютно корректно в своём снимке, но правило нарушено: дежурных не осталось. В денежных системах то же самое случается с правилами вроде «суммарный баланс двух счетов не уходит в минус» или «у пользователя один активный промокод».
На уровне Serializable PostgreSQL эту зависимость ловит:
UPDATE doctors SET on_call = true; -- reset
-- Session A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2
-- Session B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE shift_id = 1 AND on_call; -- 2
-- Session A
UPDATE doctors SET on_call = false WHERE id = 1;
-- Session B
UPDATE doctors SET on_call = false WHERE id = 2;
-- Session A
COMMIT; -- succeeds
-- Session B
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
-- HINT: The transaction might succeed if retried.
Как работает SSI? Serializable-транзакция ставит на прочитанные данные predicate lock (SIRead lock). Он никого не блокирует — это просто отметка «я это читала». Если другая транзакция меняет эти данные, между ними фиксируется rw-зависимость. Две такие зависимости подряд (A → B → C, причём A и C могут быть одной транзакцией) считаются опасной структурой, и PostgreSQL отменяет одну из транзакций с 40001. Ошибка может возникнуть как на UPDATE, так и на COMMIT, поэтому код должен быть готов к обоим вариантам.
Сравнительная таблица
| Аномалия | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|
| Dirty read | нет | нет | нет |
| Non-repeatable read | есть | нет | нет |
| Phantom read | есть | нет (в PostgreSQL) | нет |
| Lost update (read-modify-write) | есть | нет, ошибка 40001 |
нет, ошибка 40001 |
| Write skew | есть | есть | нет, ошибка 40001 |
Более строгий уровень изоляции не «тихо исправляет» аномалию — он превращает её в ошибку. Поймать эту ошибку и повторить транзакцию — задача вашего кода.
В Django: сначала правильный запрос, потом изоляция
Модели для примеров:
# models.py
from django.db import models
class Account(models.Model):
owner = models.CharField(max_length=100)
balance = models.DecimalField(max_digits=12, decimal_places=2)
class Doctor(models.Model):
name = models.CharField(max_length=100)
shift_id = models.IntegerField()
on_call = models.BooleanField(default=True)
Классический lost update в Django:
# Wrong: read-modify-write in Python, a lost update under READ COMMITTED
def deposit(account_id, amount):
account = Account.objects.get(pk=account_id)
account.balance += amount
account.save(update_fields=["balance"])
Часто менять уровень вообще не нужно — достаточно объединить чтение и запись в один UPDATE или заранее заблокировать строку:
# services.py
from decimal import Decimal
from django.db import transaction
from django.db.models import F
from .models import Account
class InsufficientFunds(Exception):
pass
def deposit(account_id: int, amount: Decimal) -> None:
# UPDATE accounts SET balance = balance + %s WHERE id = %s
Account.objects.filter(pk=account_id).update(balance=F("balance") + amount)
def withdraw(account_id: int, amount: Decimal) -> None:
updated = Account.objects.filter(pk=account_id, balance__gte=amount).update(
balance=F("balance") - amount
)
if updated == 0:
raise InsufficientFunds(account_id)
def withdraw_with_fee(account_id: int, amount: Decimal, fee: Decimal) -> None:
# When the logic does not fit into one UPDATE: lock the row first
with transaction.atomic():
account = Account.objects.select_for_update().get(pk=account_id)
total = amount + fee
if account.balance < total:
raise InsufficientFunds(account_id)
account.balance -= total
account.save(update_fields=["balance"])
Выражение F() превращается в UPDATE ... SET balance = balance + ..., а select_for_update() — в SELECT ... FOR UPDATE. Во втором случае параллельная транзакция будет ждать на SELECT, пока первая не завершится.
Настройка уровня изоляции в Django
С PostgreSQL Django по умолчанию работает в Read Committed. Уровень для всего соединения меняется через OPTIONS:
# settings.py
import os
from django.db.backends.postgresql.psycopg_any import IsolationLevel
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "shop",
"USER": "shop",
"PASSWORD": os.environ["DB_PASSWORD"],
"HOST": "localhost",
"PORT": "5432",
"OPTIONS": {
# default is READ COMMITTED; change it only if you mean it
"isolation_level": IsolationLevel.REPEATABLE_READ,
},
}
}
Но перевести весь проект на Serializable — редко правильное решение: теперь 40001 может получить любая пишущая транзакция, и каждую придётся повторять. Обычно Serializable нужен для нескольких критичных операций. У atomic() в Django нет параметра уровня изоляции, поэтому отправим SET TRANSACTION ISOLATION LEVEL первым statement внутри транзакции и тут же напишем логику повтора:
# db.py
import functools
import random
import time
from django.db import OperationalError, connection, transaction
# 40001 = serialization_failure, 40P01 = deadlock_detected
RETRYABLE_SQLSTATES = {"40001", "40P01"}
def _sqlstate(exc: Exception) -> str | None:
cause = exc.__cause__ # the original psycopg exception
# psycopg 3 uses .sqlstate, psycopg2 uses .pgcode
return getattr(cause, "sqlstate", None) or getattr(cause, "pgcode", None)
def serializable(retries: int = 5, base_delay: float = 0.02):
"""Run the function in its own SERIALIZABLE transaction, retrying on 40001/40P01."""
def decorator(func):
@functools.wraps(func)
def wrapper(*args, **kwargs):
if connection.in_atomic_block:
raise RuntimeError(
f"{func.__name__}() must start the outermost transaction"
)
for attempt in range(retries + 1):
try:
with transaction.atomic():
with connection.cursor() as cursor:
# must be the first statement of the transaction
cursor.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
return func(*args, **kwargs)
except OperationalError as exc:
if _sqlstate(exc) not in RETRYABLE_SQLSTATES or attempt == retries:
raise
# exponential backoff with jitter
time.sleep(base_delay * (2**attempt) * random.uniform(0.5, 1.5))
return wrapper
return decorator
Несколько важных моментов:
- Повтор должен оборачивать всю транзакцию целиком. Поймать ошибку внутри транзакции и повторить только последний statement бессмысленно: PostgreSQL уже перевёл транзакцию в состояние aborted.
- Поэтому декоратор падает, если его вызвали внутри внешнего
atomic(). Вложенныйatomic()создаёт только savepoint, аSET TRANSACTIONтам не сработает. - Django оборачивает ошибку psycopg в
django.db.OperationalError, исходная остаётся в__cause__— SQLSTATE берём оттуда. - Пауза растёт с каждой попыткой и получает случайную добавку (jitter): иначе два столкнувшихся запроса снова повторятся одновременно и снова столкнутся.
Write skew в Django:
# services.py
from django.db import transaction
from .db import serializable
from .models import Doctor
class LastDoctorError(Exception):
pass
@serializable()
def go_off_call(doctor_id: int, shift_id: int) -> None:
on_call = Doctor.objects.filter(shift_id=shift_id, on_call=True).count()
if on_call < 2:
raise LastDoctorError("at least one doctor must stay on call")
Doctor.objects.filter(pk=doctor_id).update(on_call=False)
# Alternative without SERIALIZABLE: lock the rows the decision depends on
def go_off_call_locked(doctor_id: int, shift_id: int) -> None:
with transaction.atomic():
doctors = list(
Doctor.objects.select_for_update()
.filter(shift_id=shift_id, on_call=True)
.order_by("pk") # same lock order everywhere, fewer deadlocks
)
if len(doctors) < 2:
raise LastDoctorError("at least one doctor must stay on call")
Doctor.objects.filter(pk=doctor_id).update(on_call=False)
Если оба врача одновременно вызовут go_off_call(), один завершится успешно, второй получит 40001, повторит попытку, в новом снимке увидит одного дежурного и остановится с LastDoctorError. Второй вариант (select_for_update) здесь тоже работает, потому что решение зависит от конкретных существующих строк. Если же решение зависит от того, что строки нет (например, нет другой брони на этот интервал времени), FOR UPDATE не может заблокировать несуществующую строку — тут нужен Serializable, unique constraint или exclusion constraint.
Подводные камни в продакшене
Side effects внутри повтора. Функция выполнится заново. Если внутри она шлёт HTTP-запрос платёжному провайдеру или вызывает task.delay(), это случится дважды. Выносите внешние эффекты в transaction.on_commit(): колбэки отменённой попытки выбрасываются вместе с откатом и срабатывают только после успешного коммита.
ATOMIC_REQUESTS = True. Каждая view уже внутри транзакции, и декоратор выше не сработает. Помечайте такие view @transaction.non_atomic_requests или организуйте повтор вне view, в сервисном слое.
Тесты. TestCase в Django оборачивает каждый тест в транзакцию, поэтому serializable-код и конкурентные сценарии нужно проверять в TransactionTestCase, с отдельными потоками и отдельными соединениями.
Защищены только Serializable-транзакции. SSI отслеживает зависимости только между транзакциями уровня Serializable. Если в одну таблицу где-то пишут в Serializable, а где-то в Read Committed, гарантия пропадает.
Индексы и ложные срабатывания. Predicate lock может ставиться на прочитанные страницы индекса или на всю таблицу. Sequential scan блокирует таблицу целиком, и 40001 становится больше даже без реального конфликта. Проверяйте через EXPLAIN, что критичные запросы идут по индексу. Пишите количество повторов в метрики: так вы измеряете проблему, а не гадаете.
Длинные читающие транзакции. В Serializable может быть отменена даже транзакция, которая только читает. Для больших отчётов есть DEFERRABLE:
-- Long read-only report under SERIALIZABLE that is never cancelled
-- with a serialization error (it may wait a bit at start instead)
BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
SELECT owner, sum(balance) FROM accounts GROUP BY owner;
COMMIT;
Длинные транзакции вообще. В Repeatable Read и Serializable чем дольше открыта транзакция, тем выше шанс конфликта и тем дольше VACUUM не может убрать старые версии строк. Не ждите внешний API внутри транзакции.
Практический чеклист
- Найдите все read-modify-write:
obj.field += x; obj.save()— первый кандидат. - По возможности объединяйте чтение и запись в один
UPDATE(F(),filter(...).update(...), проверка числа обновлённых строк). - Для сложной логики блокируйте строку через
select_for_update()и соблюдайте одинаковый порядок блокировок. - Если решение зависит от отсутствия строки — constraint или Serializable.
- На Serializable/Repeatable Read повторяйте всю транзакцию при
40001и40P01, с backoff и jitter. - Выносите внешние side effects в
transaction.on_commit(). - Проверяйте конкурентные сценарии в
TransactionTestCaseс двумя соединениями; ручное воспроизведение в двух окнахpsql— самая быстрая проверка. - Мониторьте число повторов и частоту
40001.
Итог
Уровень изоляции отвечает на вопрос «насколько параллельные транзакции видят друг друга», и Read Committed по умолчанию в PostgreSQL допускает lost update и write skew. Большинство проблем решается одним правильно написанным UPDATE или select_for_update(). Когда решение зависит от нескольких строк или от отсутствия строки, Serializable — правильный инструмент, но только вместе с повтором. Любой новый критичный сценарий один раз «сломайте» в двух окнах psql: это занимает несколько минут и защищает от потери денег.
// комментарии
Загружаем комментарии...