Serializability: invariants across concurrent transactions

Serializable isolation constrains every successfully committed transaction in the participating set to have the same effect as some serial execution. It does not mean that transactions run one at a time, and it does not make an application correct unless each transaction preserves the invariant when run alone.

Snapshot isolation can admit write skew when transactions read overlapping predicates and write disjoint rows. PostgreSQL Serializable Snapshot Isolation tracks read/write dependencies and may abort a transaction; applications must retry the whole transaction and must not publish external side effects before commit.

Модель и предпосылки

• Every transaction that can participate in an invariant uses the same compatible coordination boundary; a serializable transaction cannot protect concurrent weaker transactions or another independent database.
• A serialization failure or deadlock makes the current attempt unusable. Retry begins from a new transaction and uses stable operation identity for external effects.
• Explicit locks work only when every competing path locks the same guard row or range in a consistent order.

Проверяемые утверждения

• C1. PostgreSQL Repeatable Read is snapshot isolation: its stable snapshot prevents PostgreSQL phantoms but can still admit serialization anomalies such as write skew.
• C2. PostgreSQL Serializable adds SSI dependency monitoring; predicate SIRead locks identify dependencies and do not themselves block writers.
• C3. SQLSTATE 40001 and deadlock rollback require retrying the whole transaction; ordinary external effects need idempotency or an outbox.
• C4. SERIALIZABLE READ ONLY DEFERRABLE can wait for a safe snapshot and then avoid serialization failure risk for its reads.
• C5. A local serializable boundary does not provide global serializability across weaker participants or independent databases.

Исполняемые сценарии

Write skew under snapshot isolation. Two transactions can read the same valid snapshot, update disjoint rows, and jointly violate an invariant.

SSI abort and whole-transaction retry. Dependency monitoring prevents the bad committed set by aborting an attempt; the application restarts all of its logic.

Explicit common guard lock. A shared guard row serializes competing paths only when every path follows the same locking protocol.

Mixed isolation hole. One serializable participant cannot protect an invariant from a concurrent weaker or external participant.

Ошибки проектирования

• Не назначайте заранее «проигравшую» транзакцию: конкретный victim и момент abort зависят от обнаруженной зависимости и реализации.
• Не считайте SELECT FOR UPDATE универсальным лекарством от predicate/write-skew: все пути должны блокировать общий объект, которого может не существовать без отдельной guard row.
• Не повторяйте только последний SQL statement после 40001 или deadlock; snapshot и все решения попытки уже недействительны.
• Не выпускайте письмо, платёж или сообщение до commit без transactional outbox или другого идемпотентного протокола.

Границы гарантии

• Serializability — это порядок committed transactions, а не real-time order; strict serializability добавляет real-time constraint.
• Уникальные ограничения, advisory locks и distributed commit решают отдельные задачи и не возникают автоматически из isolation level.
• Производительность и abort rate измеряются на конкретном workload; универсального множителя latency нет.

Связанные материалы

Официальные и первичные источники

1. PostgreSQL 18: Transaction Isolation
2. PostgreSQL 18: Explicit Locking
3. MySQL 8.4: How to Minimize and Handle Deadlocks
4. CockroachDB: Developer Basics and Transaction Retries