beginnerintermediateadvanced

System Design · Section 21

Isolation Levels and Concurrency

Level: intermediate
Read: 20 min
Concepts: 4

Isolation levels decide how much of one transaction's in-progress work another concurrent transaction can see — from Read Uncommitted (almost nothing hidden) to Serializable (behaves like one transaction at a time). Four named anomalies — dirty reads, non-repeatable reads, phantom reads, lost updates — describe exactly what weaker levels allow. Concurrent writes to the same data are handled with either pessimistic control (lock first) or optimistic control (verify at write time), implemented concretely with version columns, compare-and-set, unique constraints or row locks.

System Design overview

System Design for Beginners CoursefreeCodeCamp.org

What is true here

Four isolation levels, each stricter than the last: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
Four named anomalies — dirty read, non-repeatable read, phantom read, lost update — each prevented starting at a specific level.
Pessimistic concurrency control locks before writing; optimistic control checks for a conflict at write time and retries.
Version columns, compare-and-set, unique constraints and row locks are the concrete mechanisms behind both strategies.

What you will be able to do

Name which isolation level a workflow actually needs and why
Identify which of the four named anomalies a given bug report describes
Choose optimistic vs pessimistic concurrency control based on expected contention
Pick the lightest concrete mechanism — version column, CAS, unique constraint, or row lock — for a given invariant

Isolation levels and the anomalies they prevent

The four standard SQL isolation levels, and the four named ways concurrency can go wrong at weaker levels.

The four SQL isolation levels

coreintermediate

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.

Think of it as

Isolation levels are like how much privacy a shared kitchen gives simultaneous cooks. Read Uncommitted lets you taste a dish another cook is still stirring, before they decide it needs salt. Read Committed only lets you taste what they've actually plated. Repeatable Read guarantees the dish you tasted once won't have changed if you taste it again mid-meal. Serializable acts as if only one cook were in the kitchen at a time, even though several really are.

sql

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

What we're doing: Show the same query at Read Committed vs Repeatable Read returning different results.

isolation-levels.sqlsql

-- Transaction A (Read Committed)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- reads 100

-- Transaction B, in between, commits a change
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;

-- back in Transaction A, same query again
SELECT balance FROM accounts WHERE id = 1;  -- reads 150
COMMIT;
-- Read Committed: the second read saw B's committed
-- change — a non-repeatable read.

-- Same sequence under REPEATABLE READ instead:
-- Transaction A's second SELECT still returns 100,
-- because its snapshot was fixed at the first read.

2: Transaction A takes its first read under Read Committed.
9: The second read within the same transaction sees a different value — the non-repeatable read Read Committed allows.
13: Repeatable Read would keep returning 100 for the rest of this transaction, from its fixed snapshot.

Why this works: The exact same two queries return different results purely based on isolation level — this is the concrete behavior the four levels differ on, not an abstract distinction.

Assuming the database's default isolation level is Serializable

Wrong

text

"We're using transactions, so concurrent
requests can't interfere with each other."

Better

text

"We're using transactions at Read Committed
(the default) — concurrent requests can still
see non-repeatable reads and phantoms; if that
matters for this workflow, we need Repeatable
Read/Serializable or an explicit lock."

What you see: Two requests reading the same row twice within a transaction get different values, or a filtered query returns different row counts across two reads in the same transaction — surprising behavior for a team that assumed "transaction" alone meant full isolation.

Why: Read Committed is the default in PostgreSQL, Oracle and SQL Server; only MySQL/InnoDB defaults to Repeatable Read. None of them default to Serializable, because it is the most expensive level to run under real concurrency.

Non-repeatable read under Read Committed

Txn A

Txn B

accounts

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

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

Together

text

PostgreSQL's Repeatable Read is stricter than the SQL
standard requires — it uses snapshot isolation and
in practice also blocks phantom reads for most cases,
though it still allows a narrower anomaly class (write
skew) that only true Serializable rules out.

Remember: 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).

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

coreintermediate

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.

Think of it as

Think of four specific ways a shared spreadsheet can go wrong when two people edit at once: seeing a formula mid-edit before it's saved (dirty read), reading a cell twice and getting two different answers (non-repeatable read), running a filter twice and getting a different row count because someone added a row (phantom read), and two people overwriting the same cell where one person's edit is silently discarded (lost update).

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

What we're doing: Show a lost update: two transactions both read a counter, both increment it, and one increment disappears.

lost-update.sqlsql

-- inventory starts at 10

-- Transaction A                    -- Transaction B
BEGIN;                               BEGIN;
SELECT stock FROM items              SELECT stock FROM items
  WHERE id = 1;  -- reads 10           WHERE id = 1;  -- reads 10
-- (application computes 10 - 1 = 9) -- (application computes 10 - 1 = 9)
UPDATE items SET stock = 9           UPDATE items SET stock = 9
  WHERE id = 1;                        WHERE id = 1;  -- blocks until A commits
COMMIT;                              COMMIT;  -- writes 9, not 8

-- Two units were sold, but stock only dropped by 1.
-- One of the two decrements was lost.

5: Both transactions read the same starting value of 10 before either has written anything back.
12: B's write overwrites A's — both computed "9" independently from the same stale read, so one real decrement vanishes.

Why this works: Lost updates are the anomaly most likely to cause a real production bug — inventory counts, balances and vote counts are all vulnerable if concurrent read-modify-write cycles aren't protected explicitly.

Assuming a higher isolation level alone prevents lost updates

Wrong

text

"We're on Serializable, so read-modify-write
races can't happen."

