Concepts and theorems · Intermediate

MVCC: concurrency control with versions and visibility

Understand why keeping old row versions supports snapshots without eliminating every lock or anomaly.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Several versions of one logical row
  2. Following an update and two readers
  3. Snapshot scope changes the experience
  4. Locks and cleanup still matter
  5. Ask Logeia about this articleCheck your understanding

Several versions of one logical row

Multiversion concurrency control uses multiple versions of data together with visibility rules to coordinate concurrent transactions. Instead of immediately overwriting every trace of an old row, a reader selects a version visible to its snapshot. This can avoid exposing partially committed changes while reducing some reader-writer interference. The word snapshot does not by itself specify when the view is taken or how long it lasts. Those details depend on the database implementation and transaction isolation level, so MVCC should be treated as a family of mechanisms rather than one universal behavior.

Following an update and two readers

Suppose a balance starts at 100 and transaction A updates it to 150. Another ordinary transaction does not simply read A uncommitted replacement. Even after A commits, a reader using a valid earlier snapshot may still see 100. Two readers returning different values at one wall-clock moment can therefore be consistent with their respective snapshot rules. To interpret the result, record transaction boundaries, statement boundaries, commit order, and isolation settings. A single query result without that context is insufficient to reconstruct the version-selection decision.

Snapshot scope changes the experience

In PostgreSQL Read Committed mode, an ordinary SELECT uses a snapshot associated with the start of that statement. A later statement in the same transaction can therefore observe a newly committed change. Repeatable Read uses a stable transaction-level snapshot under its documented rules. These experiences cannot be predicted merely by knowing that both modes use MVCC. Specify whether the application needs statement consistency, a repeatable transaction view, or serializable outcomes. A stable snapshot is useful, but it is not automatically a proof that every concurrent transaction combination preserves a cross-row invariant.

Locks and cleanup still matter

MVCC does not remove every lock or conflict. Concurrent updates to the same row can still wait, fail, or require retries according to the implementation and isolation level. Old versions also occupy space until the system can safely reclaim them; a long-running transaction can extend the period during which a version remains potentially visible. Diagnose version churn, transaction lifetime, and cleanup behavior as well as raw update throughput. Claims that MVCC means no waiting, constant storage use, or automatic serializability confuse the version mechanism with guarantees it does not independently provide.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path