Scaling Beyond Locks: A Practical Guide to MVCC in PostgreSQL and MySQL

Table of Contents

The Database Locking Nightmare

Picture a flash sale with 10,000 concurrent users hitting your checkout button. Thousands of shoppers are checking stock while your inventory service frantically updates quantities. In older database architectures, the only way to keep data consistent was through heavy-duty locking. If Transaction A was reading a row, Transaction B simply had to wait. We call this Pessimistic Locking. It works, but it scales like a lead balloon.

I once inherited a legacy system where row-level locks were the norm. As traffic spiked, latency jumped from 50ms to 5 seconds. The database wasn’t running out of CPU or RAM; it was just idling, stuck waiting for locks to release. Multi-Version Concurrency Control (MVCC) solves this. After implementing MVCC-based strategies across PostgreSQL and MySQL on high-scale projects, I’ve found that understanding their internal differences is the secret to building systems that stay fast under pressure.

The Core Conflict: Readers vs. Writers

Database concurrency fails when readers and writers trip over each other. To achieve high performance, we need a system where:

Multiple users can read the same data simultaneously.
Readers never block writers.
Writers never block readers.

Traditional locking treats data as a single, static snapshot. If you change a value, you have to hide it from everyone else until you’re done. MVCC takes a different path. It allows multiple versions of the same row to exist at once. Instead of overwriting data, the database creates a new version. Every transaction sees a private “snapshot” of the data as it existed the moment the query started.

PostgreSQL vs. MySQL: Two Paths to the Same Goal

While both engines use MVCC, their internal mechanics are worlds apart. Choosing the right one—or tuning the one you have—requires knowing what’s happening under the hood.

PostgreSQL: The Version-in-Table Approach

PostgreSQL stores every version of a row directly in the main data files. Each row carries hidden metadata, specifically xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it).

-- Inspecting hidden MVCC metadata in Postgres
SELECT ctid, xmin, xmax, * FROM users WHERE id = 1;

When you update a row, Postgres doesn’t touch the old data. It marks the old version as “expired” using xmax and inserts a completely new row. This makes writes incredibly fast. However, it creates “bloat.” I’ve seen 1GB tables swell to 5GB because the VACUUM process couldn’t keep up with dead tuples. Without regular cleanup, your performance will eventually crater.

MySQL (InnoDB): The Undo Log Approach

InnoDB takes the opposite approach. It keeps only the latest version of a row in the main table. To provide MVCC, it moves the old data to a separate structure called the Undo Log. Each row contains a pointer to its previous state stored in that log.

If a transaction needs an older version of the data, InnoDB reconstructs it on the fly using those undo records. This prevents the table bloat seen in Postgres. The trade-off? Long-running transactions can cause the Undo Log to explode in size, slowing down the entire system as it traverses long version chains.

Isolation Levels: Where Data Goes Wrong

MVCC isn’t a silver bullet. Your Transaction Isolation Level determines which anomalies you’ll face. Two specific issues—Phantom Reads and Write Skew—frequently catch developers off guard.

1. The Phantom Read

A Phantom Read happens when a transaction runs the same query twice but finds new rows the second time because another user inserted data. In MySQL, the default REPEATABLE READ uses Gap Locking to lock the “spaces” between rows, preventing these phantoms. PostgreSQL’s REPEATABLE READ is even stricter; it simply throws an error if it detects that the data snapshot has changed, forcing the application to handle the conflict.

2. Write Skew: The Silent Killer

Write Skew is a subtle bug that slips through even at REPEATABLE READ. It occurs when two transactions read the same data, make a logic-based decision, and then update different rows that invalidate each other’s premise.

The Doctor On-Call Scenario:
A hospital requires at least one doctor on duty. Alice and Bob are both on call. They both try to clock out at the exact same time.

-- Transaction 1 (Alice)
SELECT count(*) FROM doctors WHERE on_call = true; -- Returns 2
UPDATE doctors SET on_call = false WHERE name = 'Alice'; -- Allowed

-- Transaction 2 (Bob)
SELECT count(*) FROM doctors WHERE on_call = true; -- Returns 2 (due to MVCC snapshot)
UPDATE doctors SET on_call = false WHERE name = 'Bob'; -- Allowed

-- Result: 0 doctors are on call. The system failed.

Since they updated different rows, no lock was triggered. MVCC successfully kept the database running, but it allowed a business logic violation.

Strategies for Production Success

Don’t just set everything to SERIALIZABLE. It’s the safest level, but it can kill throughput by forcing transactions to run almost sequentially. Here is a better way to handle high-concurrency logic.

Use Explicit Locking for Critical Paths

When Write Skew is a risk, use SELECT ... FOR UPDATE. This forces the database to lock the rows you are reading, making other transactions wait until you finish.

-- Manually stopping Write Skew
BEGIN;
SELECT count(*) FROM doctors WHERE on_call = true FOR UPDATE;
-- Alice now holds the lock. Bob's query will wait here.
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

PostgreSQL’s SSI Advantage

PostgreSQL offers a feature called Serializable Snapshot Isolation (SSI). Unlike traditional serializable levels that lock entire tables, SSI tracks dependencies. If it spots a potential Write Skew, it kills one transaction and returns a serialization error. Your code must be ready to catch this error and retry the operation immediately.

MySQL Strategy: Version Tracking

For MySQL, I often prefer Optimistic Concurrency Control (OCC). Instead of relying on heavy database locks, add a version column to your table. It’s lightweight and works perfectly for web applications.

-- Optimistic locking in the application layer
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 101 AND version = 12; -- 12 is the version we fetched earlier

If the update affects 0 rows, someone else beat you to it. Your application should then fetch the new data and try again.

Final Checklist

Stick to READ COMMITTED: This is the best default for 90% of use cases. It offers high performance and prevents dirty reads.
Watch the Bloat (Postgres): Monitor pg_stat_all_tables. If your dead tuple count is rising, your autovacuum settings need tuning.
Kill Long Transactions (MySQL): Monitor your History List Length. Transactions that stay open for hours will bloat your Undo Log and degrade performance.
Build Retry Logic: If you use high isolation levels, retries aren’t optional. They are a core part of your application’s reliability.

MVCC is the engine that allows modern databases to scale to millions of rows. By understanding how versions are stored and where the logic can fail, you can build data layers that are both lightning-fast and perfectly consistent.