intermediate~4h

Isolation Levels — Dirty Read, Phantom Read, Non-Repeatable Read

Transaction isolation levels control how much a transaction is isolated from the effects of other concurrent transactions. There is a fundamental trade-off: higher isolation = fewer anomalies + more l

ACID's "Isolation" guarantee isn't one fixed behavior — it's a tunable spectrum, and choosing the wrong level for a given operation either lets subtle concurrency bugs through (too weak) or tanks throughput under load for no real benefit (too strict). You need to understand the anomalies before the isolation levels that prevent them make any sense.

Multiple transactions running concurrently against the same rows can interfere with each other in specific, well-known ways — reading another transaction's uncommitted change, getting different answers to the same query twice within one transaction, or seeing rows appear/disappear between two identical queries. Isolation levels exist specifically to control which of these anomalies are allowed.

Dirty read — reading another transaction's uncommitted (and possibly soon-to-be-rolled-back) changes. Non-repeatable read — re-reading the same row twice in one transaction and getting a different value, because another transaction committed a change in between. Phantom read — re-running the same filtered query twice in one transaction and getting a different set of rows, because another transaction inserted/deleted matching rows in between.

The four SQL standard isolation levels

READ UNCOMMITTED — can read uncommitted changes from other transactions (dirty reads allowed).
READ COMMITTED — only reads committed data. (PostgreSQL default). Prevents dirty reads.
REPEATABLE READ — same query returns same results within a transaction. Prevents dirty + non-repeatable reads.
SERIALIZABLE — transactions execute as if they ran one after another. Prevents all anomalies.

Anomalies prevented at each level

Dirty Read: reading uncommitted changes. Prevented by: READ COMMITTED and above.
Non-Repeatable Read: same row returns different values in same transaction. Prevented by: REPEATABLE READ and above.
Phantom Read: same query returns different set of rows (new rows added/deleted). Prevented by: SERIALIZABLE.
Lost Update: two transactions read then update same row; one overwrites the other. Prevented by: proper locking or SERIALIZABLE.

PostgreSQL implementation note: PostgreSQL does NOT implement READ UNCOMMITTED (treated as READ COMMITTED). PostgreSQL's REPEATABLE READ prevents phantom reads (uses MVCC snapshots) — stronger than the SQL standard.

PostgreSQL Isolation via MVCC (Multi-Version Concurrency Control):

READ COMMITTED (default)

