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.

Границы и компоненты

Сценарии

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]wal-write-ahead-log

[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.