Skip to content
←Data & Databases

// Data & Databases · 03

Isolation Levels

Concurrent writers collideleads toPick which anomalies to allow

How much concurrent transactions may see of each other.

How it fits together

Isolation Levels structureREAD COMMITTED: overbooksFix: SELECT ... FOR UPDATE«client»T1reads seats = 1then inserts booking«client»T2reads seats = 1then inserts bookingSeats rowseats_left = 1Bookings tablebooking Abooking B (oversold)«client»T1SELECT ... FOR UPDATEbooks, then COMMIT«client»T2blocked until T1 COMMITthen re-reads 0does not bookSeats rowlocked by T1seats_left: 1 to 0Bookings tablebooking A only1 read2 read3 insert4 insert1 lock + read2 waits on lock3 insert4 re-read = 0
uses / callscreatesfails / error path· numbered steps replay in order
  • T1 uses / calls Seats row (1 read)
  • T2 uses / calls Seats row (2 read)
  • T1 creates Bookings table (3 insert)
  • T2 creates Bookings table (4 insert)
  • T1 uses / calls Seats row (1 lock + read)
  • T1 fails / error path T2 (2 waits on lock)
  • T1 creates Bookings table (3 insert)
  • T2 uses / calls Seats row (4 re-read = 0)

Spot it

  • Two requests both book the last seat
  • Same query, same transaction, different rows
  • Readers and writers block each other under load

It costs

Stronger isolation means more blocking or retries; weaker means more anomalies.

In code

BEGIN;​SELECT seats_leftFROM eventsWHERE id = @event_idFOR UPDATE;​-- seats_left < 1: ROLLBACK and report sold out​INSERT INTO bookings (event_id, customer_id)VALUES (@event_id, @customer_id);​UPDATE eventsSET seats_left = seats_left - 1WHERE id = @event_id;​COMMIT;
Go deeperThe full explanation and what to ask for · 1 min readExpand

The SQL standard defines levels by the anomalies they forbid. READ UNCOMMITTED allows dirty reads; READ COMMITTED stops those but allows non-repeatable reads; REPEATABLE READ stops those but may allow phantoms; SERIALIZABLE behaves as if transactions ran one at a time. Snapshot isolation sits apart: no dirty, non-repeatable or phantom reads, but write skew is still possible.

Engines differ. PostgreSQL defaults to READ COMMITTED on MVCC snapshots, its REPEATABLE READ is snapshot isolation, and its SERIALIZABLE aborts conflicting transactions, so callers must retry. SQL Server also defaults to READ COMMITTED, but with locks, so readers and writers block each other unless READ_COMMITTED_SNAPSHOT is on.

The classic mistake is READ UNCOMMITTED or NOLOCK to "fix" deadlocks: it trades blocking for dirty reads and, in SQL Server, rows read twice or skipped. Fix the cause: consistent lock order, shorter transactions, READ_COMMITTED_SNAPSHOT for reader-writer blocking. For check-then-write races, use SERIALIZABLE with retries, row locks or optimistic concurrency.

Ask for it

  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • snapshot isolation / MVCC
  • phantom read
  • write skew

Two concurrent requests can both pass the 'seats left > 0' check and overbook. In your database, name the anomaly at the current isolation level, then compare two fixes: SERIALIZABLE with retry on serialization failure, and SELECT ... FOR UPDATE on the event row.

Connects to