TRACE · INSPECT · CHALLENGE
MVCC: a snapshot remembers active writers
Compare two SELECT statements against the same committed and uncommitted row versions.
Your experiment
The default writer is active when the first SELECT runs at 5, then commits at 8. Repeatable Read still returns 100 at the second SELECT at 10. Switch to Read Committed: the second SELECT returns 200. Abort the writer: both keep 100. Select deletion: a committed, visible delete returns no row. Move commit before 5: both reads see the committed change.
Play inspects 14 modeled ticks over 60 seconds at 1×. This is teaching time, not database runtime. Pause, scrub or step to the next event. Restart keeps your inputs; changing a parameter recalculates without autoplay.
What the model means
The snapshot stores an upper transaction-ID bound and the active transaction set, not just one current ID. For this read-only fixture, an inserting transaction must be committed, below snapshot xmax, and absent from xip. A visible deleting transaction hides the old version. The first SELECT takes its snapshot at 5; Repeatable Read retains it, while Read Committed refreshes at 10. Writer resolution happens before a read at the same tick.
State at this event
Exact event and state inspector
Scope and references
A simplified PostgreSQL-style visibility fixture with writer 101 and a previously committed version from transaction 100. No current-transaction writes, subtransactions, command IDs, wraparound, locks, vacuum, MultiXact or serializable conflict detection. This is not a complete PostgreSQL implementation or a claim that Repeatable Read prevents every anomaly.
PostgreSQL: transaction isolation · Official AICassindra articles
Inspect versions, not one mutable value
The fixture starts with value one hundred inserted by committed transaction 100. Writer 101 begins at tick two and either stages an updated version or marks the old version for deletion. Its work is not visible merely because the tuple exists in storage. The reader issues SELECT at ticks five and ten. The table distinguishes stored versions from the versions visible to the selected snapshot. Inspect the snapshot transaction-ID boundary and active-writer list together with the writer status; checking only whether a transaction has eventually committed would reveal changes that were not visible when an earlier snapshot was taken.
Set the timing and the operation
Reader isolation selects Repeatable Read or Read Committed. Writer commits or aborts at tick controls resolution from tick three through twelve; resolution happens before a SELECT at the same tick in this model. Abort writer 101 changes that resolution to an abort rather than a commit. Delete instead of update makes the visible committed change remove the row instead of exposing a replacement tuple. Updated value controls the new tuple only in update mode and is inactive for deletion. Changing a control reconstructs the fixed timeline and pauses at its start; it does not mutate a running database transaction.
Cross the two snapshot boundaries
Set writer resolution to eight and updated value to two hundred. Repeatable Read returns one hundred at both reads because transaction 101 was active in the first snapshot. Read Committed takes another snapshot at ten and returns two hundred on the second read. Set resolution to five: this model resolves the writer first, so both reads include the committed update. Set resolution to ten: only the refreshed Read Committed snapshot includes it. Set resolution to twelve and neither read sees the update. Enable abort and the original value remains visible. With deletion enabled and a commit between the reads, Read Committed returns no row on its second read while Repeatable Read retains the old version.
Inspect reproducibly and respect the scope
Play maps fourteen modeled ticks to sixty inspection seconds. Pause holds the current event, Next event skips to the next scheduled change, Restart rewinds, Speed adjusts viewing pace and Inspection scrubs the sequence. The first read captures the retained snapshot; before it runs, there is no snapshot to inspect. This read-only exercise excludes the reader’s own writes, statement command identifiers, subtransactions, locks, vacuum, transaction-ID wraparound and serializable anomaly detection. It does not claim that every database implements the same Repeatable Read semantics or that snapshot isolation prevents all consistency anomalies. The exact inspector is the source for tuple fields and read results; the playback clock is teaching time, not query performance.