beginnerintermediateadvanced

System Design · Isolation Levels and Concurrency

Isolation Levels and Concurrency

What concurrent transactions can see of each other, and the tools that keep them from colliding

Four levelsRead Uncommitted → Serializable
Four anomaliesdirty, non-repeatable, phantom, lost update
Optimistic vs pessimisticverify at write time vs lock upfront
Mechanismsversion column, CAS, unique constraint, row lock

1.The four SQL isolation levels

An isolation level is a rule that decides how much one transaction can see of another transaction's uncommitted or concurrent work. The four standard levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — trade correctness for concurrency: each stricter level rules out more anomalies, at the cost of more locking or more retried transactions.

Key features

Read Uncommitted: transactions can see other transactions' uncommitted changes (dirty reads) — rarely used in practice
Read Committed: only ever sees committed data, but a re-read within the same transaction can see a different value (non-repeatable read) — PostgreSQL's and most databases' default
Repeatable Read: a value read once stays the same for the rest of the transaction, but new rows matching a filter can still appear (phantom reads)
Serializable: transactions behave as if run one at a time in some order — the strongest level, at the highest cost in locking or retries
Each level up removes more anomalies but increases lock contention or the chance a transaction must retry after a conflict

Isolation levels and the anomalies each one prevents

Isolation levels and the anomalies each one prevents
Level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible (varies by database)
Serializable	Prevented	Prevented	Prevented

Non-repeatable read under Read Committed

Txn A

Txn B

accounts

1SELECT balancereads 100
2UPDATE + COMMITbalance = 150
3SELECT balance againreads 150 — non-repeatable read

Command

sql

-- set for one transaction (PostgreSQL / MySQL syntax)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;

Four levels, each removing more anomalies: Read Uncommitted (dirty reads possible) → Read Committed (default in most databases) → Repeatable Read (a value stays stable) → Serializable (behaves like one transaction at a time).

2.Dirty reads, non-repeatable reads, phantom reads and lost updates

These are the four named ways concurrent transactions can produce a wrong or surprising result if isolation is too weak. Each has a precise definition and a specific isolation level that first prevents it — knowing the names is what lets a design conversation say exactly which risk a chosen isolation level does and does not cover.

Key features

Dirty read: transaction A reads data transaction B has written but not yet committed — if B rolls back, A read data that never really existed
Non-repeatable read: transaction A reads the same row twice and gets different values because B committed a change in between
Phantom read: transaction A re-runs the same filtered query and gets a different set of rows because B inserted/deleted a matching row
Lost update: two transactions read the same value, both compute a new value based on it, and the second write silently overwrites the first
Lost updates are not fully solved by isolation level alone in every database — explicit locking or optimistic concurrency control is often still needed

Each anomaly, in one line

Each anomaly, in one line
Anomaly	What goes wrong	First level that prevents it
Dirty read	Reads another transaction's uncommitted write	Read Committed
Non-repeatable read	Same row, two different values, same transaction	Repeatable Read
Phantom read	Same filter, different row set, same transaction	Serializable (standard); Repeatable Read in practice for many databases
Lost update	A write silently overwrites another concurrent write	Needs explicit locking/CAS even under Serializable in some databases

Four named concurrency anomalies

Dirty read

reads uncommitted data

Non-repeatable read

same row, two values

Phantom read

same filter, different rows

Lost update

a write silently overwritten

Command

text

dirty read:          reads uncommitted data
non-repeatable read:  same row, two reads, two values
phantom read:         same filter, two reads, two row sets
lost update:          concurrent write silently discarded

Dirty read (uncommitted data), non-repeatable read (same row, two values), phantom read (same query, different rows), lost update (a write silently overwritten) — four distinct named risks, each with its own fix.

3.Optimistic vs pessimistic concurrency control

