SQL · Queries that survive scale
What are transaction isolation levels and their anomalies?
Short answer
Isolation levels trade concurrency for protection from dirty reads, non-repeatable reads, and phantoms. Most production systems use read committed or repeatable read.
SQL defines four levels: read uncommitted, read committed, repeatable read, and serializable. Exact behavior varies by engine. PostgreSQL, for example, implements read uncommitted as read committed and uses MVCC snapshots rather than read locks for ordinary reads.
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;Anomalies to know
- Dirty read: seeing another transaction's uncommitted change
- Non-repeatable read: reading the same row twice and getting different values
- Phantom read: a re-run query returns new rows that match the predicate
- Lost update: two transactions overwrite each other's changes
- Write skew: transactions read overlapping data and make individually valid but jointly inconsistent decisions
Serializable is the strongest level and can abort transactions with serialization failures. Applications must retry those failures. Repeatable read prevents non-repeatable reads and, in PostgreSQL, also prevents phantoms, but write skew still needs explicit locking or serializable isolation.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1What is the difference between a non-repeatable read and a phantom read?
- 2How does PostgreSQL implement repeatable read?
- 3What is write skew and how do you prevent it?
- 4Why do serializable transactions need retries?
Say it in the interview
Isolation levels define which concurrency anomalies a transaction may observe. The main ones are dirty reads, non-repeatable reads, phantoms, lost updates, and write skew. I usually run at read committed for normal work and use repeatable read or serializable for multi-step invariants. Serializable can abort with serialization failures, so the application must retry, and I check engine-specific behavior because the names do not guarantee identical semantics.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.