10 · Isolation Levels — Control Concurrent Transaction Visibility

Scaling Writes · Topic 10 of 10

---

Why Isolation Levels?

Concurrent transactions can interfere. Isolation levels are a knob between performance (low isolation, fewer locks) and correctness (high isolation, more locks).

---

Concurrency Anomalies

Anomaly	Description
Dirty Read	Reading uncommitted data from another transaction
Non-Repeatable Read	Same row re-read returns different value (updated by another tx)
Phantom Read	Re-executing a range query returns different rows (inserted by another tx)
Lost Update	Two transactions read-then-write; one overwrites the other's change

---

Standard Isolation Levels (SQL Standard)

Level	Dirty Read	Non-Repeatable	Phantom
Read Uncommitted	✅ Possible	✅ Possible	✅ Possible
Read Committed	❌ Prevented	✅ Possible	✅ Possible
Repeatable Read	❌ Prevented	❌ Prevented	✅ Possible
Serializable	❌ Prevented	❌ Prevented	❌ Prevented

---

PostgreSQL Implementation

PostgreSQL uses MVCC (Multi-Version Concurrency Control) — readers don't block writers.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

• Default: Read Committed
• Repeatable Read in Postgres also prevents phantoms (snapshot-based)
• Serializable Snapshot Isolation (SSI) — true serializability without locking

---

Cloud Implementations

DB	Default Isolation	Max Isolation	Notes
PostgreSQL	Read Committed	Serializable (SSI)	MVCC-based
Spanner	Serializable	External Consistency	Strongest: globally serializable
DynamoDB	Read Committed	Serializable (Transactions API)	Transactions needed for serializable
Cassandra	None (row-level)	Linearizable (LWT for single key)	No multi-row transactions
MongoDB	Read Uncommitted	Snapshot (multi-doc transactions)	Default reads are non-isolated

---

Practical Advice

Use the lowest isolation level that prevents the bugs you care about

Serializable at scale requires careful tuning. Most applications work fine with Read Committed + optimistic locking for specific hot rows.

-- Optimistic locking pattern
UPDATE orders SET status = 'shipped', version = version + 1
WHERE order_id = 42 AND version = 5;
-- Check rows affected = 1, else retry

---