Skip to content

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) 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

#

Question 1

What must a serialization retry include?

#

Question 2

Why is a single conditional decrement safer than read-then-write?

Sources: PostgreSQL isolation, Spanner isolation levels.