Pessimistic concurrency control locks a row before touching it, so no other transaction can write to it until the lock releases. Optimistic concurrency control assumes conflicts are rare — it reads without locking, then checks at write time whether the data changed underneath it, retrying if so. Which one fits depends on how often writes actually collide.

Key features

Pessimistic: acquire a lock (e.g. SELECT ... FOR UPDATE) before reading, blocking other writers until the transaction ends
Optimistic: read without locking, then verify at write time nothing changed (a version check or compare-and-set), retrying on conflict
Pessimistic locking adds contention and can deadlock, but never wastes a completed unit of work
Optimistic control wastes no locks under low contention, but repeated retries under high contention can waste more work than locking would have
The right choice tracks collision frequency: pessimistic for high-contention hot rows, optimistic for low-contention, high-read workloads

Optimistic vs pessimistic concurrency control

Optimistic vs pessimistic concurrency control
Property	Pessimistic	Optimistic
Mechanism	Lock before reading/writing	Read freely, verify before committing
Best for	High-contention data (frequent collisions)	Low-contention data (rare collisions)
Failure mode	Blocking, possible deadlock	Wasted work on retry
Typical implementation	SELECT ... FOR UPDATE, row/table locks	Version column, compare-and-set, unique constraint

Pessimistic vs. optimistic concurrency control

Pessimistic

Lock before reading/writing
Best for high-contention hot rows
Failure mode: blocking, possible deadlock

Optimistic

Read freely, verify before committing
Best for low-contention data
Failure mode: wasted work on retry

Command

sql

-- pessimistic: lock the row for this transaction
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- optimistic: check the version hasn't moved
UPDATE accounts SET balance = 90, version = version + 1
  WHERE id = 1 AND version = 3;
-- 0 rows updated → someone else won the race, retry

Pessimistic: lock first, no wasted work, but blocking/deadlock risk — for hot, high-contention data. Optimistic: check at write time, no locks, but wasted retries under contention — for low-contention, read-heavy data.

4.Version columns, compare-and-set, unique constraints and row locks

These are the four concrete tools a design actually reaches for to implement concurrency control — a version column or compare-and-set for optimistic control, row locks for pessimistic control, and a unique constraint as a lightweight way to enforce "only one of these can exist" without any explicit locking logic at all.

Key features

Version column: an integer/timestamp bumped on every write; a write only succeeds if the version it expected still matches
Compare-and-set (CAS): the same idea generalized — write succeeds only if the current value still equals the expected old value
Unique constraint: the database itself rejects a duplicate — often the simplest way to enforce "exactly one" without any manual locking
Row lock (SELECT ... FOR UPDATE): blocks other transactions from writing the row until the current transaction commits or rolls back
Picking the lightest mechanism that expresses the actual invariant is usually better than reaching for a full lock by default

Four mechanisms and when each fits

Four mechanisms and when each fits
Mechanism	Enforces	Good fit
Version column	"Nobody else changed this since I read it"	Optimistic updates to a single row
Compare-and-set	Same as version column, more general (any expected-value check)	Key-value stores, caches, distributed counters
Unique constraint	"Exactly one row can have this value"	Preventing duplicate signups, idempotency keys
Row lock (FOR UPDATE)	"Nobody else can touch this row until I'm done"	High-contention rows, multi-step read-modify-write

Four concrete concurrency-control mechanisms

Version column

expected version must match

Compare-and-set

generalized value check

Unique constraint

exactly one row can exist

Row lock

blocks others until done

Command

sql

-- unique constraint used as a concurrency-safe guard
CREATE UNIQUE INDEX idx_one_active_session
  ON sessions (user_id) WHERE status = 'active';

-- second concurrent INSERT for the same user_id fails
-- with a constraint violation instead of racing

Version column/CAS for "did this change since I read it," row lock for "block others while I finish," unique constraint for "exactly one of these" — pick the lightest mechanism that matches the actual invariant.

Spotted a mistake on this page? Tell us.

Read Sheet