PostgreSQL MVCC vs MySQL Key-Next Locking: How Transaction Isolation Affects Concurrency

When developers talk about ACID databases, two giants dominate: PostgreSQL and MySQL (InnoDB). Both claim full MVCC (Multi-Version Concurrency Control) support, but their implementations are fundamentally different.

This post explains the key differences, how transaction isolation levels behave in each, and why PostgreSQL is truly non-blocking for readers while MySQL often isn't.

TL;DR Summary

Feature	PostgreSQL	MySQL InnoDB
MVCC Implementation	Versioned tuples (true MVCC)	Undo logs + next-key locking (hybrid)
Readers block writers?	No	Yes (in REPEATABLE READ)
Writers block readers?	No	Yes (in REPEATABLE READ)
Default isolation level	READ COMMITTED	REPEATABLE READ
Non-blocking READ COMMITTED	Yes	No (uses semi-consistent reads)
Phantom reads in default mode	Possible (READ COMMITTED)	Prevented (REPEATABLE READ + gap locks)
Lock escalation / gap locks	No	Yes (notorious source of deadlocks)

1. PostgreSQL's True MVCC: How It Achieves Non-Blocking Reads & Writes

PostgreSQL is famous for its rock-solid ACID compliance and incredible concurrency. The secret sauce? True Multi-Version Concurrency Control (MVCC) — an implementation that lets readers and writers fly past each other without ever blocking.

Let’s dive deep into how it actually works under the hood.

What "True MVCC" Really Means

Every time a row is UPDATE or DELETE, PostgreSQL does not overwrite the old row. Instead:

• A new version (tuple) of the row is created.
• Old versions get metadata:
  • xmin → transaction ID that created this version
  • xmax → transaction ID that deleted/updated it (or still active)
• Each transaction gets a snapshot (via the transaction ID snapshot) that determines which row versions are visible to it.
• Dead tuples are cleaned up later by VACUUM.

Result: No read locks are ever needed.

-- This SELECT will NEVER block, even if another transaction is updating the row right now
SELECT * FROM orders WHERE customer_id = 123;

Visibility Rules — The Heart of PostgreSQL MVCC

Condition	Visible to my transaction?
Row's xmin committed and ≤ my snapshot	Yes
Row's xmax committed and > my snapshot	No (deleted/updated after I started)
Row's xmax is still active (in progress)	No (not yet committed)

How Isolation Levels Change Snapshot Behavior

Level	Snapshot Taken	Non-Repeatable Reads?	Phantom Reads?	Blocking?
READ COMMITTED	New snapshot per statement	Possible	Possible	Never
REPEATABLE READ	Snapshot at first statement of tx	No	Possible	Never
SERIALIZABLE	Snapshot at tx start + SSI (predicate checks)	No	No	Never (aborts instead of deadlocks)

Yes — even in SERIALIZABLE, PostgreSQL never blocks; it aborts one transaction if a conflict is detected.

Real Example: Zero Blocking

-- Session 1 (long-running report)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM users WHERE age > 30;  -- runs for 20 seconds

-- Session 2 (concurrent write)
UPDATE users SET age = 35 WHERE id = 999;  -- finishes instantly!

-- Session 1 continues happily with its old snapshot
SELECT COUNT(*) FROM users WHERE age > 30;  -- still sees pre-update data
COMMIT;

No waiting. No deadlocks. Pure magic.

Benifits and Trade-offs

• Benefits:
  • Readers never block writers
  • Writers never block readers
  • Almost zero deadlocks (only from explicit FOR UPDATE/LOCK TABLE)
  • Great for analytics + OLTP mixed workloads
  • VACUUM and autovacuum keep bloat under control
• Trade-offs:
  • Table bloat if VACUUM lags (solved with good autovacuum tuning)
  • Slightly higher write amplification (new tuple per update)

2. MySQL InnoDB: MVCC + Next-Key Locking (The Hidden Truth)

MySQL InnoDB’s MVCC Is Not What You Think: The Truth About Next-Key Locks

Everyone says "InnoDB has MVCC". That’s technically true — but it’s not the same as PostgreSQL’s non-blocking MVCC.

InnoDB combines MVCC with row locks and gap/next-key locks, and the default isolation level (REPEATABLE READ) turns innocent SELECTs into blocking operations.

Let’s uncover what really happens.

InnoDB’s Hybrid Approach

• Old row versions are stored in undo logs
• Each transaction gets a read view (similar to a snapshot)
• But… to prevent phantom reads in REPEATABLE READ, InnoDB uses next-key locking:
  • Record lock (on existing row)
  • Gap lock (on the "gap" between index entries)

This means: plain SELECTs can acquire locks and block INSERTs/UPDATEs.

The Infamous Gap Lock Example

-- Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;  -- scans index on age

-- This query places GAP LOCKS on age ranges (...,20], (20,25], (25,30], (30,...) 

-- Session 2 (gets BLOCKED!)
INSERT INTO users (name, age) VALUES ('Alice', 27);
-- Waits... forever... until Session 1 commits/rollbacks

A harmless SELECT just blocked an INSERT on a non-existing row!

How Isolation Levels Really Behave in InnoDB

Level	Snapshot?	Gap Locks?	Readers Block Writers?	Writers Block Readers?	Deadlocks Common?
READ UNCOMMITTED	No	No	No	Yes	Rare
READ COMMITTED	Partial	No	No	Yes	Less
REPEATABLE READ (default)	Yes	Yes	Yes	Yes	Very common
SERIALIZABLE	Yes	Yes + stricter	Yes	Yes	Extremely common

Yes — in the default REPEATABLE READ, reads block writes and writes block reads.

Why InnoDB Chose This Design

Goal: Prevent phantom reads without predicate locking (like PostgreSQL’s SSI).
Solution: Lock the gaps in indexes → no phantoms, but at the cost of blocking.

Real-World Pain Points

• A background analytics query can freeze your entire app
• Random deadlocks at high concurrency
• Hard to debug: SHOW ENGINE INNODB STATUS shows giant lock wait chains
• Scaling writes often requires partitioning or read replicas

When InnoDB’s Approach Is Acceptable

• Low-to-medium concurrency
• Mostly primary key lookups (no range scans → fewer gap locks)
• You’re already deep in the MySQL ecosystem

How to Reduce Pain (if you’re stuck with MySQL)

-- Use READ COMMITTED + row-based replication
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Avoid SELECT ... FOR UPDATE/SHARE unless necessary
-- Use primary key lookups instead of range scans when possible
-- Consider MariaDB (its default is often less aggressive)

3. Conclusion

InnoDB’s "MVCC" is a hybrid of versioning + locking.
It gives you strong consistency (no phantoms by default) but sacrifices true non-blocking concurrency.
If you ever wondered why your MySQL app randomly deadlocks at scale even though "you’re just reading" — now you know: your reads are silently locking gaps.
PostgreSQL proved decades ago that you can have both MVCC and no blocking. MySQL chose a different trade-off.
Choose your database accordingly.