MVCC Snapshots and Row Version Visibility
An MVCC snapshot is a transaction’s view of the database at a particular logical point in time. When a SQL statement reads a table, the engine uses that snapshot to decide which committed version of each row the statement is allowed to see.
MVCC stands for multi-version concurrency control. Instead of overwriting a row in place and making readers wait while that happens, the database keeps enough history to let readers use an older version while a writer creates a newer one.
The problem MVCC solves
Imagine one transaction updates an account balance while another transaction runs a report:
-- Transaction A
UPDATE accounts SET balance = balance - 10 WHERE id = 7;
-- Transaction B
SELECT balance FROM accounts WHERE id = 7;
Without a versioning scheme, the database has two uncomfortable choices:
- Let the report read the row while it is being changed, risking a half-finished or inconsistent value.
- Lock the row for the update, making the report wait—or lock it for the report, making the update wait.
Waiting is sometimes necessary for correctness, but making every ordinary read block every write leads to poor concurrency. MVCC avoids that particular conflict by allowing both operations to proceed: the report reads the last version that was valid for its snapshot, while the update produces a new version for later readers.
A snapshot is therefore not usually a physical copy of the whole database. It is metadata describing which transaction changes are visible at a point in time.
A row can have several versions
With MVCC, an update is commonly represented as a new row version rather than a destructive replacement of the old version. Conceptually, a row might look like this over time:
| Version | Value | Created by | Valid until |
|---|---|---|---|
| Old | 100 | Transaction 41 | Transaction 52’s update |
| New | 90 | Transaction 52 | Still current |
The exact storage layout varies. Some engines store version information alongside the row; others keep old values in undo records. The important property is the same: the engine can identify which version was current for a reader’s snapshot.
A transaction that has not committed does not normally make its new versions visible to other transactions. If it rolls back, those versions must be ignored. The database also needs to distinguish a row that existed before the snapshot from one inserted afterward, and a row that was deleted before the snapshot from one deleted afterward.
How a statement gets a snapshot
At the start of a statement—or, under some isolation modes, at the start of a transaction—the engine records a visibility boundary. That boundary includes information such as:
- Which transactions had committed.
- Which transactions were still running.
- The identity of the transaction taking the snapshot.
The isolation level controls when this boundary is taken. Under the common read committed behavior, each statement gets its own snapshot. Two SELECT statements in one transaction can therefore see different committed data if another transaction commits between them. Under repeatable read, the transaction generally keeps one snapshot, so its reads remain based on the same database view.
The implementation may express the boundary as transaction IDs, commit sequence numbers, or another internal structure. Those details differ, but the visibility question is consistent:
Was this particular row version committed early enough to belong to my snapshot, and is it hidden by a later version that also belongs to my snapshot?
The visibility decision
For each candidate row, the engine follows the chain or metadata for its versions and applies rules like these:
- Ignore changes from transactions that were still uncommitted when the snapshot was taken. Their inserts and updates are not visible.
- Ignore changes from transactions that had already rolled back. They never became part of the database view.
- Accept a version created by a transaction committed before the snapshot, unless a newer visible version supersedes it.
- Treat the transaction’s own appropriate writes as visible to itself, even though they may not have been committed when its snapshot was created.
- Respect deletes. If a visible transaction deleted the row before the snapshot, the row is absent from that snapshot; if the delete was still in progress or happened later, the older version may remain visible.
Suppose transaction 52 updates a row but is uncommitted when transaction 60 starts. Transaction 60 follows the row’s versions, sees that 52 is not yet committed, and reads the previous committed version. If 52 commits after 60’s snapshot is created, that does not retroactively change what 60 can see.
This is why an MVCC snapshot behaves like a consistent point in time even though the database is continuing to change underneath it.
Readers and writers proceed together
The writer can create its new version without replacing the old version that a reader needs. The reader can scan and filter rows using its snapshot without taking a lock that blocks that update. Once the writer commits, later snapshots can select the new version.
That does not mean MVCC eliminates all locking. Two transactions trying to update the same logical row may still wait or conflict, because silently accepting both updates could lose one of them. Schema changes, explicit FOR UPDATE reads, unique constraints, and some serialization mechanisms also use locks or conflict detection.
MVCC also does not make every transaction outcome safe. At weaker isolation levels, two transactions can each read related data and make decisions that are individually valid but jointly wrong. This is write skew: for example, two doctors each see that another doctor is on call and both remove themselves, leaving nobody on call. Stronger isolation levels add conflict detection or other protections for cases like this.
Why old versions need cleanup
A version that is no longer visible to any active snapshot is obsolete, but it cannot always be deleted immediately. A long-running transaction may still need it. Background maintenance—called vacuum, purge, or garbage collection depending on the engine—eventually removes such old versions and reclaims their space.
This explains familiar operational symptoms: long-running transactions can retain old row versions, increase storage and scan costs, and make cleanup fall behind. When an incident mentions MVCC bloat, undo growth, vacuum lag, or a transaction holding an old snapshot, it is describing the cost of preserving these historical versions.
The central idea remains simple: a snapshot gives each statement a visibility boundary, and row versions let the engine honor that boundary while other transactions continue to write.