MVCC Snapshots and SQL Row Visibility

22 Sep 2026 sqldatabasesconcurrency

MVCC, or multi-version concurrency control, lets a database keep several versions of a row and give each transaction a consistent view of those versions. An MVCC snapshot is the transaction’s record of which changes it is allowed to see.

That means a reader can continue using an older row version while a writer creates a newer one, instead of waiting for the writer to finish.

The problem: one row, competing work

Suppose a request reads an account balance while another request updates that balance. With a simple locking design, the writer might lock the row, forcing the reader to wait. Or the reader might lock it first, forcing the writer to wait. Locks are useful for coordinating changes, but making every read wait for every write can reduce concurrency and increase latency.

Without an appropriate isolation mechanism, avoiding those waits can create worse problems: a reader might see half of a change, or observe values that a transaction later rolls back. An isolation level is the database setting that defines how much one transaction may observe of other transactions’ work.

MVCC addresses this by separating two questions:

1. Which version should this transaction read?
2. Which transaction is allowed to replace the current value?

Reads can usually answer the first question from a snapshot. Writes still coordinate with other writes when they target the same data.

Rows become a history of versions

When an MVCC database updates a row, it commonly does not overwrite the old contents in place. It creates a new row version and records enough metadata to determine which transactions created or invalidated each version.

For example, an account might progress like this:

balance = 100  (created by transaction 10)
balance = 150  (created by transaction 12)
balance =  90  (created by transaction 15)

The old versions are not necessarily visible to every transaction. They remain available so a transaction that started earlier can still read the value it was entitled to see.

A deletion is handled similarly: the database records that a version was deleted, often by creating a deletion marker or by storing metadata on the old version. To a transaction that can see the deletion, the row is absent. To an older transaction, the old version may still appear.

The implementation details differ between database engines. PostgreSQL, for example, stores transaction metadata with row versions; other systems use undo records or a separate version store. The principle is the same: preserve enough history to answer visibility questions.

What an MVCC snapshot contains

At snapshot creation, the database establishes a boundary between changes the transaction may use and changes it must ignore. Conceptually, the snapshot knows:

• which transactions committed before the snapshot;
• which transactions were still running when the snapshot was taken; and
• the identity of the transaction taking the snapshot, so it can see its own writes.

A transaction normally sees changes committed before its snapshot. It does not see changes from transactions that were still running at that moment, because those transactions might later roll back or make further changes. It also does not see a transaction that commits after the snapshot was created.

The database applies this rule to each candidate row version. A version is visible only if its creating transaction is visible to the snapshot and its deletion or replacement is not. If the newest version fails that test, the engine follows the row’s version history until it finds an older visible version—or determines that no version is visible.

This is the central operation behind an MVCC read:

A query does not simply read “the row.” It chooses the version of that row that is visible under its snapshot.

When snapshots are taken

The exact timing depends on the isolation level and database engine. A common default, read committed, gives each SQL statement its own snapshot. Two SELECT statements in one transaction can therefore see different committed results if another transaction commits between them.

BEGIN;

SELECT balance FROM accounts WHERE id = 7;
-- Another transaction commits an update here.

SELECT balance FROM accounts WHERE id = 7;

COMMIT;

Under read committed, the second SELECT commonly gets a newer snapshot and may see the update.

A stronger level, often called repeatable read, normally gives the transaction one snapshot for its lifetime. Repeating the query then keeps the same logical view, even while other transactions commit newer versions. The database may reject a write if continuing would violate the isolation guarantee.

The names and edge cases vary, so the engine’s documentation matters. The important distinction is whether the snapshot is statement-scoped or transaction-scoped.

Why readers and writers can overlap

Assume transaction A starts with a snapshot that sees balance = 100. Transaction B then updates the account to 150 and commits. A still reads the old version because B’s new version was not committed when A’s snapshot was taken. B did not need to overwrite or destroy the version A was using.

A later transaction, with a newer snapshot, sees 150. Both transactions can make progress without a read lock forcing one to wait for the other.

This does not mean MVCC removes all locking or blocking. Two writers updating the same row still need coordination so their changes do not silently overwrite each other. An UPDATE may wait for another update, detect a conflict, or be retried, depending on the engine and isolation level. Explicit locking queries can also deliberately make readers wait.

MVCC also does not automatically prevent every logical race. Write skew is a concurrency anomaly where two transactions read related rows, each sees a condition that permits its own change, and together produce an invalid result. Preventing that may require stronger isolation, explicit locks, or a database constraint.

Where old versions go

Keeping versions forever would consume unbounded storage. Once the database knows that no active snapshot can still see an old version, a cleanup process can reclaim it. In PostgreSQL this process is called vacuum: it removes or marks obsolete row versions for reuse.

Long-running transactions delay that cleanup because their old snapshots may still need the history. This is why an incident involving growing tables, bloat, or delayed vacuum often includes a transaction that has been open for a long time.

When a log line mentions a snapshot, a transaction ID, a visibility conflict, or vacuum being unable to remove old row versions, it is describing this machinery. The snapshot is the rulebook for a transaction’s view; the versions are the history used to satisfy that rulebook; and MVCC is what lets that history support concurrent readers and writers.