Cruxly › System design › Foundations of Data Systems

Transaction Isolation Levels

what can go wrong between two transactions, named precisely

"Use a transaction" is not a complete answer — a transaction's isolation level decides which of several specific, nameable anomalies between concurrent transactions are possible, and the honest answer names the anomaly you're accepting, not just the word "isolation". The long version is the lesson "Isolation levels, in depth" in the topic Databases: Choosing One.

The anomalies, each with a concrete example

Five specific things can go wrong when transactions run concurrently, and each has a name and a minimal example worth having ready. (The SQL standard names the first three; lost update and write skew are the ones interviewers probe hardest.)

Dirty read. Transaction A reads a value transaction B wrote but has not yet committed. If B then rolls back, A has read data that never officially existed.

B: UPDATE accounts SET balance = 500 WHERE id = 1;    -- not committed yet
A: SELECT balance FROM accounts WHERE id = 1;          -- reads 500
B: ROLLBACK;                                           -- balance reverts to original
-- A acted on a balance of 500 that never actually existed

Non-repeatable read. Transaction A reads the same row twice within one transaction and gets different values, because B committed a change to that row in between A's two reads.

Phantom read. Transaction A runs the same range query twice and gets a different set of rows the second time, because B inserted or deleted a row that matches the query's condition in between. This is distinct from a non-repeatable read: it's about rows appearing or disappearing from a result set, not one row's value changing.

Lost update. Two transactions both read the same value, both compute a new value based on what they read, and both write — the second write silently overwrites the first, and the first transaction's change is lost as if it never happened.

A: SELECT stock FROM items WHERE id=1;    -- reads 10
B: SELECT stock FROM items WHERE id=1;    -- reads 10
A: UPDATE items SET stock = 10 - 3 WHERE id=1;   -- writes 7
B: UPDATE items SET stock = 10 - 5 WHERE id=1;   -- writes 5, overwriting A's decrement entirely
-- 8 units of stock were sold but the count only reflects 5 sold

Nothing here throws an error — both writes "succeed." The bug is that B computed its new value from a number that was already stale by the time it wrote.

This last one is worth dwelling on because it is the anomaly that shows up constantly in interview-relevant systems — it is exactly the race the seat booking and rate limiter exercises elsewhere in this track solve by using a single conditional update instead of a separate read-then-write.

Write skew. Two transactions each read the same data, each decide based on it, and each write a different row — so no single row is ever written twice, yet together they break a rule.

Rule: at least one doctor must be on call. Alice and Bob are both on call.
A: SELECT count(*) FROM oncall WHERE shift = 'tonight';   -- 2
B: SELECT count(*) FROM oncall WHERE shift = 'tonight';   -- 2
A: UPDATE oncall SET on_call = false WHERE doctor = 'Alice';
B: UPDATE oncall SET on_call = false WHERE doctor = 'Bob';
-- both commit; nobody is on call

Write skew is the anomaly that separates snapshot isolation from true Serializable. Each transaction saw a consistent snapshot and wrote a row nobody else wrote, so snapshot isolation (Postgres's Repeatable Read, Oracle's "Serializable") allows it. Only Serializable — or an explicit lock on the rows the decision depended on (SELECT ... FOR UPDATE) — prevents it.

The isolation levels, and which anomalies each one actually stops

The SQL standard defines these four levels by which anomalies they permit — not by mechanism, which is why two databases at "the same" level can behave differently underneath.

Read Uncommitted. Permits dirty reads. Rarely used deliberately; some databases (Postgres) don't even implement it distinctly from Read Committed.

Read Committed — the default in Postgres, Oracle, and SQL Server. Guarantees you never read another transaction's uncommitted data. Still permits non-repeatable reads, phantom reads, and lost updates — two reads of the same row, or the same range query, within one transaction can see different results if another transaction commits in between.

Repeatable Read — the default in MySQL's InnoDB. Guarantees that once a transaction reads a row, re-reading it within the same transaction returns the same value. The standard permits phantoms at this level, and real engines differ in what they actually give you:

MySQL InnoDB avoids phantoms for plain SELECTs by reading from the transaction's snapshot; gap/next-key locks apply only to locking reads (SELECT ... FOR UPDATE) and writes. Mixing plain and locking reads in one transaction can still show phantom-like behaviour. It still permits lost updates (a plain read, then a write) and write skew.
Postgres Repeatable Read is really snapshot isolation: no phantoms, and it aborts a transaction that would cause a lost update — but it still allows write skew.
Oracle's "Serializable" is also snapshot isolation, so it allows write skew despite its name.

