Snapshot rules and safe version reads

Start with the guided lesson. These sections develop the mechanisms and tradeoffs beyond that first example.

Learning outcomes

Determine version visibility from a coherent transaction snapshot.
Construct a write-skew history and explain why snapshot isolation differs from serializability.
Explain version retention and reclamation costs caused by old readers.

Versions and snapshots

MVCC retains multiple versions so a reader can choose the version visible to its snapshot. A version commonly carries creator or commit metadata and a link or boundary for older versions. Exact representation differs among engines.

A snapshot defines which transactions or timestamps are visible. A long-running reader can continue seeing a stable historical state while writers create newer versions. This can reduce reader/writer blocking, but the engine must retain old versions and handle conflicts among writers.

key 17:
    value C, created by transaction 300
       -> value B, created by transaction 220
          -> value A, created by transaction 100

The diagram is conceptual. Transaction identifiers are not automatically commit timestamps, and numeric order alone may not determine visibility. A real visibility rule can depend on transaction status, snapshot membership, commit order, and the engine’s timestamp model.

A visibility decision

For a simplified committed-timestamp model, a snapshot at timestamp S selects the newest committed version whose commit timestamp is at or before S, excluding versions invalidated under that model. An uncommitted version is not visible merely because it has a lower provisional timestamp.

read(key, snapshot):
    for version from newest to oldest:
        if version is committed and version.commit_time <= snapshot.time:
            return value_or_deleted(version)
    return NOT_FOUND

This pseudocode is intentionally limited. It does not implement a production engine’s in-progress transaction handling, read-your-own-writes behavior, concurrent chain access, or garbage collection. It isolates the rule that visibility depends on committed state and snapshot boundaries, not simply physical presence.

A deletion is a visible state transition too. A reader before deletion may still need the old value. A current reader may need to see absence rather than continue past a visible tombstone to an older value. Treating a deletion marker as just another skipped version would resurrect deleted data.

Isolation levels over versions

A statement-level snapshot can let successive statements in one transaction see different committed states. A transaction-level snapshot can keep ordinary reads stable throughout the transaction. Neither choice by itself establishes all serializable relationships.

Snapshot isolation typically prevents two transactions from both committing incompatible writes to the same protected item under its conflict rule. It can still permit anomalies involving reads of relationships and disjoint writes, as in the on-call example. Serializable execution requires a result equivalent to some sequential transaction order.

PostgreSQL documents its specific isolation levels, including differences between repeatable read and serializable behavior. Those documented guarantees should be used directly. The general examples in this lesson explain why similarly named snapshots or MVCC implementations must not be assumed identical across products.

A timestamp model with explicit visibility intervals

For a more complete teaching model, represent each committed version with an interval [begin, end). A transaction reading at timestamp S selects a version with begin <= S < end. An initial version might cover [10, 20), an updated version [20, 35), and a deletion marker [35, infinity). A read at 19 sees the first value, at 20 sees the second, and at 35 sees absence. Half-open intervals remove ambiguity at exact boundaries.

Real implementations need not store both endpoints directly. An end can be inferred from another version, and transaction-status metadata can represent an unresolved creator or deleter. The interval model explains semantics without prescribing the on-disk format. It also separates a transaction’s start timestamp from its eventual commit timestamp: using one value for both can violate the intended ordering.

Read-your-own-writes needs an additional rule. If a transaction inserts an item and reads it again before commit, it normally sees its tentative version even though other transactions do not. Its private writes may shadow the snapshot. An update followed by a delete in the same transaction may collapse into no externally visible insertion, while still generating internal log and allocation work. Visibility is partly relative to the reader’s identity.

read_for_transaction(key, tx):
    if key in tx.private_changes:
        return tx.private_changes[key].value_or_absent
    version = newest_committed_version_visible_at(key, tx.snapshot)
    if version is absent or version.is_tombstone:
        return NOT_FOUND
    return version.value

This model assumes the private map and version traversal are physically safe and that the snapshot is valid. It does not resolve concurrent writes. At commit, another protocol must validate conflicts and publish all changes consistently. Treating the read rule as a complete transaction implementation would leave the difficult race at publication undefined.

Construct a snapshot without a race

Suppose transaction identifiers increase when work begins. T100 starts, then T101 starts and commits, while T100 remains active. A new snapshot cannot simply include all identifiers below 102: that would expose T100’s uncommitted changes. A snapshot can instead capture an upper boundary plus the set of transactions still in progress at capture, together with the engine’s visibility rules.

Capturing the set and boundary needs synchronization with transaction admission and commit. If a transaction disappears from the active set before its commit status becomes safely observable, a reader can misclassify it. The implementation may use locks, atomic state transitions, timestamp assignment protocols, or validated reads of metadata. The requirement is a coherent cut through transaction state, not one particular data structure.

A commit-timestamp engine has an analogous issue: allocating a timestamp before publishing data can create a hole. A later reader at a higher timestamp must not assume every lower timestamp is fully applied unless a safe watermark or wait rule establishes that property. Applied progress and assigned timestamps are different concepts. The same distinction reappears in replication and realtime delivery.

A long-running snapshot needs a registered lifetime. Starting to read first and registering retention later creates a gap in which garbage collection can reclaim required versions. Register-before-access protocols, epochs, or coordinated snapshot creation avoid that race. Ending the snapshot similarly requires releasing every dependent reference before advancing reclamation eligibility.

Snapshot freshness and UX

A stable historical view is useful for a report, but a client deciding whether to buy an item needs a fresh authoritative transition. Displaying cached owner Alice while Bob has already acquired the item is a stale observation; granting the purchase based on that observation would be a correctness error. The command should revalidate availability within its transaction.

A UI can show an optimistic pending purchase before confirmation, then reconcile against the committed result. This improves responsiveness without claiming the local prediction is authoritative. MVCC determines permitted server observations; presentation logic determines how uncertainty is communicated to the user. Keeping those responsibilities distinct avoids treating every stale display as a broken transaction or every smooth animation as proof of consistency.

Design conclusion

MVCC separates physical mutation from reader visibility. It can provide stable reads and reduce some blocking while introducing version storage, retention, and validation work. The promised isolation level determines which anomalies remain possible.

Lesson 22 compares alternative ways to order conflicting work, including optimistic validation, deterministic scheduling, and serialized execution. The right choice depends on how often operations actually conflict and how expensive wasted or coordinated work becomes.