When you run a SELECT while another transaction is mid-update, most modern databases return consistent data instantly — no waiting for the write to finish. This isn’t magic. It’s multi-version concurrency control (MVCC), the mechanism that lets readers and writers operate simultaneously without stepping on each other.

MVCC underpins PostgreSQL, MySQL’s InnoDB engine, Oracle, CockroachDB, and many others. Understanding it explains a surprising amount of otherwise mysterious database behavior — from why long-running transactions can degrade performance to why autovacuum exists at all.

How It Works

The core idea is simple: instead of one authoritative copy of a row, the database keeps multiple versions of it, each tagged with the transaction that created it and the transaction that superseded it.

When a transaction updates a row, the database doesn’t overwrite the existing data. It writes a new version and marks the old one as obsolete from that transaction’s perspective. Readers with an earlier snapshot still see the old version. Readers that started after the update see the new one.

Each transaction gets a snapshot at the moment it begins — or at the moment of each statement, depending on the isolation level. That snapshot defines which row versions are visible. A version is visible if it was committed before the snapshot was taken and hasn’t been superseded by a later committed transaction within the snapshot’s view.

The result: reads never block writes, and writes never block reads. Two writers touching the same row still need conflict detection — that part still involves locking or optimistic validation — but readers are never in the queue.

The Cost: Dead Tuples and Garbage Collection

MVCC’s tradeoff is storage overhead. Every update and delete leaves behind an old version of the row until nothing needs to see it anymore. PostgreSQL calls these dead tuples; other systems use similar terminology.

Left uncollected, dead tuples bloat tables and indexes, slow down sequential scans, and waste I/O bandwidth. This is why PostgreSQL runs autovacuum: a background process that identifies dead tuples no current transaction can see and reclaims that space.

Autovacuum is usually invisible, but it stops being invisible when something holds a transaction open for a long time. MVCC guarantees that any open snapshot can see all versions created after it began, so a long-running transaction forces the database to retain dead tuples from its snapshot forward — even if millions of updates have happened since. A single idle transaction left open overnight can cause dramatic table bloat and eventually trigger a wraparound protection mechanism that takes the database offline for emergency maintenance. This is why long transactions are legitimately expensive in MVCC systems, not just a theoretical concern worth footnoting.

Isolation Levels in Practice

MVCC makes it practical to implement multiple isolation levels without catastrophic performance tradeoffs.

Read Committed takes a fresh snapshot at the start of each statement. A transaction can see different data over its lifetime as other commits land. It’s the default in PostgreSQL and MySQL and is appropriate for most OLTP workloads, but it exposes applications to non-repeatable reads.

Repeatable Read (often implemented as snapshot isolation in MVCC databases) takes one snapshot at the start of the transaction. Reads within the same transaction are consistent with each other regardless of commits that arrive mid-flight. This is the source of the guarantee that “the same query returns the same result twice within one transaction.”

Serializable adds further tracking to detect and abort transactions whose concurrent execution would produce results inconsistent with any serial ordering. It’s the strongest guarantee and carries the highest overhead.

Choosing the wrong isolation level is a common source of subtle bugs. Write skew, phantom reads, and non-repeatable reads all result from operating at a lower level than the workload actually requires.

What This Means for Application Code

MVCC has a few direct implications worth building into habits:

Keep transactions short. The longer a transaction runs, the more dead tuple accumulation it blocks and the more it can interfere with vacuum’s ability to do its job.

Don’t leave idle transactions open. An ORM that opens a transaction and then waits for user input, or a connection pool that starts transactions speculatively, is a common source of bloat in production systems.

Understand your isolation level. Read committed gives the best throughput but permits certain anomalies. If two reads within a request need to be consistent with each other, use a higher isolation level or be deliberate about query ordering.

Batch large updates carefully. A transaction updating millions of rows holds all the displaced versions live until it commits. Breaking work into smaller batches keeps the version pressure manageable.

MVCC is one of those mechanisms that’s easy to use correctly by accident at small scale and surprisingly easy to misuse as workloads grow. Knowing how the version ledger works is the foundation for writing database code that stays fast under production load.