Isolation Levels
The contract a database makes about what concurrent transactions can see of each other's work — from anything-goes to fully serializable.
What it is
Isolation levels define which concurrency anomalies a transaction is protected from. The SQL (Structured Query Language) standard defines four levels (read uncommitted, read committed, repeatable read, serializable) plus snapshot isolation as a common extension. Each level prevents specific anomalies: dirty reads (seeing uncommitted data), non-repeatable reads (same query returns different rows in the same transaction), phantom reads (range query returns new rows on re-execution), and write skew (concurrent transactions each read a consistent snapshot but together violate a constraint). Higher levels cost more in throughput and contention.
Why senior interviewers ask
Most engineers can't name what their database's default level is, let alone which anomalies it allows. Senior interviewers use this to separate the ones who've debugged a production race condition from the ones who haven't.
Key points
- Read uncommitted: allows dirty reads. Almost no engine actually implements this; Postgres treats it as read committed.
- Read committed: no dirty reads, but non-repeatable reads allowed. Postgres default.
- Repeatable read: no dirty or non-repeatable reads. MySQL InnoDB default; uses MVCC (Multi-Version Concurrency Control) + gap locks.
- Snapshot isolation: transaction sees a consistent snapshot taken at start. Prevents most anomalies but allows write skew.
- Serializable: as if transactions ran one at a time. Postgres uses SSI (Serializable Snapshot Isolation) — optimistic with abort on conflict.
- Write skew: two transactions read overlapping data, each acts on its view, together break a constraint (e.g., 'at least one doctor on call').
- Higher levels = more aborts/retries under contention. App must handle serialization failures (SQLSTATE 40001).
Real systems
Interview probe
'Your app has a check-then-insert pattern (only allow signup if username is unique). What isolation level do you need?' Answer: read committed is NOT enough — you need either serializable, or a unique constraint at the DB level (which is the right answer regardless of isolation level).
Dive deeper
Snapshot isolation is often confused with serializable but they're different. Snapshot isolation gives each transaction a consistent view, but two transactions reading overlapping snapshots can both commit changes that together violate a constraint — that's write skew. SSI (Postgres serializable) tracks read-write dependencies and aborts one of the conflicting transactions at commit time. It's optimistic, so under low contention it's nearly free; under high contention you'll see 40001 errors and need a retry loop.