MVCC

Definition: Multi-Version Concurrency Control (MVCC) is a concurrency control method where every write creates a new version of a row instead of overwriting it in place, so readers never block writers and writers never block readers.

MVCC exists because the alternative, blocking every reader behind every writer with locks, does not scale to mixed read/write workloads. It is the concurrency-control strategy underneath most modern relational databases, and it changes how you reason about isolation levels, vacuuming, and even backup strategy.

How It Works

• Instead of updating a row in place, a write creates a new tuple version tagged with the transaction ID that created it (xmin) and, once superseded, the transaction ID that deleted or replaced it (xmax).
• Every transaction gets a snapshot at the moment it starts (or, at Read Committed, at the start of each statement): a record of which transaction IDs were already committed and visible at that point.
• A row version is visible to a transaction only if its creator committed before the snapshot was taken, and it hasn’t been deleted/replaced by another transaction visible in that same snapshot.
• Old versions that no longer matter, no active transaction’s snapshot can still see them, become dead tuples. A background process (VACUUM in PostgreSQL, purge threads in InnoDB) reclaims that space.
• The exact moment a snapshot is taken depends on isolation level: Read Committed takes a fresh snapshot for every statement, so two SELECTs in the same transaction can see different data if a concurrent commit happened between them; Repeatable Read and Serializable take one snapshot for the whole transaction, so every read within it sees the same consistent point in time.

Under the Hood

Two transactions reading the same row at different points while a third transaction updates it:

Worked example 1: snapshot isolation in action

• Given: row id=7 starts at balance=500, created by transaction 90.
• Step: T1 begins and takes a snapshot that sees everything committed up through xid 100, so it can see the xmin=90 version.
• Step: T2 (xid 101) updates the row: the engine writes a new tuple with xmin=101 and stamps the old tuple’s xmax=101, then commits.
• Step: T1 runs the same SELECT again, still using its original snapshot, which predates xid 101.
• Answer: T1 still sees balance=500. The old version is not visible to T2’s write logic, but it hasn’t been physically removed either, it’s needed for T1’s snapshot, and for any other transaction whose snapshot predates xid 101.

Worked example 2: dead tuple accumulation

• Given: a counters row is updated 10,000 times per hour by a busy service, no long-running transactions are open.
• Step: each UPDATE creates a new tuple version and marks the previous one dead, since no snapshot still needs it once nothing older is active.
• Step: without regular vacuuming, PostgreSQL accumulates thousands of dead row versions physically inside the table’s pages, all still occupying disk space and slowing down sequential scans and index lookups over that row.
• Answer: VACUUM (or autovacuum) scans the table, confirms no active snapshot can see a given dead tuple, and marks its space reusable. Skipping this is the single most common cause of PostgreSQL “table bloat.”

Worked example 3: write-write conflict, MVCC doesn’t remove locking entirely

• Given: two transactions, T4 and T5, both begin and both try to UPDATE the same row (id=7) in the same instant.
• Step: T4 acquires the row-level write lock first and proceeds; T5’s UPDATE blocks, waiting, because MVCC only solves read/write conflicts, not write/write conflicts on the identical row.
• Step: T4 commits. T5 now wakes up, and, depending on isolation level, either re-reads the fresh committed value and applies its update on top of it (Read Committed), or raises a serialization error and forces the client to retry (Repeatable Read/Serializable).
• Answer: MVCC removes reader/writer blocking, it does not remove the need to serialize two writers touching the same row.

Visibility Rules

A row version is visible to a given transaction’s snapshot only if all of the following hold:

• The version’s creating transaction (xmin) committed before the snapshot was taken, not just before the read, before the snapshot.
• The version’s creating transaction is not the current transaction’s own later, uncommitted work from a different statement (unless the isolation level explicitly allows seeing your own writes, which it always does).
• The version has not been superseded, its xmax is either unset, or belongs to a transaction that had not committed as of the snapshot.
• The creating transaction wasn’t rolled back. An aborted transaction’s tuples are simply invisible to everyone, forever, and get cleaned up like any other dead tuple.

Why It Matters

MVCC is what lets an analytics query run for ten minutes against a table that’s being written to thousands of times a second, without either side blocking the other. Lock-based concurrency control (readers block writers, writers block readers) collapses under that kind of mixed workload; MVCC sidesteps the conflict by giving each transaction its own consistent view of the data instead of fighting over a single shared copy.

• It makes long-running reporting queries and OLTP traffic coexist on the same primary database, at least for a while, without one starving the other.
• It underpins point-in-time consistency for backups: a hot backup tool can read a consistent snapshot of a multi-terabyte database while writes continue, because it’s reading versions, not blocking them.
• It changes capacity planning: bloat and vacuum throughput become operational metrics to watch, not just CPU and disk I/O, on any MVCC database running a write-heavy workload.

Snapshot Isolation vs True Serializability

Plain MVCC snapshot isolation is not automatically the same as the Serializable isolation level in the ANSI SQL standard. Snapshot isolation prevents dirty reads, non-repeatable reads, and phantom reads, but it can still allow “write skew”: two transactions each read overlapping data, each makes a change that is valid given what they read, but the combined result violates an invariant neither transaction saw broken on its own (the classic example is two on-call doctors each independently deciding it’s safe to go off-call because the other one is still listed as on-call). PostgreSQL’s actual Serializable level adds extra runtime checks, Serializable Snapshot Isolation (SSI), on top of MVCC specifically to detect and abort transactions involved in this kind of conflict.

