What they're testing
Whether you know which level your own code runs at. It directly affects business correctness, and most developers have never checked.
The short answer~30 seconds
READ UNCOMMITTED permits dirty reads. READ COMMITTED stops those but still allows non-repeatable reads and phantoms. REPEATABLE READ additionally stops non-repeatable reads. SERIALIZABLE stops everything, including write skew. The part that matters: Postgres defaults to READ COMMITTED while InnoDB defaults to REPEATABLE READ — so the same code can be correct on one and wrong on the other.
Two transactions overlap in time. At a low isolation level each sees only part of what the other is doing.
The long answer
The four-anomaly table is the standard, but real engines don't implement exactly what it describes. Postgres has no true READ UNCOMMITTED — ask for it and you get READ COMMITTED, because MVCC gives it no way to show you uncommitted data. Postgres's REPEATABLE READ is really snapshot isolation, stronger than the standard in that it also prevents phantom reads.
InnoDB prevents phantoms at REPEATABLE READ a different way: next-key locking — a record lock plus a gap lock on the space before it — so nobody can insert into a range you just scanned. That detail matters because it's also MySQL's most common source of deadlocks: two transactions locking overlapping ranges in opposite order.
What the standard omits is write skew: two transactions read the same set of rows, each decides based on that set, then each writes to a DIFFERENT row — so they never conflict, yet together they break the invariant. The canonical case is on-call: the rule is "at least one doctor on shift", two request leave, each transaction sees the other still there, both commit, the shift is empty. Snapshot isolation doesn't prevent it; only SERIALIZABLE, or an explicit lock, does.
In practice Postgres's SERIALIZABLE (SSI) doesn't lock — it detects conflicts and aborts one transaction with SQLSTATE 40001. Which means choosing that level OBLIGES the application to have a retry loop. Teams that enable SERIALIZABLE and forget the retry see random errors under load and usually blame the database.
Write skew — both commit successfully
-- Bất biến: luôn còn ít nhất 1 bác sĩ trực. Hiện có: Alice, Bob.
-- T1 -- T2
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall SELECT count(*) FROM oncall
WHERE on_shift; -- 2 WHERE on_shift; -- 2
-- "còn 2 người, mình nghỉ được" -- "còn 2 người, mình nghỉ được"
UPDATE oncall SET on_shift = false UPDATE oncall SET on_shift = false
WHERE name = 'Alice'; WHERE name = 'Bob';
COMMIT; COMMIT;
-- Không hàng nào bị ghi trùng -> không xung đột -> ca trực trống.At SERIALIZABLE, Postgres aborts one of them with SQLSTATE 40001 and you retry.
What they'll ask next
?Does SELECT … FOR UPDATE solve write skew?
Yes, if you lock the rows the decision depends on — in the example above, the whole shift set, not just the row you modify. That's the hard part: you must lock what you READ, not what you WRITE, and that's exactly what SERIALIZABLE does for you.
?Why do READ COMMITTED apps mostly work anyway?
Because most logic reads and writes in one statement (UPDATE … SET n = n + 1), and a single statement is atomic. Problems appear only when you READ into the application, compute, then WRITE back — the read-modify-write pattern. That's when you need FOR UPDATE or optimistic locking on a version column.
?Which level for a job queue in the database?
READ COMMITTED plus FOR UPDATE SKIP LOCKED. SKIP LOCKED lets each worker grab rows nobody has locked rather than queueing behind them, so n workers actually run in parallel. It's the standard pattern for a modest queue that doesn't yet justify Kafka.
These lose points
- Saying "just use SERIALIZABLE to be safe". Without a retry loop, that trades a logic bug for random 500s.
- Reciting the four-anomaly table without knowing the default of the engine you actually run.
- Assuming REPEATABLE READ means the same thing everywhere. Postgres prevents phantoms; the standard doesn't require that.
These score well
- Being able to say you CHECKED the isolation level on your last project, and with which statement (
SHOW transaction_isolation).