MVCC: How Postgres and MySQL Read Without Blocking Writes

⏱️ Lectura: 14 min

A SELECT query in PostgreSQL never waits for an UPDATE on the same row to finish: it reads an earlier version while the write happens in parallel, with no locks involved. That behavior has a name: MVCC (Multi-Version Concurrency Control), the mechanism that PostgreSQL, MySQL/InnoDB, Oracle, SQLite, and CockroachDB use so readers and writers never block each other.

📑 En este artículo

1. TL;DR
2. What MVCC Is and Why It Matters
3. How MVCC Works Under the Hood
   1. What Exactly Is a Snapshot
4. Practical Examples
5. Isolation Levels: Which Anomalies You Tolerate
6. Getting Started: Try It Yourself
7. Real-World Use Cases
8. Common Mistakes and Best Practices
9. Comparison with Alternatives: MVCC vs. Pessimistic Locking
10. Going Deeper: Vacuum, Snapshots, and SSI
11. Frequently Asked Questions
    1. Does MVCC completely replace locks?
    2. Why does my Postgres table take up more space if I never delete rows?
    3. Does MySQL/InnoDB use MVCC the same way as PostgreSQL?
    4. Does SQLite have MVCC?
    5. When is it worth using Serializable if it’s slower?
    6. What’s the difference between snapshot isolation and true serializability?
12. References

Understanding MVCC is not database trivia: it explains why a table grows even when you delete rows, why the VACUUM command exists, and why two simultaneous transactions sometimes fail with a serialization error that has to be retried.

TL;DR

• You’ll understand why a SELECT never blocks an UPDATE in Postgres, MySQL, or SQLite thanks to MVCC.
• You’ll be able to read Postgres’s internal xmin and xmax columns to know which version of a row you’re looking at.
• You’ll know how to tell Read Committed, Repeatable Read, and Serializable apart, and pick the right level for your case.
• You’ll be able to write a retry loop that handles serialization errors (SQLSTATE 40001) in concurrent transactions.
• You’ll understand why VACUUM exists and how to measure a table’s bloat with pg_stat_user_tables.
• You’ll be able to explain the difference between Postgres’s versioning model and InnoDB’s undo log.
• You’ll know what write skew is and why even Repeatable Read doesn’t always prevent it.

What MVCC Is and Why It Matters

MVCC is a concurrency control strategy: instead of locking a row so only one transaction can touch it at a time, the database keeps multiple versions of that row and shows each transaction the version that matches its own point in time. A reader never waits for a writer, and a writer never waits for a reader: each one works on its own snapshot of the data.

The alternative model, two-phase locking (2PL, two-phase locking), forces any transaction that wants to read a row to wait if another transaction has it locked for writing. It works, but under workloads with many concurrent reads it creates wait queues that MVCC avoids by design. That’s why PostgreSQL, MySQL/InnoDB, Oracle, SQL Server (with snapshot isolation enabled), and SQLite in WAL mode adopted it as the foundation of their transactional engine.

The difference shows up in production: an application with MVCC can run long SELECT reports without slowing down the INSERTs and UPDATEs arriving at the same time. The cost doesn’t disappear, it shifts: someone has to clean up the old versions nobody needs anymore, and that job falls to the vacuum process in Postgres or the purge thread in InnoDB.

How MVCC Works Under the Hood

Each engine implements MVCC differently, but the underlying idea is the same: every row carries invisible metadata marking when that version was born and when it died.

PostgreSQL adds two hidden columns to every physical row: xmin, the ID of the transaction that created it, and xmax, the ID of the transaction that replaced or deleted it (empty while the version is still alive). When you run an UPDATE, Postgres doesn’t modify the row in place: it creates a new row with a new xmin and marks the old row’s xmax with the current transaction, as described in the official concurrency control documentation. Each transaction decides which version of a row it “sees” by comparing those IDs against its own snapshot.

What Exactly Is a Snapshot

A snapshot isn’t a copy of the data: it’s a list of transaction IDs. When a transaction starts (or runs its first SELECT, depending on the isolation level), Postgres records which transactions are active at that instant. Any row created by a transaction that was on that list, or by a transaction that hadn’t started yet, is considered invisible. Everything else is visible. Comparing a handful of integers is far cheaper than copying data, which is why taking a snapshot is practically free.

SELECT xmin, xmax, id, saldo FROM cuentas;

Running this query, you’ll see that the xmin and xmax columns exist even though you never declared them: they’re internal metadata that Postgres exposes on request to debug exactly this mechanism.

MySQL/InnoDB does the opposite: it modifies the row in place, but before overwriting it, it copies the previous version to a separate undo log. If another transaction needs an old version, InnoDB reconstructs it by reading the undo log backward, as InnoDB’s documentation explains. This makes Postgres tend to accumulate more disk space from old versions (the well-known bloat), while InnoDB pays the cost by reconstructing old rows as the undo log grows.

SQLite, in its WAL (Write-Ahead Logging) mode, achieves a similar effect: writers append new pages to the end of a log instead of modifying the main file, and readers keep seeing a consistent version of the file while the log grows, according to the official WAL documentation.

