Аномалии изоляции и SSI в PostgreSQL: write skew, SIReadLock и ошибка 40001

Две транзакции на REPEATABLE READ проверяют правило «на дежурстве хотя бы один врач», меняют разных врачей и обе фиксируются, хотя вместе правило нарушили: снимок не видит конфликта, строки разные. SERIALIZABLE добавляет к снимку предикатные блокировки SIReadLock и поиск цикла зависимостей чтение-запись. Уровни описаны в статье про изоляцию, здесь разобран механизм.

DBA / 01koddo

Write skew: цикл из двух rw-зависимостей

Опыт 1: lost update без блокировок

Подними PostgreSQL и открой две сессии psql: вторую команду docker exec запусти в другом окне терминала. Первое окно — сессия A, второе — сессия B.

docker run -d --name dba-isolation-anomalies-and-ssi -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-isolation-anomalies-and-ssi psql -U postgres

SELECT version();
CREATE TABLE acc (id int PRIMARY KEY, balance int);
INSERT INTO acc VALUES (1, 100);

                                                       version
--------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

Приложение читает баланс, считает новое значение и пишет его. Обе сессии начинают транзакцию на READ COMMITTED, обе читают 100, A пишет 70, B пишет 50:

BEGIN ISOLATION LEVEL READ COMMITTED;     -- A и B
SELECT balance FROM acc WHERE id = 1;     -- A и B: 100
UPDATE acc SET balance = 70 WHERE id = 1; -- A
UPDATE acc SET balance = 50 WHERE id = 1; -- B: ждёт, строку держит A
COMMIT;                                   -- A
COMMIT;                                   -- B
SELECT balance FROM acc;                  -- любая сессия

UPDATE 1
COMMIT
 balance
---------
      50
(1 row)

Первые две строки — ответ B после коммита A. Итог 50: списание A (100 → 70) потеряно, хотя B прочитал 100 и об этом не знал. Это lost update. Повтори всё на REPEATABLE READ: после COMMIT в A сессия B получает ошибку, откатывается, а в таблице остаётся 70.

ERROR:  could not serialize access due to concurrent update
ROLLBACK
 balance
---------
      70
(1 row)

Lost update закрывает уже REPEATABLE READ: строка, изменённая после снимка, отправляет вторую транзакцию на повтор. Следующая аномалия проходит и этот уровень.

Опыт 2: write skew на REPEATABLE READ

Дежурные врачи: правило «хотя бы один on_call» проверяется чтением, а не ограничением.

