Databases · Question 24 of 50
What are isolation levels? Dirty reads, non-repeatable reads and phantoms.
Concurrency anomalies, and how to stop them in code.
The answer
Isolation levels trade safety for concurrency. Read uncommitted allows dirty reads: seeing another transaction's change before it commits. Read committed, PostgreSQL's default, sees only committed data, but reading the same row twice can give two values (a non-repeatable read). Repeatable read, MySQL InnoDB's default, keeps your reads stable inside the transaction; the SQL standard still allows phantoms (new rows matching your WHERE), though PostgreSQL and InnoDB prevent most of them. Serializable behaves as if transactions ran one at a time, with more waiting or retries.
Even at a safe level, "read, then write" code (check the balance, then update it) needs SELECT ... FOR UPDATE, or one atomic UPDATE ... WHERE balance >= 500.
The picture
dirty non-repeatable phantom read uncommitted yes yes yes read committed no yes yes repeatable read no no maybe* serializable no no no * by the standard; PostgreSQL and InnoDB prevent most
What is a lost update?
Show the answer
Two transactions read the same value and both write back, so one change disappears. Fix it with an atomic update, SELECT ... FOR UPDATE, or optimistic locking.