🎚️Isolation levels

Concurrency is a dial, not a switch

Lower isolation lets more transactions overlap, higher throughput, more anomalies. SERIALIZABLE gives the strongest illusion of running alone, but you pay in retries, deadlocks, and latency variance (watch those p99 tails).

📊Anomaly matrix (ANSI picture)

Engines add nuance, treat this table as the baseline story, then read vendor notes.

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible*	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Maybe**
SERIALIZABLE	No	No	No

⚡ Advanced / dialect note

*PostgreSQL treats READ UNCOMMITTED like READ COMMITTED. **InnoDB REPEATABLE READ uses next-key locking to shrink many phantom scenarios, verify semantics before betting the farm on RR.

Isolation mismatch

Assuming READ COMMITTED prevents all lost updates without explicit locking or optimistic versioning.
Long analytics on the primary under SERIALIZABLE defaults, blocking OLTP and widening p99.
ORM-managed scopes fighting hand-written SQL in the same request, nested or leaked transactions.

Explicit levels and observability

Map invariants to anomaly classes; set isolation per transaction where engines allow.
Budget retries for serialization failures; send heavy reads to replicas with known staleness.
Watch lock wait time and transaction age, not only CPU.

👥MVCC: The Engine Under the Dial

How PostgreSQL and InnoDB provide isolation without heavy locking.

XMIN, XMAX, and Snapshots

In MVCC, every row has invisible headers: XMIN (ID of the transaction that created the row) and XMAX (ID of the transaction that deleted/updated it). When you start a query, you get a Snapshot of all active Transaction IDs. You can only see a row if its XMIN is committed and XMAX is either 0 or uncommitted.

Row Visibility = (XMIN < MySnapshot) AND (XMAX == 0 OR XMAX > MySnapshot)

Reads Never Block Writes

Because a reader sees a "Snapshot" of old versions, a writer can concurrently update the table by creating a new version. This is why MVCC scales better than lock-based engines.

Write Skew

A REPEATABLE READ anomaly: two transactions read state A, satisfy a condition, and both update different rows that together break the business logic. Only SERIALIZABLE detects this.

The MVCC Tax: VACUUM

Since updates create new versions, the physical file accumulates "Dead Tuples." A background process (VACUUM) must eventually reclaim this space. If you have long-running transactions, they "pin" old versions, preventing VACUUM and causing Table Bloat.

SQL

Set isolation per transaction (PostgreSQL)

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM wallets WHERE user_id = 9;
COMMIT;

✓Illustrative result set

balance
150

Operator mindset

When users report “ghost numbers that change on refresh,” capture pg_stat_activity, transaction age, and isolation level, half the time it is expected RC behavior, not corruption.

TL;DR

Isolation levels trade concurrency for anomaly prevention: READ UNCOMMITTED (dirty reads), READ COMMITTED (default in many engines, no dirty reads), REPEATABLE READ, and SERIALIZABLE (strongest). MVCC engines implement levels differently than lock-based textbooks, verify behavior for phantoms and serialization failures on your database. Pick the weakest level that still matches product guarantees.