Concurrency Without Locking: Introduction to MVCC

The Locking Problem

To satisfy the Isolation property of ACID, we need to prevent concurrent transactions from corrupting each other's view of the data. The oldest approach is pessimistic locking:

• Transaction A reads a row → acquires a shared (read) lock.
• Transaction B wants to update that row → needs an exclusive (write) lock → must wait for A to release its read lock.
• Transaction A reads again → must wait for B to release its write lock.

In a high-traffic system, this creates cascading waits. Readers block writers. Writers block readers. A long-running report query can block all writes to a table for minutes. This is the reader-writer conflict, and it's a fundamental scalability bottleneck.

The MVCC Insight: Keep Multiple Versions

Multiversion Concurrency Control (MVCC) solves the reader-writer conflict with a simple but powerful idea: instead of modifying a row in place, keep the old version and create a new version alongside it.

• Readers see the version of the row that existed when their transaction started. They don't care that a newer version exists.
• Writers create new versions. They don't need to wait for readers to finish.
• Writers only block other writers — if two transactions try to modify the same row simultaneously, one must wait.

The MVCC mantra: "Readers never block writers. Writers never block readers."

This is why PostgreSQL can run a long analytical query (reading millions of rows) at the same time as thousands of concurrent writes, without either blocking the other.

The Trade-off: Storage Overhead

MVCC is not free. Every UPDATE creates a new row version. Every DELETE leaves the old version in place (just marked as deleted). Over time, tables accumulate dead tuples — old row versions that no active transaction can see anymore. This is the bloat problem, addressed by the Vacuum process covered later.