Multi-version concurrency control, or MVCC, works by letting a database preserve more than one historical version of a logical row while concurrent transactions are active. Each reader receives a visibility rule—often described as a snapshot or read view—and the engine selects the row version that is valid for that reader.
The result is a powerful separation of concerns. Writers can create newer state without immediately destroying older state. Readers can continue using a coherent view without forcing every writer to wait. Cleanup later removes versions that no valid reader can still see.
This guide is the master for the MVCC cluster. The three pillars are How Database Snapshots Work in MVCC, How Row Versions Work in MVCC, and How MVCC Garbage Collection Works. The broader owner remains How Databases Work.
Pillar Explainers for This Master Guide
- Snapshots and read views — which commits are visible to this reader, how statement and transaction snapshots differ, and why stable snapshots are not the same as serializable execution.
- Row versions — how inserts, updates, deletes and aborted work become historical states that different snapshots can resolve differently.
- Garbage collection and vacuum — when old versions become safe to remove, why long transactions retain history and how maintenance debt becomes an operational problem.
The Direct Answer: Keep History Long Enough for Concurrent Readers
Imagine two people using an equipment system. Jo opens a report when camera-2 is available. Ben reserves the camera and commits a moment later. Should Jo’s already-running report suddenly change halfway through?
An MVCC database can say no. It keeps enough historical state for Jo’s read view to continue seeing the earlier version while new transactions see Ben’s reservation. The physical database advances; Jo’s logical view remains coherent under the selected isolation semantics.
PostgreSQL’s current MVCC introduction describes this as a multiversion model in which SQL statements see snapshots of data rather than inconsistent intermediate states from concurrent updates. Its glossary defines MVCC as a mechanism that lets several transactions read and write the same rows without making ordinary reads and writes stall each other unnecessarily.
Why Concurrency Is Hard Without Versions
Suppose a database stored exactly one mutable physical copy of every row and a writer changed that copy directly. A reader arriving halfway through the change would need some protection from incomplete or contradictory state.
One protection strategy is locking: the writer takes a lock, readers wait, then the writer releases the lock. This can be correct but can also create contention when many readers and writers touch the same data.
MVCC changes the trade. Instead of forcing readers to wait for the one mutable copy, the engine can let them read an older committed version while the writer creates a newer state. Concurrency becomes a problem of version visibility rather than only a problem of exclusion.
The Four Responsibilities Inside MVCC
A useful MVCC map has four connected responsibilities.
- Create version history. Inserts, updates and deletes produce transaction-labelled states.
- Define a read view. A snapshot says which transactions count as visible.
- Resolve the visible version. The engine chooses the historical state that satisfies the snapshot and transaction rules.
- Reclaim obsolete history. Cleanup removes versions only after no supported reader can still see them.
Lose any one of these and the system fails. Versions without snapshots create ambiguous history. Snapshots without retained versions cannot be answered. Cleanup without visibility proof can destroy valid reads. Unlimited retention without cleanup eventually consumes storage and maintenance capacity.
A Worked Timeline: Four States of One Camera
Use a simplified sequence model. Sequence 41 inserts camera-2 = available. Sequence 47 changes it to reserved. Sequence 52 deletes it. Sequence 60 inserts it again as repaired.
A snapshot at 45 should see available. A snapshot at 49 should see reserved. A snapshot at 55 should see absence. A snapshot at 65 should see repaired.
The database can reorganise physical pages, indexes or files between those reads without changing the answers. Logical transaction order survives physical movement. This is the same representation-invariance principle that appeared in the B-tree and LSM clusters.
Snapshot Means Visibility, Not a Full Copy
An MVCC snapshot does not require duplicating the whole database. It can be compact metadata describing transaction boundaries and transactions that were still active when the read view was established.
PostgreSQL exposes a pg_snapshot representation containing boundaries such as xmin and xmax plus in-progress transaction identifiers. Its snapshot information documentation makes the central point clear: the read view is transaction metadata used to classify visibility.
The row storage then supplies the candidate versions. Snapshot plus version metadata yields the visible logical row.
Statement Snapshots Versus Transaction Snapshots
At READ COMMITTED, PostgreSQL ordinarily gives each statement a fresh snapshot. A transaction can therefore run one SELECT, wait while another transaction commits, then run a second SELECT and observe newer committed data.
At REPEATABLE READ, PostgreSQL holds a stable transaction snapshot after the relevant starting point. The same transaction can continue seeing an older state even after other transactions commit newer versions.
The current SET TRANSACTION documentation describes these isolation behaviours. MySQL InnoDB’s consistent read documentation documents related multi-version snapshot behaviour under its own isolation model.
Your Own Writes Do Not Behave Like Another Transaction’s Writes
A transaction generally sees the changes it has made itself. If A updates camera-2 to reserved and then queries camera-2 again inside the same transaction, it should not behave as though its own update did not exist.
This is why the phrase “snapshot of the database at time T” should be treated as a learning metaphor, not a complete specification. The transaction’s own writes are incorporated through rules that differ from ordinary visibility of concurrent transactions.
When debugging, always distinguish self-writes from other-transaction writes. Many apparent snapshot inconsistencies disappear once that distinction is made explicit.
Insert, Update and Delete Become Different Version Transitions
An insert creates a first committed candidate. An update creates or records a newer state while preserving enough older state for older snapshots. A delete changes visibility so new snapshots see absence while old snapshots can still see the pre-delete version.
PostgreSQL represents row versions as heap tuples with transaction-related header information. InnoDB uses hidden transaction information and undo records to reconstruct older states. These physical differences matter for tuning and storage, but they serve the same MVCC purpose.
Read the detailed row-version pillar when you need the storage-side mechanics.
Aborted Work Must Remain Invisible
A physical row version can exist even though the creating transaction later aborts. That version must not become committed truth merely because it is newer or physically present.
This immediately disproves the naive rule “choose the newest physical version.” Correct version selection needs transaction status plus snapshot visibility.
The same idea applies to deletion. A delete from an aborted transaction must not make the row disappear for future committed readers.
MVCC Reduces Read-Write Blocking, Not All Blocking
MVCC is sometimes summarized as “readers do not block writers and writers do not block readers.” That captures an important ordinary-case advantage but is too broad as a universal rule.
Writers can still conflict with other writers. Explicit row locks can wait. Schema changes can need stronger locks. SERIALIZABLE transactions can abort. Storage and resource waits can delay queries even when row visibility itself is nonblocking.
Use the slogan to remember the architectural intention, not as a substitute for product-specific lock and isolation documentation.
MVCC and Locking Work Together
In PostgreSQL, row- and table-level locks exist alongside MVCC. In InnoDB, consistent nonlocking reads coexist with locking reads and write locks. An MVCC database is therefore not a lock-free database.
MVCC decides which committed history ordinary readers can observe. Locks coordinate operations that cannot safely proceed independently. These responsibilities overlap at transaction boundaries but should not be conceptually collapsed.
If an application needs to ensure that a row cannot be changed between reading and acting, an explicit locking read or application-level version check can be appropriate. A plain historical snapshot does not automatically reserve the row.
Isolation Level Determines More Than “How Old” the Data Is
Isolation controls which concurrency anomalies are allowed and which conflicts cause waiting, retry or failure. The standard names—READ COMMITTED, REPEATABLE READ, SERIALIZABLE—describe contracts, not merely freshness settings.
A READ COMMITTED statement can see a fresh committed snapshot while still avoiding dirty data. REPEATABLE READ can hold a stable view while still permitting anomalies that serializable execution would reject. SERIALIZABLE can preserve stronger invariants by aborting one of several transactions whose combined pattern is unsafe.
The right isolation level follows from application invariants. A report, reservation system, accounting transfer and analytics export do not necessarily need the same concurrency contract.
Dirty Read, Nonrepeatable Read, Phantom and Write Skew Are Different Problems
A dirty read observes uncommitted data from another transaction. MVCC systems normally prevent this under their documented ordinary isolation modes.
A nonrepeatable read occurs when the same row query within one transaction returns a changed committed value later. Statement-level snapshots can allow this across statements; transaction-level stable snapshots can prevent it.
A phantom involves a predicate result changing because rows matching the predicate appear or disappear. Stable snapshots can prevent ordinary snapshot phantoms within the transaction, though exact standards and engine semantics should be checked.
Write skew is subtler: two transactions read a shared invariant from the same snapshot, update different rows and jointly produce a state that violates the invariant. Stable snapshots alone are not necessarily enough to prevent it.
Worked Write Skew: Two Doctors, One Invariant
There are two doctors, Aisha and Ryan, both on call. The rule is that at least one doctor must remain on call.
Transaction T1 sees both doctors on call and sets Aisha off call. At the same time, T2 sees the same original snapshot and sets Ryan off call. They update different rows, so a simple same-row conflict detector may not see a direct write-write collision.
If both commit, the invariant is broken. Serializable isolation needs a mechanism capable of rejecting a dangerous pattern or otherwise ensuring an equivalent serial outcome. PostgreSQL’s SERIALIZABLE mode uses Serializable Snapshot Isolation and can abort transactions when the dependency pattern would produce a non-serializable result.
Serializable Does Not Mean “Lock the Entire Database”
One path to serializability is strict locking, but modern engines can use other techniques. PostgreSQL’s serializable isolation monitors dependencies among transactions using snapshot-based machinery and predicate locking concepts rather than simply turning every read into a mutually exclusive lock.
The application must still handle serialization failures. An aborted transaction under serializable isolation is often evidence that the database is protecting correctness, not evidence that the transaction engine malfunctioned.
Retries should be bounded and designed around idempotent external effects. Database retry safety is part of the application architecture.
Lost Updates Depend on How the Application Expresses Change
Two applications can both read quantity 10 and independently compute replacement values. If one later overwrites the other’s committed result, part of the intended work disappears.
However, expressing the operation as decrement quantity by 3 inside the database can have different conflict behaviour from read 10, calculate 7 in the application, then write 7. Statement design matters.
Optimistic version columns can also protect application workflows: update only when the row’s application version still equals what the user originally read. If the condition fails, reload and resolve. This is application optimistic concurrency layered on top of the database’s MVCC implementation.
MVCC Is Not the Same as Optimistic Concurrency Control
Both approaches can defer some conflict handling, so they are often confused. MVCC is a version-based visibility architecture for concurrent transactions. Optimistic concurrency control is a broader strategy that proceeds under the expectation that conflicts are uncommon and validates before commit or update.
A database can use MVCC while also supporting optimistic application checks. Another system can use optimistic validation without retaining database row history in the same way.
Use precise terms because they answer different questions: what can I see, what can I change, and when is a conflict detected?
MVCC Is Also Not a Storage Layout
The previous clusters covered B-trees, B+ trees and LSM trees. Those are data-organisation mechanisms. MVCC is a transaction-visibility mechanism.
A B-tree-backed engine can implement MVCC. An LSM-backed engine can implement MVCC. The storage structure answers how keys and versions are located and maintained; MVCC answers which version a transaction may observe.
For ordered indexes and page routing, use How B+ Trees Work. For immutable sorted runs and compaction, use How LSM Trees Work.
Write-Ahead Logging Is Orthogonal to MVCC
A write-ahead log helps recovery reconstruct or replay durable changes after failure. MVCC helps concurrent transactions decide which row versions are visible. The two mechanisms interact but solve different problems.
A transaction can be invisible because it has not committed even though some of its recovery records already exist. Conversely, an old committed row version can remain visible to an old snapshot even though its original log record is long past the active recovery window.
Separating durability from visibility prevents a common conceptual error: “written to the log” does not automatically mean “visible to every reader.”
Indexes Find Candidates; MVCC Decides Which Candidate Counts
An index lookup can find a tuple or row representation whose key matches the query. The database still needs to determine whether that version is visible to this snapshot.
PostgreSQL’s visibility map can sometimes prove that all tuples on a page are visible to all relevant transactions, allowing index-only scans to avoid visiting the heap. When that proof is unavailable, the heap visibility check remains necessary.
This is a clean separation of mechanism: search narrows location; MVCC validates time.
Why Long Transactions Create Invisible Operational Debt
A transaction can hold an old snapshot for hours. During that time, thousands of other transactions can update the database. Those new transactions generate old versions that the long-running reader may still be entitled to see.
The reader can therefore impose a retention cost without blocking foreground writes. PostgreSQL vacuum cannot reclaim versions still protected by an old snapshot. InnoDB purge similarly respects active read views.
This turns transaction age into a capacity metric. Monitor not only query runtime but snapshot lifetime and the amount of historical state retained behind it.
Garbage Collection Is Part of the Concurrency Algorithm
MVCC is incomplete without version reclamation. Keeping every historical state forever would eventually consume unlimited storage and make reads traverse ever more history.
Cleanup must wait until the last relevant snapshot is gone. Then it can reclaim obsolete row versions, associated index state and related metadata according to the engine’s implementation.
The garbage-collection pillar explains ordinary PostgreSQL VACUUM, VACUUM FULL, autovacuum, transaction-ID freezing, long-running transactions and InnoDB purge as different forms of this same historical-retention responsibility.
PostgreSQL and InnoDB Show Two Physical Designs
PostgreSQL
PostgreSQL stores tuple versions in table heap pages and uses transaction metadata such as xmin and xmax to help determine visibility. Updates commonly create new tuple versions. VACUUM later reclaims versions that are no longer visible and performs transaction-ID lifecycle maintenance.
InnoDB
InnoDB maintains multi-version information with transaction identifiers and undo records. Consistent reads can reconstruct older row states from undo history, and purge later removes obsolete undo information when active read views no longer need it.
Do not ask which physical layout is “the real MVCC.” Both illustrate the higher-level contract through different structures. Performance comparisons need workload, schema, hardware and configuration evidence rather than a conclusion from architecture names alone.
MVCC Makes “Current State” a Reader-Relative Concept
At wall-clock 10:05, the physically latest committed value can be reserved while an old transaction correctly sees available. Which one is “current” depends on the question.
For the newest committed database state, reserved is current. For the old transaction’s stable snapshot, available is the current value inside that view. MVCC therefore makes database time explicitly relative to a transaction context.
This does not mean truth is arbitrary. Each view is governed by a precise contract. The mistake is to omit the read context and assume all observers must use one temporal boundary.
A Snapshot Can Be Consistent and Stale
Consistency and freshness are separate dimensions. A six-hour-old analytical snapshot can be internally consistent and badly out of date for an operational dashboard.
Choose snapshot lifetime based on the task. Financial close reports can value stability. Inventory allocation can value newer state. An interactive editor can require explicit conflict detection. There is no universal “best” isolation level independent of the business job.
Serializable Transactions Need a Retry Path
When a database protects serializable semantics by aborting a transaction, the application must decide what to do next. Retrying the database transaction can be appropriate, but only if external side effects are handled safely.
An email already sent, payment already submitted or third-party API already called may not be automatically reversible. The database can roll back its own changes; it cannot automatically undo every outside effect.
Therefore, transaction retry design often includes idempotency keys, outbox patterns, staged side effects or explicit workflow compensation. MVCC lives inside a larger distributed-systems reality.
A Simple Visibility Predicate
For learning, imagine every version has created_by and removed_by transaction identifiers. The snapshot classifies transactions as visible, active, future or aborted.
A version can appear when its creator is visible and its remover is either absent or not visible. If the creator is invisible, ignore the version. If the remover is visible, the version is no longer part of this snapshot’s result.
Real engines optimise this heavily, but the predicate exposes the invariant. Physical storage order is not enough. Transaction state plus snapshot context determines the answer.
A Complete Laboratory: Observe Four Isolation Behaviours
Use a disposable database. Create a table with camera-2 = available. Open two sessions.
- At READ COMMITTED, read camera-2 in A, update and commit in B, then read again in A.
- At REPEATABLE READ, repeat and observe whether A keeps the earlier value.
- Perform an update inside A and verify that A can see its own write.
- Construct a write-skew case across two rows and repeat at SERIALIZABLE, recording any serialization failure.
Do not memorise one vendor’s behaviour and assume every database uses identical semantics. Record the exact database version, isolation level and transaction commands used.
A Cleanup Laboratory: Observe the Cost of an Old Snapshot
Keep one stable-snapshot transaction open. In another session, update the same set of rows repeatedly. Observe version, dead-tuple or undo-history metrics while the old snapshot remains active.
Then close the old transaction and allow cleanup to run. The experiment demonstrates the causal link between active historical visibility and retained version state.
Run this only in a disposable environment. Production maintenance behaviour depends on workload, indexes, autovacuum or purge configuration and resource limits.
Diagnose an MVCC Problem by Classifying the Symptom
- Unexpected old value: inspect snapshot age, isolation level, replica lag and cache layers.
- Unexpected new value: inspect statement-versus-transaction snapshots and self-write rules.
- Blocking: inspect writer conflicts, explicit locks and schema locks rather than blaming version visibility.
- Serialization failure: inspect the concurrent dependency pattern and application retry path.
- Table or undo growth: inspect long transactions, cleanup throughput and update churn.
- Deleted data reappears: inspect transaction rollback, replica/application logic and version-selection bugs; ordinary MVCC should not expose a committed-deleted version to a snapshot for which the deletion is visible.
This symptom-first classification is more useful than changing isolation settings randomly. Each symptom points to a different stage of the mechanism.
What MVCC Does Not Guarantee
- It does not guarantee serializable execution at every isolation level.
- It does not eliminate writer conflicts or all locks.
- It does not provide a permanent audit history.
- It does not create an independent backup.
- It does not guarantee fresh reads from a replica or long-lived snapshot.
- It does not make external side effects transactional automatically.
- It does not decide application-specific conflict policy for you.
These boundaries make MVCC more useful, not less. Once its job is precise, adjacent mechanisms can be designed explicitly instead of being falsely attributed to the concurrency model.
How to Compare Two MVCC Engines Fairly
Hold the workload constant. Specify read/write ratio, transaction length, contention, row size, index count, update locality, isolation level, durability settings and cleanup configuration.
Measure throughput and latency together with version-retention metrics, cleanup bandwidth, storage growth, retry rate and lock waits. An engine that admits writes quickly while accumulating unbounded cleanup debt has not demonstrated sustainable throughput.
Also compare semantics before speed. If one benchmark uses READ COMMITTED and another uses SERIALIZABLE, the systems are providing different guarantees. Performance without a guarantee label is difficult to interpret.
MVCC as a Civilisational Mechanism: Several Valid Pasts Supporting One Shared Present
At a systems level, MVCC solves a familiar coordination problem: many actors need to work from coherent state while the world continues changing. The database does not freeze the whole world for each reader. It preserves enough recent history that each authorised view can remain internally consistent until its job is finished.
The analogy should not be stretched into social philosophy. Its technical lesson is narrower: high-throughput coordination often depends on separating observation from mutation while keeping a traceable rule for when old state stops being authoritative.
That rule appears in databases, caches, deployments, versioned documents and many other systems, but the implementation and correctness criteria differ. Familiar pattern first; exact mechanism second.
Frequently Asked Questions
What does MVCC stand for?
Multi-version concurrency control. The engine retains multiple historical versions or enough information to reconstruct them so concurrent transactions can receive correct read views.
Does MVCC mean readers always see the latest committed value?
No. Readers see the latest version visible to their snapshot under the selected isolation level. A long-lived transaction snapshot can intentionally remain older than the newest committed state.
Does MVCC eliminate locks?
No. Writer conflicts, explicit locking reads, schema operations and other coordination still use locks or related mechanisms. MVCC mainly reduces many ordinary reader-versus-writer conflicts.
Why are old row versions kept?
An older snapshot may still need them. Once no supported reader can see them, the database’s cleanup mechanism can reclaim them.
Is REPEATABLE READ always serializable?
No. Stable snapshots and serializability are different properties. Check the engine’s isolation documentation and application invariants.
Is MVCC the same as a B-tree or LSM tree?
No. B-trees and LSM trees organise stored data. MVCC controls transaction visibility across versions. An engine can combine either storage family with MVCC.
Can MVCC history replace an audit log?
No. MVCC history exists for concurrency and is normally reclaimed when no longer needed. Use an explicit audit or temporal-history mechanism for long-term business history.
Sources and Scope
The primary implementation references are PostgreSQL’s current MVCC introduction, transaction isolation, SET TRANSACTION, snapshot information, page layout and vacuuming documentation, plus MySQL InnoDB’s consistent reads and multi-versioning documentation. The camera sequence, doctor example and laboratory timelines are teaching models, not measured benchmark results.
Final Synthesis: MVCC Lets the Database Move Forward Without Erasing the View You Still Need
The mechanism is simple to state and deep in consequence. Keep historical row states. Give each transaction a visibility rule. Let readers choose the version that belongs to their view. Coordinate conflicting writers. Remove old history only after the last valid reader is gone.
That sequence turns time into an explicit database dimension. The newest physical bytes stop being the only possible truth, and concurrency becomes manageable without freezing the entire system around every read. MVCC works because the database can remember several recent pasts, apply a precise rule for who may see each one, and then forget them safely when their work is finished.