Vacuum and Version Cleanup

PostgreSQL’s autovacuum daemon periodically scans each table, identifies dead tuples no snapshot can still see, and marks their space reusable by future inserts, it does not usually shrink the file on disk (that needs VACUUM FULL, which rewrites the table and takes an exclusive lock). InnoDB takes a different path: it stores only the current row version in the main table and keeps older versions in a separate undo log segment, purged by a background thread once no active read view needs them. Both approaches solve the same problem, bounding how much old-version data accumulates, with different storage trade-offs: PostgreSQL pays in table bloat if vacuum falls behind, InnoDB pays in undo log growth under long-running transactions.

Transaction ID Wraparound

PostgreSQL’s transaction IDs are a 32-bit counter, about 4.2 billion possible values, wrapping back to the start once exhausted. Because visibility comparisons rely on being able to tell “in the past” from “in the future” relative to the current xid, PostgreSQL reserves roughly half that range, around 2 billion transactions, as a safety margin and forces the database into read-only mode if autovacuum can’t freeze old tuples fast enough to stay under that limit. A busy database issuing thousands of write transactions per second can, in theory, approach this in a matter of months if autovacuum is disabled or badly tuned, which is why datfrozenxid age is a standard PostgreSQL monitoring metric.

Common Pitfalls

• Leaving a transaction open (an idle connection with BEGIN but no COMMIT) for hours: its old snapshot prevents vacuum from reclaiming any dead tuple newer than that snapshot, across the entire database, not just the tables that transaction touched.
• Assuming MVCC means “no locking ever.” Writes to the same row still conflict, the second writer either blocks until the first commits/rolls back, or fails immediately depending on isolation level and locking mode.
• Not accounting for xid wraparound: transaction IDs are a finite 32-bit counter in PostgreSQL, and an unmonitored database that never vacuums can, in rare cases, approach wraparound and force emergency read-only mode.
• Expecting SELECT COUNT(*) to be instant. MVCC makes an exact row count expensive because visibility must be checked per row, there’s no single maintained counter the way there might be in a simpler engine.
• Confusing snapshot-based Repeatable Read with true Serializable: MVCC alone prevents dirty and non-repeatable reads, but write skew anomalies can still slip through unless the engine adds extra checks (Serializable Snapshot Isolation).
• Running a large batch UPDATE and being surprised the table temporarily doubles in size, since every touched row gets a full new version before the old ones are eligible for cleanup.
• Assuming an index automatically reflects only live rows. In PostgreSQL, index entries can point at dead heap tuples until vacuum catches up, which is why index-only scans still need a visibility map check.
• Treating replica lag as unrelated to MVCC: a read replica applying changes slightly behind the primary is, in effect, serving a slightly older but still internally consistent MVCC snapshot, not corrupted data.

Comparison

Approach	Readers block writers?	Writers block readers?	Storage overhead	Used by
MVCC	No	No	Extra row versions until vacuumed/purged	PostgreSQL, MySQL InnoDB, Oracle
Two-Phase Locking (2PL)	Yes (shared/exclusive locks)	Yes	Minimal, single copy per row	Older/simpler engines, some strict-serializable systems
Optimistic Concurrency Control	No (checks at commit time)	No	Minimal, but retries on conflict	High-contention distributed systems, some NewSQL

2PL is simpler to reason about and has no version cleanup cost, but it directly trades concurrency for that simplicity: a long-running report can genuinely stall write throughput. MVCC accepts extra storage and background cleanup work in exchange for readers and writers never waiting on each other. Optimistic concurrency control pushes the check even later, to commit time, betting that conflicts are rare enough that occasional retries beat the cost of locking or versioning up front, a bet that holds well for low-contention, geographically distributed workloads and breaks down under hot, frequently-contended rows.

Example

PostgreSQL is the textbook MVCC implementation: every row physically carries xmin/xmax system columns, and autovacuum runs continuously in the background to clean up dead tuples and prevent transaction ID wraparound. You can inspect it directly with SELECT xmin, xmax, * FROM table_name, the hidden columns are queryable like any other.

MySQL’s InnoDB engine also uses MVCC but structures it differently: it keeps a single current version of each row in the clustered index and reconstructs older versions on demand by walking an undo log, applying undo records in reverse until it reaches the version a given read view should see, rather than storing multiple full row versions inline in the table like PostgreSQL does. Oracle Database’s implementation is closer to InnoDB’s: it also reconstructs prior versions from undo segments rather than keeping them inline, which is why a very old, long-running query in Oracle can fail with an “ORA-01555: snapshot too old” error if its needed undo data gets overwritten first.

This distinction matters in practice: a system advertising “snapshot isolation” is not automatically safe from every concurrency anomaly, and teams that need true serializable guarantees should confirm which specific mechanism their database uses, not just that it “uses MVCC.”

Related Terms

• Transaction Isolation Levels — MVCC is the mechanism, isolation levels are the guarantees it’s used to implement
• Write-Ahead Logging (WAL) — new row versions are logged the same way any other write is
• ACID (Atomicity, Consistency, Isolation, Durability) — MVCC is the most common way modern engines deliver the Isolation property
• Database Replication — replicas often apply MVCC-versioned changes to stay consistent with the primary
• Query Optimization and Execution — the planner accounts for visibility checks when estimating scan cost
• Database Indexing Internals — index entries can reference multiple live row versions until vacuum catches up