Designing Data-Intensive Applications
Ch. 7

Weak Isolation Levels

Most databases default to weak isolation for performance — accepting anomalies most apps never notice.

Serial execution of transactions is safe but slow. Weak isolation levels allow concurrency by permitting certain anomalies. Understanding which anomalies your application can tolerate is essential.

Diagram
In practice

PostgreSQL's default READ COMMITTED is fine for most web apps. Inventory systems prone to write skew might need SERIALIZABLE or explicit row locks (SELECT FOR UPDATE). MySQL InnoDB uses REPEATABLE READ with next-key locking to reduce phantoms — behavior differs from PostgreSQL, so isolation level names are not portable across databases.

Airbnb at scale

Double-booking the same listing date is a write-skew anomaly — two guests read available=true concurrently and both commit. Inventory systems need SERIALIZABLE or SELECT FOR UPDATE, not default READ COMMITTED.

typescript — Choosing an isolation level
// PostgreSQL isolation — pick the weakest level that is safe
await db.query("BEGIN ISOLATION LEVEL READ COMMITTED");
const row = await db.query("SELECT stock FROM inventory WHERE sku = $1", [sku]);
if (row.stock < qty) throw new Error("insufficient stock");
await db.query("UPDATE inventory SET stock = stock - $1 WHERE sku = $2", [qty, sku]);
await db.query("COMMIT");
// SERIALIZABLE or SELECT FOR UPDATE needed to prevent write skew
Key Takeaways
  • Read committed: no dirty reads; each query sees only committed data.
  • Snapshot isolation: each transaction sees a consistent point-in-time snapshot.
  • Repeatable read prevents non-repeatable reads within a transaction.
  • Write skew: two transactions read overlapping data and write disjoint rows.
  • Phantoms: new rows appear in a re-executed range query.
  • PostgreSQL defaults to READ COMMITTED; SERIALIZABLE uses SSI for stronger guarantees.
read committedPostgreSQLsnapshot isolationwrite skewphantom read
← The Slippery Concept of a TransactionSerializability →