MVCC: How Databases Let Readers and Writers Work Without Blocking Each Other

Snapshots Instead of Locks

Traditional locking concurrency control treats a database row like a single shared resource: if a writer wants to change it, everyone else, including readers, has to wait until the writer is done. This keeps things simple but destroys throughput the moment a workload mixes long reads with frequent writes. MVCC takes a fundamentally different approach. Rather than protecting a single copy of a row with a lock, the database keeps multiple versions of that row around at once, each one stamped with information about when it was created and, eventually, when it was superseded. When a transaction begins, it effectively takes a mental photograph, a snapshot, of the database as it existed at that instant. For the rest of that transaction's life, every query it runs consults that snapshot rather than the live, ever-changing state of the table. A writer updating a row does not touch or destroy the version that the reader's snapshot points to; it simply creates a brand new version alongside the old one. The reader keeps seeing its consistent picture of the world, undisturbed, even as the underlying data keeps changing around it. This is the core conceptual leap: concurrency control shifts from 'protect the one copy' to 'keep enough copies that everyone can see a consistent one.'

Why Non-Blocking Reads and Writes Matter

The practical payoff of MVCC is enormous: readers never block writers, and writers never block readers. In a purely lock-based system, a single slow analytical query can stall every update trying to touch the same rows, and a burst of writes can starve reporting queries into timeouts. MVCC breaks that dependency entirely. A dashboard querying millions of rows and a payment service updating a handful of them can run at the same time on the same table without either one waiting on the other. This is exactly why MVCC became the default design in modern relational systems: PostgreSQL has used it since its earliest versions, MySQL's InnoDB storage engine relies on it, Oracle pioneered many of its ideas decades ago, and databases like SQL Server (in snapshot isolation mode) and CockroachDB use variations of the same principle. The gain in concurrency is not a minor optimization; it is often the difference between a database that scales comfortably under mixed workloads and one that grinds to a halt as soon as reporting and transactional traffic collide. Two-phase locking still exists and still guarantees correctness, but it does so at a throughput cost that most modern applications simply cannot afford.

Tagging Versions with Transaction IDs

Mechanically, MVCC works by attaching bookkeeping metadata to every row version. When a transaction creates or modifies a row, the new version is tagged with the transaction ID (or timestamp) that created it. When that row is later updated or deleted, the old version is not immediately erased; instead it gets marked with the ID of the transaction that superseded it, effectively saying 'I was valid until this transaction replaced me.' Every row version therefore carries a visibility window: a creation marker and, once retired, an expiration marker. When a new transaction starts and takes its snapshot, the database records which transactions were already committed at that moment. From then on, whenever that transaction reads a row, it walks through the available versions and picks the one whose creating transaction had already committed before the snapshot began, and whose expiring transaction (if any) had not yet committed by that point. This simple rule is what makes the snapshot consistent: it filters out changes made by transactions that are still in flight or that started after the snapshot was taken, while still surfacing anything that was safely committed beforehand.

Dead Tuples and the Vacuum Tradeoff

Keeping old row versions around instead of overwriting them in place is what makes MVCC work, but it is not free. Every update or delete leaves behind a stale version that some snapshot might still need, and once no active transaction can possibly need it anymore, it becomes pure waste. PostgreSQL calls these leftover, no-longer-visible-to-anyone rows dead tuples; other systems use similar language for the same idea. Left unchecked, dead tuples accumulate inside tables and indexes, bloating storage on disk, slowing down sequential scans, and degrading index efficiency, since the database still has to skip past all that dead weight to find live data. The fix is a background garbage-collection process, known in PostgreSQL as vacuum, which periodically scans tables, identifies versions that no transaction can possibly see anymore, and reclaims that space for reuse. This is the fundamental tradeoff of MVCC: it buys concurrency by deferring cleanup, and if vacuum falls behind, whether from misconfiguration, extremely long-running transactions holding old snapshots open, or sheer write volume, table bloat and performance degradation follow. Tuning autovacuum is a routine, non-optional part of operating an MVCC database at scale.

Handling Write-Write Conflicts

MVCC elegantly solves reader-versus-writer contention, but it cannot make write-write conflicts disappear, because two transactions genuinely cannot both win when they try to update the exact same row at the exact same time. Databases resolve this collision in one of two ways, depending on the isolation level in play. The common approach is to make the second writer wait: the first transaction to touch the row acquires an exclusive lock on that specific row (a much narrower lock than blocking all readers), and the second transaction blocks until the first commits or rolls back, then proceeds against the now-updated data. The alternative, used under stricter isolation levels like serializable or snapshot isolation, is to detect the conflict and abort one of the transactions with a serialization or conflict error, forcing the application to retry. This tradeoff mirrors the classic optimistic-versus-pessimistic concurrency choice: waiting keeps both transactions alive but risks deadlocks and latency, while aborting keeps latency predictable but pushes retry logic onto the application. Which strategy a given database chooses, and under which isolation level, is one of the most consequential design decisions in how it behaves under concurrent write-heavy load.

Frequently asked questions

Try it live

Everything above runs in your browser — open MVCC: How Databases Let Readers and Writers Work Without Blocking Each Other and change the parameters while it is running. Nothing is installed, nothing is uploaded, the whole model lives in one tab.