Detailed explanation
Transaction isolation levels define how much a transaction can observe changes made by concurrent transactions. Isolation is the “I” in ACID, and it matters when multiple requests read or modify shared data, such as account balances or inventory. The SQL standard describes four levels through the anomalies they permit or prevent. Stronger isolation provides stronger consistency guarantees, but its cost depends on the database implementation and workload; it does not always mean blocking every concurrent operation.
READ UNCOMMITTED permits dirty reads: a transaction may read another transaction’s changes before they commit, even if those changes are later rolled back. READ COMMITTED prevents dirty reads, but repeated queries can return different committed values because another transaction commits between statements; this is a non-repeatable read. REPEATABLE READ additionally prevents non-repeatable reads. A phantom occurs when repeating a predicate query, such as selecting accounts with balance above 10,000, returns a changed set of matching rows because of concurrent inserts, deletes, or updates. Standard REPEATABLE READ may permit phantoms.
SERIALIZABLE provides results equivalent to some serial execution of the committed transactions, preventing these anomalies and broader serialization anomalies such as write skew. Databases implement isolation using locks, multiversion concurrency control (MVCC), conflict detection, or combinations of these techniques. Locks can delay conflicting work; MVCC allows readers to access older row versions. Serializable execution may block transactions or abort one on a conflict, requiring the application to retry the entire transaction.
In the example, REPEATABLE READ protects a second read of the account balance from changes committed by another transaction after the first read; your own updates can still change what you see. However, syntax and guarantees vary: PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, and its REPEATABLE READ also prevents phantoms but can allow write skew. In PostgreSQL, set the level within BEGIN or use BEGIN ISOLATION LEVEL REPEATABLE READ. Interviewers expect you to distinguish standard guarantees from database-specific behaviour and choose isolation based on business invariants, contention, and retry handling.
Key points to remember
- READ UNCOMMITTED permits dirty reads; READ COMMITTED prevents them.
- REPEATABLE READ also prevents non-repeatable reads, but standard guarantees still allow phantoms.
- SERIALIZABLE makes committed transactions behave as though executed in some serial order.
- Locking, MVCC, and conflict detection determine practical blocking, visibility, and retry behaviour.
- Choose isolation using the database’s actual guarantees and the business rules that must remain true.
Common mistakes candidates make
- Assuming isolation-level names provide identical behaviour across all database engines.
- Claiming REPEATABLE READ always prevents phantoms or guarantees serializable execution.
- Assuming stronger isolation eliminates application retries, or that a plain SELECT automatically locks the row for later updates.
Likely follow-up questions
What is the difference between a non-repeatable read and a phantom read?
A non-repeatable read occurs when a previously read row changes or disappears between reads. A phantom concerns a change in the set of rows satisfying a query predicate, such as an additional account matching a balance threshold.
Does REPEATABLE READ prevent write skew?
Not necessarily: snapshot-based REPEATABLE READ can let two transactions read the same state and update different rows, jointly breaking a business rule. SERIALIZABLE, suitable explicit locking, or a database-enforced constraint can prevent this, depending on the rule.
When would you use SELECT ... FOR UPDATE?
Use it when a read-modify-write operation must coordinate access to selected rows by locking them until the transaction ends. It does not automatically protect every predicate or missing row, so its guarantees depend on the engine, isolation level, and query.
Interview tip: Explain the four levels through dirty reads, changed values, and changed result sets, then mention implementation differences and transaction retries.