MVCC Explained: How Postgres and MySQL Let Readers and Writers Avoid Blocking Each Other

Quick Answer

MVCC (Multi-Version Concurrency Control) is the technique Postgres and MySQL/InnoDB use to let reads and writes happen at the same time without one blocking the other. Instead of locking a row every time someone reads it, the database keeps multiple versions of that row around and hands each transaction a consistent snapshot to read from, based on when that transaction started. We measured this directly: a write to a row completed in 0.088 seconds even while another transaction held a 5-second-long read open on that exact row, versus 3.5 seconds when we forced a real lock instead.

The Problem: Naive Locking Makes Readers and Writers Fight

Imagine the simplest possible way to keep concurrent transactions from stepping on each other: every time anyone reads a row, take a shared lock on it; every time anyone writes a row, take an exclusive lock that blocks every other reader and writer until it's released. This is correct, in the sense that it genuinely prevents dirty reads. It is also disastrous for throughput the moment real usage shows up, because a single long-running report query (a read) can block every write to the rows it touches for as long as it runs, and a single slow write can stack up every reader behind it.

MVCC is the answer every serious general-purpose database engine converged on independently: instead of making readers and writers fight over the same copy of a row, give them different copies. When a row is updated, the database doesn't overwrite it in place and force everyone else to wait — it creates a new version of the row and keeps the old version around too, at least for as long as some other transaction might still need to see it. A reader that started before the update gets handed the old version; a reader that starts after sees the new one. Nobody has to wait on anybody just to read.

This single idea is why 'readers don't block writers, and writers don't block readers' is true in Postgres and MySQL/InnoDB for ordinary reads, even though it sounds like it shouldn't be possible. The rest of this article shows it working, first in a tiny toy implementation you can read in thirty seconds, then with real timing numbers against an actual InnoDB table.

It's worth pausing on just how counterintuitive this is if you're coming from a background of single-threaded programming, where 'two things touching the same piece of memory at once' is almost always a bug waiting to happen. A database handles this routinely, thousands of times a second, specifically because it refuses to let a read and a write ever compete for the same physical copy of the data. Multiple versions sound wasteful at first glance, but the cost of storing a handful of extra row versions is tiny compared to the cost of making every read wait behind every write, which is the alternative MVCC was built to avoid.

A Toy MVCC Engine in About 25 Lines