Each statement gets a fresh snapshot of committed data.
Between two statements in same transaction, newly committed rows become visible.
No blocking on reads (reads don't take locks in PostgreSQL).

REPEATABLE READ

Entire transaction gets ONE snapshot at the start.
All reads throughout the transaction see the same data (snapshot from transaction start).
Phantom reads prevented: new rows committed by others are invisible (snapshot).
Write conflicts: if another transaction modifies a row you read, SERIALIZABLE failure on COMMIT.

SERIALIZABLE (SSI - Serializable Snapshot Isolation)

PostgreSQL uses SSI: tracks read/write dependencies between transactions.
Detects "dangerous structures" (cycles in dependency graph).
If detected: one transaction gets serialization failure (retryable error).
No false positives (unlike 2PL): only aborts when actual conflict exists.

Step 1: Understand your application's concurrency requirements.

Step 2: Choose the lowest isolation level that prevents your specific anomaly.

Step 3: Use READ COMMITTED for most OLTP (default in PostgreSQL and Oracle).

Step 4: Use REPEATABLE READ for complex reports that must see consistent data.

Step 5: Use SERIALIZABLE for financial operations requiring absolute consistency.

Step 6: Handle serialization failures with retry logic in your application.

Step 7: Monitor lock contention: pg_stat_activity, pg_locks.

Use READ COMMITTED for most OLTP applications — balances safety and performance.
Use REPEATABLE READ for complex reports — consistent snapshot across all queries in the report.
Use SERIALIZABLE for financial transactions requiring absolute consistency (bank transfers, stock trades).
Always implement retry logic for SERIALIZABLE transactions — serialization failures are expected and retryable.
Keep high-isolation transactions short — they hold version information and can cause conflicts.
Test with simulated concurrent load — isolation issues only appear under concurrent access.
Document the isolation level requirements per operation type — not all queries need highest isolation.
Set lock_timeout to avoid indefinite waiting: SET lock_timeout = '5s'.
Monitor pg_stat_activity for transactions with state 'idle in transaction'.
Consider SELECT ... FOR UPDATE for explicit row-level locking within a transaction.

Using SERIALIZABLE for all transactions — unnecessary overhead; most operations are safe at lower levels.
Not implementing retry logic for SERIALIZABLE — serialization failures are normal; app must handle them.
Assuming READ COMMITTED prevents all anomalies — non-repeatable reads and phantoms still occur.
Long transactions at high isolation — causes lock buildup and version bloat.
Not understanding PostgreSQL's stronger REPEATABLE READ — PostgreSQL REPEATABLE READ prevents phantoms (stronger than SQL standard).
Not testing concurrent scenarios — isolation bugs only manifest under concurrent load.
Confusing isolation level with locking — PostgreSQL MVCC allows concurrent reads at any isolation level without blocking.

READ COMMITTED is fastest in PostgreSQL — MVCC means reads never block writes.
SERIALIZABLE has overhead: SSI tracks dependencies in memory (predicate locks).
Under high write concurrency: SERIALIZABLE transactions may abort frequently requiring retries.
pg_stat_activity: monitor for long transactions at high isolation levels.
Use explicit locking (SELECT FOR UPDATE) when you need row-level locking at READ COMMITTED level.
Retry with exponential backoff: 100ms, 200ms, 400ms for serialization failures.

Choosing too weak an isolation level (Read Uncommitted, rarely used in practice) for a security-sensitive operation — like checking an account balance before allowing a withdrawal — can let a transaction act on data that never actually became permanent, a real correctness and, in financial contexts, security problem.

Track serialization-failure rates (could not serialize access errors under Serializable isolation) — a rising rate signals genuine contention on specific rows and usually means the isolation level or the application's retry strategy needs attention, not that something is broken.

Default to READ COMMITTED for all application connections.
Set SERIALIZABLE at transaction level only when needed (not as connection default).
Monitor serialization failures: SHOW pg_stat_bgwriter; track serialization_failures counter.
alert on high serialization failure rates — may indicate design issue or high contention.
For point-in-time consistent reporting: consider REPEATABLE READ on replica (no impact on primary).
Test isolation behavior with pgbench -T (TPC-B test) for benchmarking.

Demonstrate a non-repeatable read: Open two psql sessions. Session A: BEGIN; SELECT balance FROM accounts WHERE account_id=1. Session B: UPDATE accounts SET balance=9000 WHERE account_id=1; COMMIT. Session A: SELECT balance again. Observe the different values. Then repeat at REPEATABLE READ level.
Demonstrate a serialization conflict: Two sessions both BEGIN SERIALIZABLE; both SELECT SUM(balance) FROM accounts; both try UPDATE. One will get a serialization failure. Implement a retry loop.
Measure performance overhead: run a concurrent benchmark at READ COMMITTED vs SERIALIZABLE. Use pgbench or create your own concurrent load. Measure transaction throughput and serialization failure rate.
Test phantom reads in PostgreSQL at REPEATABLE READ: Open Session A at REPEATABLE READ; SELECT COUNT() FROM employees. Session B inserts a new employee and commits. Session A SELECT COUNT() again. Verify PostgreSQL prevents phantom even at REPEATABLE READ.

Four levels: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ < SERIALIZABLE.
Dirty read: reading uncommitted data (prevented at READ COMMITTED+).
Non-repeatable read: same row, different values in same transaction (prevented at REPEATABLE READ+).
Phantom read: different set of rows from same query (prevented at SERIALIZABLE; PostgreSQL RR also prevents).
PostgreSQL default: READ COMMITTED. Most OLTP use cases.
SERIALIZABLE: absolute consistency; requires retry logic for serialization failures.
MVCC: PostgreSQL reads never block writes — no lock contention for reads at any isolation level.

Want a visual for this concept?

Generate a diagram tailored to “Isolation Levels — Dirty Read, Phantom Read, Non-Repeatable Read” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Locking — Optimistic vs Pessimistic →← Back to all SQL chapters