CREATE TABLE doctors (name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

В обеих сессиях подряд: BEGIN ISOLATION LEVEL REPEATABLE READ; и SELECT count(*) FROM doctors WHERE on_call; — обе получают 2. Затем A выключает Алису, B выключает Боба, после чего обе фиксируются:

UPDATE doctors SET on_call = false WHERE name = 'alice';  -- A
UPDATE doctors SET on_call = false WHERE name = 'bob';    -- B
COMMIT;                                                   -- A
COMMIT;                                                   -- B
SELECT * FROM doctors ORDER BY name;

UPDATE 1
UPDATE 1
COMMIT
COMMIT
 name  | on_call
-------+---------
 alice | f
 bob   | f
(2 rows)

Обе UPDATE прошли без ожидания: строки разные, и проверка «изменена после снимка» не сработала. Ни одной дежурной нет. Это write skew: решение принято по чтению, которое чужая запись уже сделала неверным.

Опыт 3: тот же сценарий на SERIALIZABLE

Верни данные (UPDATE doctors SET on_call = true;) и повтори с BEGIN ISOLATION LEVEL SERIALIZABLE;. Сначала в каждой сессии узнай SELECT pg_backend_pid();: в опыте это 317 для A и 319 для B. После обоих UPDATE и COMMIT в A открой третью сессию и посмотри предикатные блокировки. Ответы сессий до этого момента:

UPDATE 1
UPDATE 1
COMMIT

Предикатные блокировки:

SELECT pid, locktype, relation::regclass AS rel, mode, granted
FROM pg_locks WHERE mode = 'SIReadLock' ORDER BY pid, locktype, 3;

 pid | locktype |     rel      |    mode    | granted
-----+----------+--------------+------------+---------
 317 | page     | doctors_pkey | SIReadLock | t
 317 | relation | doctors      | SIReadLock | t
 319 | page     | doctors_pkey | SIReadLock | t
 319 | relation | doctors      | SIReadLock | t
(4 rows)

У каждой сессии по два SIReadLock. count(*) в обеих сессиях прочитал таблицу через Seq Scan, поэтому блокировка висит на всей таблице (relation). UPDATE ... WHERE name = 'bob' нашёл строку через Index Scan using doctors_pkey, поэтому блокировка есть и на странице индекса (page). Планы взяты через EXPLAIN:

 Aggregate  (cost=24.96..24.97 rows=1 width=8)
   ->  Seq Scan on doctors  (cost=0.00..23.30 rows=665 width=0)
         Filter: on_call

 Update on doctors  (cost=0.15..8.17 rows=0 width=0)
   ->  Index Scan using doctors_pkey on doctors  (cost=0.15..8.17 rows=1 width=7)
         Index Cond: (name = 'bob'::text)

Блокировки A остались в pg_locks, хотя A уже выполнила COMMIT: они живут, пока не закончатся все пересекающиеся транзакции. Теперь COMMIT в B:

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.

После этого SELECT count(*) FROM pg_locks WHERE mode = 'SIReadLock'; возвращает 0: когда завершилась последняя пересекающаяся транзакция, сервер освободил и блокировки A. Повтор B (BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM doctors WHERE on_call;) видит результат A:

 count
-------
     1
(1 row)

Ни одна транзакция не ждала другую: на SERIALIZABLE обе UPDATE вернули UPDATE 1 сразу, а COMMIT в A прошёл. Отмена пришла на COMMIT, и обязанность повторить транзакцию лежит на приложении. Повтор должен выполнить весь блок заново, включая чтение: с count = 1 проверка правила не даст отключить последнего врача.

Опыт 4: read-only anomaly

Третья аномалия: читатель, который ничего не пишет, видит состояние, невозможное при последовательном выполнении. Таблицы: control(batch) с номером открытой пачки и receipt(batch, amount). Транзакция T1 принимает платёж в пачку, которую считает текущей, T2 закрывает пачку, T3 читает отчёт по закрытой пачке.

CREATE TABLE control (batch int);  INSERT INTO control VALUES (1);
CREATE TABLE receipt (batch int, amount int);

Порядок шагов на REPEATABLE READ:

-- T1: BEGIN ISOLATION LEVEL REPEATABLE READ;  SELECT batch FROM control;       -- 1
-- T2: BEGIN ...;  UPDATE control SET batch = batch + 1;  COMMIT;
-- T3: BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
--     SELECT batch FROM control;                                              -- 2
--     SELECT coalesce(sum(amount), 0) AS total FROM receipt WHERE batch = 1;  -- 0
--     COMMIT;
-- T1: INSERT INTO receipt VALUES (1, 100);  COMMIT;
-- после всех: SELECT sum(amount) FROM receipt WHERE batch = 1;             -- 100

 batch
-------
     2
 total
-------
     0
 sum
-----
 100

Последний запрос — новая транзакция после всего. T3 отчитался, что пачка 1 закрыта на 0, а потом в ней появились 100. Последовательного порядка у такого результата нет: T3 видит закрытие пачки, значит T2 раньше T3; T3 не видит платёж, значит T3 раньше T1; T1 не видит закрытие, значит T1 раньше T2. Круг замкнулся. На SERIALIZABLE те же шаги дают отказ на INSERT в T1, не в читателе T3:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.

После этого SELECT sum(amount) FROM receipt WHERE batch = 1; возвращает пустое значение: платёж не вставлен, отчёт T3 остался верным.

Что говорит код predicate.c

Три цитаты из src/backend/storage/lmgr/predicate.c по тегу REL_18_6. Первая — почему SIReadLock ничего не ждёт (L46-L48):

 * (3)	They never block anything; they are more like flags than locks
 *		in that regard; although they refer to database objects and are
 *		used to identify rw-conflicts with normal write locks.

Это объясняет опыт 3: UPDATE в обеих сессиях не ждали, SSI только ставит метки и сверяет их с записями. Вторая — условие, при котором PostgreSQL отменяет транзакцию, в OnConflict_CheckForSerializationFailure (L4522-L4528):

 * A serialization failure can only occur if there is a dangerous structure
 * in the dependency graph:
 *
 *		Tin ------> Tpivot ------> Tout
 *			  rw			 rw
 *
 * Furthermore, Tout must commit first.

Дальний участник может быть самой фиксируемой транзакцией: в PreCommit_CheckForSerializationFailure об этом говорит условие farConflict->sxactOut == MySerializableXact (L4745), то есть допустим цикл из двух. В опыте 3 Tin и Tout — это A, Tpivot — B. Третья цитата объясняет, кто платит за ошибку (L4691-L4700):

 * We're checking for a dangerous structure as each conflict is recorded.
 * The only way we could have a problem at commit is if this is the "out"
 * side of a pivot, and neither the "in" side nor the pivot has yet
 * committed.
 *
 * If a dangerous structure is found, the pivot (the near conflict) is
 * marked for death, because rolling back another transaction might mean
 * that we fail without ever making progress.  This transaction is
 * committing writes, so letting it commit ensures progress.  If we
 * canceled the far conflict, it might immediately fail again on retry.

Отменяют опорную транзакцию (pivot), а та, что уже фиксируется, проходит: так повтор гарантированно продвигается. Поэтому в опыте 3 COMMIT в A прошёл, а B получила Canceled on identification as a pivot. Метку SXACT_FLAG_DOOMED B получила в момент COMMIT в A, а ошибку — на своей следующей команде: отсюда «during commit attempt» в тексте ошибки. Зависимость «читал, а другая записала» ловит и функция CheckForSerializableConflictOut (L4009-L4045): по её комментарию, табличный метод доступа передаёт ей xid транзакции, создавшей невидимую версию (или изменившей видимую), и при пересечении транзакций возникает conflict out.

Что из этого следует

• REPEATABLE READ не держит правило, проверяемое чтением нескольких строк. Lost update он закрыл (опыт 1), write skew пропустил (опыт 2). Проверку «хотя бы один» на нём не делай без явной блокировки или ограничения.
• Отмена приходит посреди транзакции: в опытах — на COMMIT и на INSERT, по коду — и на чтении (CheckForSerializableConflictOut). Результаты SELECT до успешного COMMIT не считай окончательными и не отправляй наружу письмо или запрос до коммита.
• Ошибка 40001 повторяется целиком. Повтор начинается с BEGIN и чтения: в опыте 3 он получил count = 1. Ограничь число повторов и добавь паузу со случайной добавкой.
• Seq Scan ставит блокировку на всю таблицу. count(*) оставил relation-блокировку на doctors, поэтому любая запись в эту таблицу параллельной транзакцией образует с ним rw-зависимость. Индексный доступ по узкому условию оставил блокировку на странице индекса (page).
• Считай отказы на стороне приложения. pg_stat_database_conflicts — это отмены запросов на репликах из-за восстановления; после отмены сериализации в опыте 3 все её счётчики confl_* остались нулями. В логе сервера отмена видна строкой ERROR: could not serialize access, а в драйвере — SQLSTATE 40001.

Чек-лист для SERIALIZABLE

1. Найди проверки вида «прочитал, решил, записал в другую строку»: именно их пропускает REPEATABLE READ.
2. Включай SERIALIZABLE явно в BEGIN и оборачивай транзакцию в повтор по SQLSTATE 40001 с ограничением числа попыток.
3. Не делай побочных эффектов (письмо, запрос в сторонний сервис) до успешного COMMIT.
4. Смотри pg_locks WHERE mode = 'SIReadLock': relation вместо page или tuple значит, что любая запись в таблицу параллельной транзакцией образует зависимость.
5. Убери контейнер: docker rm -f dba-isolation-anomalies-and-ssi.