The cleanest way to see what's actually happening is to build the smallest version of the idea that still works. Here's a toy single-row MVCC table in Python: each 'row' is really a list of versions, each version tagged with the transaction ID that created it and (if it's been superseded) the transaction ID that deleted it.

versions = [{"value": 1000, "created_by": 0, "deleted_by": None}]

def read_snapshot(as_of_txn_id):
    visible = [v for v in versions if v["created_by"] <= as_of_txn_id
               and (v["deleted_by"] is None or v["deleted_by"] > as_of_txn_id)]
    return visible[-1]["value"] if visible else None

def writer_job():
    txn = new_txn_id()
    for v in versions:
        if v["deleted_by"] is None:
            v["deleted_by"] = txn
    versions.append({"value": 4242, "created_by": txn, "deleted_by": None})

The entire trick is in read_snapshot: a reader doesn't ask 'what's the current value', it asks 'what was visible as of my own transaction ID'. A writer never deletes anything in place — it marks the old version as superseded by its own transaction ID and appends a brand new version. Running this with a reader that opens its snapshot, then a concurrent writer that updates the value while the reader is still 'reading' (simulated with a sleep), produces this real output:

[reader] txn=1 opened snapshot, sees value=1000
[writer] txn=2 starting update at t=1792058924.625
[writer] txn=2 committed new version at t=1792058924.628
[reader] txn=1 after sleep, still sees value=1000 (unchanged)

Final state has 2 row versions stored:
[{'value': 1000, 'created_by': 0, 'deleted_by': 2},
 {'value': 4242, 'created_by': 2, 'deleted_by': None}]
A BRAND NEW transaction (no stale snapshot) now reads: 4242

The writer finished its update in about 3 milliseconds without waiting for the reader at all, the reader kept seeing 1000 for its entire lifetime regardless of when the writer committed, and a fresh transaction that opens afterward correctly sees the new value, 4242. That's the whole mechanism, minus about two decades of engineering around making the version list efficient to store, scan, and eventually garbage-collect, which real engines spend most of their MVCC-related code on.

The Same Thing, for Real, on InnoDB — With Timings

To confirm the toy model matches reality, we ran the equivalent scenario against a real MariaDB server using the InnoDB engine. First, a baseline: how long does an ordinary update take with zero contention?

time mysql -u root -e "UPDATE accounts SET balance=1234 WHERE id=1;"

real    0m0.038s

Now, transaction A opens a REPEATABLE READ transaction, reads the row (opening its MVCC snapshot), and then sleeps for 5 seconds while still inside that open transaction — simulating a slow report or a long-running read. While A is mid-sleep, transaction B runs a real UPDATE ... WHERE id=1 on the exact same row and commits:

time mysql -u root -e "UPDATE accounts SET balance=4242 WHERE id=1;"

real    0m0.088s

B's write finished in 0.088 seconds — essentially the same order of magnitude as the zero-contention baseline of 0.038 seconds — while A's transaction was still five seconds away from finishing. A's own snapshot read, checked both before and after B's write, returned 1000 both times: it never saw B's write, because B's write happened after A's snapshot was taken, and InnoDB doesn't retroactively change what an open snapshot sees.

To make the contrast concrete, we then reran the identical scenario except A used a locking read instead of a plain one — SELECT ... FOR UPDATE, which takes a real row lock rather than just reading from a snapshot:

-- A's transaction:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
SELECT SLEEP(5);
COMMIT;

time mysql -u root -e "UPDATE accounts SET balance=7777 WHERE id=1;"

real    0m3.531s

This time B's write took 3.531 seconds — it genuinely had to wait for A's lock to release at commit. Same two transactions, same row, same 5-second window; the only variable was whether A's read took an actual lock or just opened an MVCC snapshot. That's the entire value proposition of MVCC in one side-by-side number: 0.088 seconds versus 3.531 seconds, for the exact same amount of concurrent work.

Old Versions Don't Live Forever — Someone Has to Clean Them Up

Keeping every version of every row around forever would make the database grow without bound, so both major engines run a background cleanup process, and the two differ in a way that matters operationally. In PostgreSQL, a row version that's been superseded by an update or delete becomes a 'dead tuple' — it isn't removed immediately, it's marked and left in place. The VACUUM process (normally run automatically by the autovacuum daemon) later scans for these dead tuples, removes the index entries pointing at them, and frees their space for reuse. If autovacuum falls behind — commonly because a long-running transaction is holding an old snapshot open and nothing can be cleaned up while any transaction might still need it — dead tuples accumulate and the table physically bloats, which is the famous 'Postgres table bloat' operational problem.

MySQL's InnoDB takes a related but structurally different approach: old row versions live in a separate undo log rather than inline in the table, and a dedicated background purge thread (or threads) continuously scans that undo log for versions that no transaction could possibly still need — specifically, versions older than every currently open transaction's read view — and permanently deletes them, including cleaning up the matching delete-marked index entries. The underlying constraint is identical to Postgres's: a version can't be purged while any open transaction's snapshot might still reference it, so a single very long-running transaction can cause InnoDB's undo log to grow large for the same root reason Postgres bloats, even though the storage mechanism (separate undo log versus inline dead tuples) is different.

The practical lesson for either engine: a long-idle transaction left open by mistake (a connection that started a transaction and then just... sat there) isn't a harmless no-op. It pins the oldest snapshot the database has to keep satisfying, which blocks cleanup for every row anyone else modifies in the meantime, not just the rows that idle transaction touched.

MVCC Doesn't Make Locking Disappear Entirely

The demos above are deliberately precise about one word: ordinary reads don't block and aren't blocked. Two things still involve real locking even in a fully MVCC-based engine. First, write-write conflicts: if two transactions both try to update the same row before either commits, one of them has to wait (or, depending on the isolation level and engine, fail outright) — MVCC solves reader-versus-writer contention, not writer-versus-writer contention, because there's no sensible way to keep two different 'next version' candidates for the same row simultaneously. Second, explicit locking reads — SELECT ... FOR UPDATE in both Postgres and MySQL — deliberately opt out of snapshot-only behavior precisely because the calling code needs a real lock, usually because it's about to read a value and then write a dependent change back, and a stale snapshot read would let two transactions both act on the same soon-to-be-outdated value. That's exactly the mechanism our 3.531-second demo above triggered on purpose, to show what 'really blocked' looks like for comparison.

Isolation levels change which of these guarantees you get and how strict the snapshot behavior is — Repeatable Read versus Read Committed produce genuinely different anomalies even though both are built on the same underlying MVCC machinery, which we demonstrated with live output in the companion article on transaction isolation levels. MVCC is the engine room; isolation levels are the dashboard settings controlling what that engine room is allowed to do.

It's worth internalizing one more consequence of the write-write exception described above: since two transactions updating the same row can't both proceed on their own private version, the number of concurrently open write transactions touching the same 'hot' rows is still a real scaling limit, no matter how good your engine's MVCC implementation is. A counter row that thousands of requests per second all try to increment will serialize on that single row's write path regardless of isolation level, because MVCC was never designed to solve contention between writers, only contention between readers and writers. Recognizing this distinction up front — MVCC removes reader/writer blocking, not writer/writer blocking — explains why a 'hot row' problem under heavy concurrent writes needs an entirely different fix (batching, sharding the counter, or an engine-specific atomic increment) rather than being treated as something MVCC should have already solved.

Frequently Asked Questions