Databases · Question 24 of 50

What are isolation levels? Dirty reads, non-repeatable reads and phantoms.

What they're checking

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
The follow-up they'll ask

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.

Your progress stays on this device.