Better

text

"We're on Serializable, which detects this
conflict and forces one transaction to retry
— but a weaker level, or a read-then-write done
as two separate statements instead of one atomic
UPDATE, can still lose an update. Prefer
UPDATE items SET stock = stock - 1, or an
explicit version check."

What you see: A counter or balance drifts lower than the sum of all recorded operations that touched it suggests it should — evidence a concurrent read-modify-write cycle silently dropped one operation.

Why: Serializable does prevent lost updates by forcing a conflicting transaction to abort and retry, but only if the database actually detects the conflict — an application that reads a value, computes in code, and writes it back as a separate statement is exactly the pattern that's safest to avoid regardless of isolation level, by using an atomic single-statement update instead.

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

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

Remember: 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.

Controlling concurrent writes

The two strategic approaches to concurrent writes, and the concrete mechanisms that implement them.

Optimistic vs pessimistic concurrency control

coreintermediate

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.

Think of it as

Pessimistic control is reserving a meeting room before you plan the meeting — nobody else can even try to use it while you hold the reservation. Optimistic control is walking into an open room, planning your meeting, and only checking at the door whether someone else already claimed it — if they did, you go plan again. The optimistic approach wastes no time reserving when conflicts are rare, but wastes real work redoing the plan when they aren't.

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

What we're doing: Show the same balance-update problem solved both ways.

concurrency-control.sqlsql

-- Pessimistic: lock, then write, releasing on commit
BEGIN;
SELECT balance FROM accounts
  WHERE id = 1 FOR UPDATE;  -- other writers now blocked
UPDATE accounts SET balance = balance - 10
  WHERE id = 1;
COMMIT;

-- Optimistic: no lock, verify version at write time
-- 1. Application reads: balance = 100, version = 3
-- 2. Application computes: new balance = 90
UPDATE accounts
  SET balance = 90, version = 4
  WHERE id = 1 AND version = 3;
-- if another transaction already bumped version to 4,
-- this UPDATE matches 0 rows — application detects the
-- conflict and retries from a fresh read.

3: The pessimistic lock is taken up front — every other writer waits here, even ones that would not have conflicted.
13: The optimistic UPDATE only succeeds if the version is still what was read — a mismatch means someone else wrote first.

Why this works: The two approaches solve the identical business problem — do not lose a concurrent balance update — with opposite default assumptions about how often writers collide.

Choosing optimistic concurrency control for a genuinely hot, high-contention row

Wrong

text

"Use optimistic version checks everywhere —
locking is old-fashioned."

Better

text

"Use optimistic checks for typically low-
contention rows (most user profiles, most
orders). For a genuinely hot row — a single
popular item's stock count under a flash sale —
prefer pessimistic locking or a sharded/
atomic counter; optimistic retries would thrash
under that much real contention."

What you see: Under a traffic spike on one specific row, most write attempts fail their version check and retry, which increases load on the same hot row further, sometimes causing a retry storm that looks like the system is falling over even though each individual query is fast.

Why: Optimistic concurrency control's cost model assumes conflicts are the exception — on a row where conflicts are actually common, the constant retry cycle can consume more total work than a pessimistic lock's brief blocking would have.

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

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

Remember: 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.

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

standardintermediate

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.

Think of it as

A version column is a "last edited" stamp on a shared whiteboard — you check the stamp matches what you last saw before erasing and rewriting. A row lock is physically holding the marker so nobody else can write until you're done. A unique constraint is a rule the whiteboard itself enforces — "only one entry per name" — so nobody even has to check manually.

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

What we're doing: Use a unique constraint to prevent two concurrent requests from both winning a "claim this order" race.

unique-constraint-race.sqlsql

CREATE TABLE order_claims (
  order_id INT PRIMARY KEY,
  worker_id TEXT NOT NULL,
  claimed_at TIMESTAMPTZ DEFAULT now()
);

-- Two workers race to claim the same order_id = 42
-- Worker A:
INSERT INTO order_claims (order_id, worker_id)
  VALUES (42, 'worker-a');   -- succeeds

-- Worker B, milliseconds later:
INSERT INTO order_claims (order_id, worker_id)
  VALUES (42, 'worker-b');   -- fails: duplicate key
-- Worker B's application code catches the constraint
-- violation and knows it lost the race — no explicit
-- lock, version column or CAS needed.

2: order_id as the primary key is the entire mechanism — the database enforces "one claim per order" for free.
13: The second INSERT fails at the database level; no application-side locking or version check was required.

Why this works: A unique constraint is often the simplest correct answer to a "who gets to do this exactly once" race — it needs no explicit transaction management, and the database's own conflict detection is atomic by construction.

Building a version-column check when a unique constraint would express the invariant directly

Wrong

text

-- claims table with a nullable worker_id and a
-- version column, checked and updated in application
-- code to "claim" a row
UPDATE order_claims SET worker_id = 'worker-a',
  version = version + 1
  WHERE order_id = 42 AND version = 0;

Better

text

-- a unique constraint on order_id (or a
-- partial unique index expressing the real
-- invariant) rejects the second claim outright
-- — no version bookkeeping needed at all

What you see: Extra columns and application logic exist purely to re-implement a check the database's own constraint system already does atomically, adding a chance for the application-side check to have a bug the database's constraint wouldn't.

Why: Version columns and compare-and-set are for updating a value that already exists and can change repeatedly; "claim this exactly once" is a simpler shape — a uniqueness invariant — and a unique constraint enforces it with less code and less room for a race condition in the check-then-write logic itself.

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

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

Remember: 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.