Transaction Isolation Levels
Explain READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE. Which anomalies does each prevent?
Answers use simple, clear English.
Audio N/AQuick interview answer
READ COMMITTED avoids dirty reads; non-repeatable reads/phantoms possible. REPEATABLE READ keeps row reads stable in a transaction (phantoms depend on engine). SERIALIZABLE prevents phantoms via stricter locking/SSI.
Detailed answer
READ COMMITTED avoids dirty reads; non-repeatable reads/phantoms possible. REPEATABLE READ keeps row reads stable in a transaction (phantoms depend on engine). SERIALIZABLE prevents phantoms via stricter locking/SSI. Higher isolation increases latency/aborts — pick the weakest that meets correctness.
Real example & use case
Bank transfer uses a strict transaction so two concurrent withdrawals cannot both pass a balance check incorrectly.
Pros & cons
Pros of higher isolation: correctness. Cons: contention and serialization failures.