System Design Cases
MVCC MultiVersion Concurrency Control concept page
MVCC (Multi-Version Concurrency Control) concept page. Каждая строка хранит несколько версий (xmin/xmax). Транзакции читают консистентный snapshot на момент start. Postgres, Oracle UNDO, SQL Server RCSI, MySQL InnoDB UNDO log, Spanner timestamp KV. Цена: bloat, VACUUM/cleanup overhead, txid wraparound risk при long-running txn. 4 сценария: stable snapshot read, write skew under SI, autovacuum cleanup + bloat от long-running txn, txid wraparound катастрофа. ADR: MVCC vs locking-based READ COMMITTED (Postgres vs SQL Server до RCSI).</description> <parameter name="name">MVCC: Multi-Version Concurrency Control
MVCC: snapshots, row versions, anomalies and vacuum horizons
MVCC хранит несколько logical versions и применяет visibility rules. Корректность определяется isolation level, snapshot boundary, conflict detection и lifecycle старых versions, а не лозунгом «readers never block writers».
Корректная модель
- Visibility comes from transaction status plus snapshot rules, not from wall-clock row timestamps.
- PostgreSQL READ COMMITTED normally uses a new snapshot per command; REPEATABLE READ and SERIALIZABLE keep a transaction view.
- MVCC still uses row/table/predicate conflicts and can abort or wait.
- Vacuum reclamation is bounded by active snapshots, replication and transaction-ID safety.
Границы и компоненты
| Компонент | Ответственность |
|---|---|
| Reader Transaction | Получает statement или transaction snapshot по isolation level. |
| Writer Transaction | Создаёт новую tuple version и может ждать locks/conflicts. |
| Snapshot and XID State | Определяет visible committed/in-progress transactions и horizon. |
| Tuple Versions | Содержит current и obsolete physical tuples до cleanup. |
| Row and Predicate Conflicts | Сериализует incompatible writes и поддерживает stronger isolation. |
| Vacuum and Freeze | Удаляет reusable dead versions и предотвращает XID wraparound. |
| Transaction Age Monitor | Находит long transactions, bloat и wraparound risk. |
Сценарии
READ COMMITTED statement snapshots
Two SELECT statements in one PostgreSQL READ COMMITTED transaction can see different committed states because each command obtains a new snapshot.
Проверяемый исход: The lesson no longer fixes READ COMMITTED visibility at transaction start.
Repeatable transaction snapshot
At REPEATABLE READ, subsequent reads use the transaction snapshot. A concurrent committed replacement remains invisible until this transaction ends.
Проверяемый исход: Repeatability is scoped to the transaction snapshot and does not imply serializability of all invariants.
Write skew and serializable retry
Two transactions read disjoint rows and each writes one row, violating a cross-row invariant under snapshot isolation. Serializable conflict detection aborts one execution.
Проверяемый исход: The application retries the complete side-effect-free transaction on serialization failure.
Long snapshot blocks reclamation
A long transaction keeps old versions potentially visible. Vacuum cannot reclaim them, so table/index bloat and XID age increase.
Проверяемый исход: Monitoring terminates or fixes the owner deliberately; vacuum is not blamed for violating an active snapshot.
Failure, concurrency и replay checklist
- Retry a complete serializable transaction, not only the last statement.
- Keep external side effects outside a retried closure or guard them with idempotency.
- Alert on oldest transaction/snapshot, dead tuples and autovacuum health.
- Do not run VACUUM FULL casually; it has different locking/rewrite behavior.
Формулы, units и допущения
- PostgreSQL xid is a 32-bit value with wraparound handling; operational thresholds are configuration/version concerns, not permission to wait 2^32 transactions.
- Version bloat rate ≈ obsolete tuple bytes/s − reclaimable cleanup bytes/s while the horizon is pinned.
- Snapshot age is time; transaction age in XIDs is work. Monitor both instead of converting one to the other with a fixed TPS assumption.
Числа выше — учебные inputs или размерностные формулы. Их нельзя выдавать за benchmark или SLA конкретного продукта.
Связанные темы
[CONCEPT]exactly-once-semantics
Первичные источники
- https://www.postgresql.org/docs/current/mvcc-intro.html
- https://www.postgresql.org/docs/current/transaction-iso.html
- https://www.postgresql.org/docs/current/routine-vacuuming.html
- https://www.postgresql.org/docs/current/transaction-id.html
Scope note
Диаграмма показывает причинные границы и recovery contracts, а не скрытую реализацию конкретного managed-сервиса. Любая stronger guarantee действует только в явно названной transaction/checkpoint/acknowledgement boundary.