That's the canonical example of "the standard defines the anomaly, not the mechanism": two databases at the same named level behave differently, so check the specific engine's actual guarantee rather than the standard's minimum.

Serializable — the strongest level. Guarantees the outcome of running transactions concurrently is equivalent to running them in some serial order, one at a time. Every anomaly here is ruled out, write skew included. The cost: the database must either take much broader locks or use optimistic concurrency control — proceed as if there's no conflict, then abort and retry a transaction if a conflict is detected at commit time. Either way, Serializable trades throughput for correctness, and that trade needs to be named, not assumed to be free.

Optimistic versus pessimistic concurrency control

Pessimistic: acquire a lock before reading, assuming conflict is likely. SELECT ... FOR UPDATE locks the selected rows until the transaction commits or rolls back, so no other transaction can modify (or, depending on the lock type, even read) them concurrently. Correct and simple to reason about; the cost is that a slow transaction holding a lock blocks every other transaction that wants the same rows, which can cascade into a significant throughput hit under contention.

Optimistic: proceed without locking, verify at commit time. Read a row along with a version number or timestamp; when writing, check that the version hasn't changed since the read (UPDATE ... WHERE id=1 AND version=5) — the exact same conditional-update, find-and-claim primitive used throughout this track for the seat booking hold, the job scheduler's claim, and the rate limiter's bucket creation. If the row changed, the write affects zero rows, and the application retries. This is cheap when conflicts are rare and expensive (repeated retries) when they are common — the right choice depends entirely on actual contention, which is knowable from the read/write ratio on the contended rows.

The lost-update anomaly is what optimistic concurrency control is specifically designed to prevent, and recognising that the version-check pattern used everywhere in this track's other exercises is optimistic concurrency control, applied without ever naming it, is worth saying explicitly — the vocabulary from this lesson retroactively explains a mechanism you'd already reach for instinctively.

Choosing a level, and what to actually say in an interview

Default to your database's default, and only escalate when a specific anomaly would cause a specific, named problem. Read Committed is sufficient for the overwhelming majority of application code. Escalating to Serializable everywhere "to be safe" trades away concurrency across the whole application for a guarantee most of it never needed.

Name the anomaly your specific operation is vulnerable to, and how you're avoiding it — that is a stronger and more precise answer than naming an isolation level in isolation. "This decrement operation is vulnerable to lost updates under Read Committed, so I'm using an atomic UPDATE ... SET qty = qty - 1 WHERE qty >= 1 instead of a separate read-then-write" says more than "I'd use Serializable isolation here" — the first shows you understand the actual race, the second just cites a stronger hammer without showing you know which nail it's for.

Application-level idempotency and database-level isolation solve different problems and are frequently confused. Isolation levels govern what one transaction can observe about others running concurrently. Idempotency (covered throughout this track — the payment ledger, the notification system, the job scheduler) governs what happens when the same operation is retried, possibly well after the original transaction already committed. A system frequently needs both, and treating them as the same concern is a common, avoidable confusion.

:::warn The reflexive wrong answer "I'd just use Serializable isolation" as a first instinct for any concurrency question, without identifying the specific anomaly at risk, is the tell that the concept is memorised rather than understood — much like reaching for a Singleton or a distributed lock reflexively rather than after establishing it's actually needed, covered elsewhere in this track. :::

How to say it in the interview

"I don't say 'I'd wrap this in a transaction' and stop there. I name the specific anomaly this operation is vulnerable to at a given isolation level — a lost update, a phantom read — because that is what actually decides whether the isolation level I picked is enough."

Checklist

Named the specific anomaly (dirty read, non-repeatable read, phantom read, lost update) rather than saying 'concurrency issue'
Distinguished a non-repeatable read (one row's value changes) from a phantom read (the result set's membership changes)
Identified lost update as the anomaly behind the classic read-then-write race
Knew Read Committed is the common default and what it still permits
Knew engines differ at the same named level, and named write skew as what snapshot isolation allows
Distinguished pessimistic locking (block others) from optimistic concurrency control (verify at commit, retry on conflict)
Recognised the version-check UPDATE pattern used elsewhere in this track as optimistic concurrency control
Escalated isolation level only for a specific, named anomaly rather than defaulting to Serializable everywhere
Distinguished database isolation (concurrent transactions) from application idempotency (safe retry)

Practice this interactively