Walk me through the transaction isolation levels and which anomalies each one permits.

What a level actually promises

An isolation level is not a description of a mechanism. It is a contract stated negatively: a list of anomalies the engine promises will not happen to you, with everything unlisted implicitly permitted. That framing matters in an interview because the strong answer is always "level X allows these anomalies, so here is the class of bug I have to defend against myself", never a recital of four names in ascending order.

The SQL standard defines the levels in terms of three phenomena, which turned out to be an incomplete list. Two engines can be compliant at the same nominal level and still behave differently, because the standard sets a floor on strictness and says nothing about mechanism.

The four anomalies

• Dirty read — you see a row version written by a transaction that has not committed, and may still roll back. You are reading data that never officially existed.
• Non-repeatable read — you read a row, another transaction commits an update to it, you read the same row again inside the same transaction and get a different value.
• Phantom read — you run a predicate query, another transaction commits an insert or delete matching that predicate, you re-run the query and the result set has changed membership rather than a single row having changed value.
• Write skew — two transactions each read an overlapping set of rows, each makes a decision based on what it read, and each writes to a different row. Neither write conflicts with the other, so both commit, and the invariant that spanned both rows is now violated.

Write skew is the one candidates omit, and it is the one that separates an answer built from reading the standard from one built from operating a database.

How the engines really behave

Level	Dirty read	Non-repeatable read	Phantom	Write skew
READ UNCOMMITTED (standard)	allowed	allowed	allowed	allowed
READ UNCOMMITTED (PostgreSQL)	impossible	allowed	allowed	allowed
READ UNCOMMITTED (InnoDB)	allowed	allowed	allowed	allowed
READ COMMITTED	prevented	allowed	allowed	allowed
REPEATABLE READ (standard)	prevented	prevented	allowed	allowed
REPEATABLE READ (PostgreSQL)	impossible	prevented	prevented	allowed
REPEATABLE READ (InnoDB)	prevented	prevented	prevented for locking reads	allowed
SERIALIZABLE (PostgreSQL, SSI)	impossible	prevented	prevented	prevented
SERIALIZABLE (InnoDB)	prevented	prevented	prevented	prevented

PostgreSQL accepts READ UNCOMMITTED as a synonym for READ COMMITTED. Its MVCC design means an uncommitted row version is simply not visible to any other snapshot, so there is no code path that could produce a dirty read even if you wanted one. Its REPEATABLE READ is snapshot isolation: the transaction takes one snapshot at its first statement and reads from it throughout, which eliminates phantoms as a side effect rather than by design.

InnoDB reaches a phantom-free-looking REPEATABLE READ by two different routes, and the distinction is where candidates who have only read MySQL's marketing sentence come unstuck. Locking reads and writes — SELECT ... FOR UPDATE, FOR SHARE, UPDATE, DELETE — take next-key locks, which combine a row lock with a gap lock on the space before the row, so a concurrent insert into the scanned range blocks. That is genuine phantom prevention, and it is why the same workload deadlocks more often under InnoDB REPEATABLE READ than under READ COMMITTED. A plain non-locking SELECT, by contrast, takes no gap locks at all: it reads the snapshot, so a phantom is merely invisible rather than prevented.

Worse, the two mechanisms interact. A current read such as an UPDATE with a matching predicate sees rows other transactions committed after your snapshot was taken, and once it touches those rows they become visible to subsequent plain SELECTs in the same transaction. InnoDB REPEATABLE READ is therefore neither pure snapshot isolation nor pure two-phase locking, and saying "phantoms prevented, full stop" is the answer an interviewer probes.

Write skew, concretely

The canonical case is an on-call rota with the invariant "at least one doctor is on call for a shift". Alice and Bob are both on call for shift 42 and both request leave simultaneously.

-- Session A                                   Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;         BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors                   SELECT count(*) FROM doctors
  WHERE shift_id = 42 AND on_call;  -- 2         WHERE shift_id = 42 AND on_call;  -- 2
-- count > 1, so it is safe to leave           -- same snapshot, same conclusion
UPDATE doctors SET on_call = false             UPDATE doctors SET on_call = false
  WHERE id = 'alice';                            WHERE id = 'bob';
COMMIT;                                        COMMIT;   -- succeeds: different row
-- Invariant broken: nobody is on call.

Snapshot isolation cannot catch this. Its write-conflict detection is first-updater-wins on individual rows, and these two transactions never touch the same row. The conflict is between what each read and what the other wrote, which is exactly the dependency PostgreSQL's SERIALIZABLE Snapshot Isolation tracks with predicate locks; under SERIALIZABLE one of the two aborts with a serialisation failure, SQLSTATE 40001. InnoDB gets to the same outcome by a cruder route: its SERIALIZABLE implicitly promotes plain SELECT to SELECT ... FOR SHARE when autocommit is off, so this example resolves as a lock wait and then a deadlock rollback rather than a clean serialisation failure. Either way the application needs retry logic; only the error it retries on differs.

The pragmatic fix, if you do not want to make every transaction retryable, is to materialise the conflict by locking the rows you based the decision on:

BEGIN;  -- READ COMMITTED is sufficient once the rows are locked
SELECT id FROM doctors
  WHERE shift_id = 42 AND on_call
  FOR UPDATE;   -- both transactions now queue on the same rows, so they serialise
-- Aggregates are rejected with FOR UPDATE, so count the returned rows in code
-- and abort unless more than one doctor is still on call.
UPDATE doctors SET on_call = false WHERE id = $1;
COMMIT;

The second transaction blocks on the lock, and after the first commits it re-reads under READ COMMITTED and sees only one doctor on call, so it declines. Note that the same locking read inside a PostgreSQL REPEATABLE READ transaction raises a serialisation failure instead of re-reading, because the row it locked changed after its snapshot was taken.

The trap

SELECT ... FOR UPDATE only locks rows that exist. Half of real write skew is phantom-shaped: two transactions each check that no booking exists for a meeting room slot, each find none, and each insert one. There is no row to lock, so the lock-the-reads trick silently does nothing. The answer there is a unique or exclusion constraint that makes the second insert fail, a lock on a stable parent row such as the room, or genuine SERIALIZABLE whose predicate locks cover the empty range. Offering FOR UPDATE as a universal remedy gets caught by this follow-up; volunteering the limitation yourself shows you have debugged it.

Name each level by the anomalies it permits, and remember that snapshot isolation's blind spot is write skew: two transactions reading the same rows, writing different ones, and jointly breaking an invariant neither violated alone.