flowchart TD
Row["Original row: xmin=100, xmax=empty"] -->|"UPDATE from tx 105"| NewVersion["New version: xmin=105, xmax=empty"]
Row -->|"xmax is set to 105"| Dead["Old version: xmin=100, xmax=105"]
Dead -->|"VACUUM reclaims it"| Free["Reusable space"]

Practical Examples

The simplest way to see MVCC in action is two psql sessions open at the same time:

-- Session A: opens a transaction and hasn't committed yet
BEGIN;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
-- no COMMIT yet

-- Session B: in parallel, on another connection
BEGIN;
SELECT saldo FROM cuentas WHERE id = 1;
-- returns the balance BEFORE session A's UPDATE, without waiting on any lock
COMMIT;

Session B reads the version that was visible when its transaction started, regardless of session A writing to the same row. No lock stops the read.

sequenceDiagram
participant W as Writer transaction
participant D as MVCC engine
participant R as Reader transaction
W->>D: BEGIN
W->>D: UPDATE cuentas SET saldo = saldo - 100
R->>D: BEGIN
R->>D: SELECT saldo FROM cuentas
D-->>R: version prior to W's UPDATE
W->>D: COMMIT
Note over W,R: R never waited on W's lock

The second example is more realistic: a bank transfer with automatic retries when the engine detects a serialization conflict.

import psycopg2
from psycopg2 import errors

def transferir(conn, origen, destino, monto, intentos=3):
    for intento in range(intentos):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    cur.execute(
                        "UPDATE cuentas SET saldo = saldo - %s WHERE id = %s",
                        (monto, origen),
                    )
                    cur.execute(
                        "UPDATE cuentas SET saldo = saldo + %s WHERE id = %s",
                        (monto, destino),
                    )
            return True
        except errors.SerializationFailure:
            conn.rollback()
    return False

If two concurrent transfers create a conflict that MVCC can’t resolve silently, Postgres aborts one of the two with the code SQLSTATE 40001. The function catches that error, rolls back, and retries: it’s the standard pattern for working with SERIALIZABLE in production.

Isolation Levels: Which Anomalies You Tolerate

The SQL standard defines isolation levels based on which concurrency anomalies they allow. MVCC doesn’t eliminate the need to choose a level: it only changes how it’s implemented underneath.

Isolation Level	Dirty read	Non-repeatable read	Phantom read	When to use it
Read Uncommitted	Possible	Possible	Possible	Almost never in production
Read Committed (default in Postgres and MySQL)	No	Possible	Possible	Most OLTP apps
Repeatable Read	No	No	Possible in MySQL; not in Postgres	Consistent reports within a transaction
Serializable	No	No	No	Bank transfers, critical inventory

Postgres implements Repeatable Read and Serializable with two different snapshot strategies: Repeatable Read uses classic snapshot isolation, while Serializable adds Serializable Snapshot Isolation (SSI), which detects conflict patterns that can’t be serialized and aborts one of the transactions instead of letting a silent anomaly through.

Getting Started: Try It Yourself

All you need is Docker to reproduce the examples above in minutes:

docker run --name pg-mvcc -e POSTGRES_PASSWORD=demo -p 5432:5432 -d postgres:16
psql -h localhost -U postgres

With the connection open, create the test table and run the two-session example from the previous section in two separate terminals:

CREATE TABLE cuentas (id serial PRIMARY KEY, saldo numeric NOT NULL);
INSERT INTO cuentas (saldo) VALUES (1000), (500);

To confirm what’s happening underneath, three verification queries:

SHOW transaction_isolation;
SELECT pid, state, query FROM pg_stat_activity WHERE state = 'active';
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'cuentas';

The last query is the key one: n_dead_tup shows how many old versions autovacuum hasn’t reclaimed yet. If that number keeps growing while the table isn’t growing in real rows, you have a long transaction blocking the cleanup.

💡 Tip: Run EXPLAIN (ANALYZE, BUFFERS) on a slow query against a table with a lot of n_dead_tup: you’ll see how many discarded versions the planner had to scan through before finding the visible row.

Real-World Use Cases

Any OLTP application with simultaneous reads and writes benefits from MVCC: an e-commerce site showing stock while purchases are being processed, a banking system running balance reports while transfers come in, or an analytics dashboard querying a table that updates in real time.

A concrete case: an online store running a flash sale keeps its catalog under massive stock reads while thousands of purchases update the same rows. Without MVCC, each stock read would compete for the same lock as the purchase writes, and the site would become unusable at peak traffic. With MVCC, reads see a consistent snapshot of stock without slowing down a single write.

Modern distributed databases take the idea a step further: CockroachDB and other databases compatible with the PostgreSQL protocol extend MVCC across multiple nodes, using hybrid timestamps instead of a local transaction counter, so the same principle (lock-free readers) works even when data is spread across geographies.

Common Mistakes and Best Practices

The most common mistake is leaving a transaction open without realizing it: an ORM connection that doesn’t close its cursor, a forgotten psql client with a BEGIN; and no COMMIT. As long as that transaction stays alive, Postgres can’t reclaim any version newer than its snapshot, so bloat grows even if the rest of the application works perfectly.

