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.
- Default:
Read Committed Repeatable Readin Postgres also prevents phantoms (snapshot-based)Serializable Snapshot Isolation (SSI)detects dangerous dependency patterns and may abort transactions; ordinary write locks still exist, while predicate locks used for SSI detection do not block
Cloud Implementations¶
| DB | Default Isolation | Max Isolation | Notes |
|---|---|---|---|
| PostgreSQL | Read Committed | Serializable (SSI) | MVCC-based |
| Spanner | Serializable | External consistency for serializable transactions | Repeatable read is also available; choose deliberately |
| 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
Write Skew: Why a Stable Snapshot Is Not Enough¶
Two doctors are on call. Each transaction reads both doctors, sees the other available and marks its own doctor off call. Because they update different rows, both can commit under snapshot isolation, leaving nobody on call. A stable snapshot prevents changing reads, not every invalid concurrent outcome.
Protect the invariant with serializable transactions (and whole-transaction retry), or serialize decisions by locking a shared row representing the rota. A version predicate on each doctor's separate row does not detect this cross-row dependency.
Serializable means equivalent to some serial execution. Strict serializability additionally respects real-time ordering. Linearizability is usually discussed for individual operations. These terms are related but not interchangeable.
MongoDB read concern, write concern and read preference are distinct controls; its read semantics should not be inferred from a SQL isolation label. Likewise, MVCC avoids many reader/writer conflicts but does not eliminate write locks, DDL locks or deadlocks.
Check Your Understanding¶
Sources: PostgreSQL isolation, Spanner isolation levels.