The second mistake is assuming Repeatable Read prevents all concurrency conflicts. The classic case of write skew: two on-call doctors each check whether at least one other doctor is available before requesting a day off. Both read “yes, another one is available” under Repeatable Read, both request the day, and the hospital ends up with no one on call. No individual row had a conflicting write, so Repeatable Read doesn’t catch it: you need Serializable for Postgres to identify the pattern and abort one of the two transactions.

⚠️ Watch out: A transaction left open for hours, due to a hung connection or an unclosed cursor, prevents VACUUM from freeing old versions: the table and its indexes grow out of control even if you delete rows every day.

Third mistake: not catching the serialization error. If your code assumes a COMMIT always succeeds and doesn’t handle SerializationFailure (Postgres) or Deadlock found (MySQL), the application will return intermittent 500 errors under real load instead of retrying automatically.

Comparison with Alternatives: MVCC vs. Pessimistic Locking

Two-phase locking (2PL) is still the right choice when conflicts are frequent and detecting them late is expensive: if two transactions are almost always going to collide over the same row, waiting on a lock can be cheaper than letting both proceed and aborting one at the end. MVCC shines in the opposite case: workloads with lots of reads and few real conflicts, where blocking a reader because of a writer that doesn’t even touch the same row would waste concurrency.

Optimistic concurrency control (OCC), used for example by some ORMs with version columns (version int), is a manual variant of the same principle: instead of the engine managing versions internally, the application compares a version number before writing and rejects the UPDATE if it changed since it was read.

Going Deeper: Vacuum, Snapshots, and SSI

Postgres’s autovacuum doesn’t delete old versions the moment nobody’s using them: it waits until no active transaction in the system could still need them. That boundary is called the xmin horizon: the oldest active transaction in the system. As long as a single transaction stays open with an old snapshot, every version created after that point stays retained, no matter how many new transactions have passed since.

Serializable Snapshot Isolation (SSI), the algorithm Postgres uses for the Serializable level, doesn’t lock anything up front. It lets transactions run in parallel over independent snapshots and, at COMMIT time, checks whether there’s a pattern of circular dependencies between reads and writes that would make it impossible to order those transactions serially. If it finds one, it aborts whichever transaction committed later, with the serialization error seen in the Python example.

When bloat becomes a real problem, the first lever to pull is autovacuum_vacuum_scale_factor: the percentage of dead rows that triggers an automatic vacuum (10% by default). Lowering it on write-heavy tables makes autovacuum run more often, in smaller batches, instead of piling up millions of dead versions and paying for one giant vacuum all at once.

stateDiagram-v2
[*] --> Alive
Alive --> Dead: UPDATE or DELETE creates a new version
Dead --> Reclaimed: VACUUM frees the space
Reclaimed --> [*]

📖 Summary on Telegram: View summary

Your next step: spin up the Postgres container from the “Getting Started” section, open two psql sessions, and reproduce the write skew example with Repeatable Read first and Serializable after, to see the difference live.

Frequently Asked Questions

Does MVCC completely replace locks?

No. MVCC avoids locks between readers and writers, but two writers modifying the same row at the same time do compete for a row lock; one waits for the other to finish its transaction.

Why does my Postgres table take up more space if I never delete rows?

Every UPDATE creates a new row instead of modifying the existing one. If autovacuum can’t keep up cleaning old versions (for example, because of a long open transaction), the space builds up as bloat.

Does MySQL/InnoDB use MVCC the same way as PostgreSQL?

The goal is the same, the implementation isn’t: InnoDB modifies the row in place and stores previous versions in a separate undo log, while Postgres keeps all versions inside the table itself.

Does SQLite have MVCC?

In WAL mode, it comes close: writers append new pages to a log without touching the main file, so readers can keep seeing a consistent version of the file while a write is in progress.

When is it worth using Serializable if it’s slower?

When the cost of a concurrency anomaly (like the write skew from the on-call doctors example) is higher than the cost of retrying an aborted transaction: money transfers, inventory reservations, shift assignments.

What’s the difference between snapshot isolation and true serializability?

Snapshot isolation (what Repeatable Read provides in Postgres) guarantees that each transaction sees a consistent snapshot, but allows anomalies like write skew. True serializability guarantees that the final result is equivalent to running all transactions one after another, with no overlap; it’s a stronger guarantee, and a more expensive one to sustain under load.

References

• Official PostgreSQL documentation on concurrency control: explains xmin, xmax, and the supported isolation levels.
• InnoDB documentation on multi-versioning: details the undo log and how InnoDB reconstructs previous versions.
• Official SQLite documentation on WAL mode: describes how Write-Ahead Logging mode separates readers from writers.
• Wikipedia: Multiversion concurrency control: a general overview of the concept and its history across different engines.

📱 Enjoying this content? Follow @programacion on Telegram for daily tech content in Spanish: quick summaries, fresh content every day.

Imagen destacada: Foto de Pankaj Patel en Unsplash