Isolation Levels
Almost every production database in the world runs at an isolation level that allows its application to be wrong, and almost nobody who chose that level could name the anomalies it permits. This track fixes that from the other direction: rather than reading the definitions, you open two psql sessions and cause each anomaly deliberately — a dirty read, a non-repeatable read, a lost update, a phantom, a write skew — then fix it, then find out what the fix costs. The same scripts run against PostgreSQL and MySQL, which disagree about almost everything here, including what their own default level means.
- 01The balance that was never real
Reproduce a dirty read by hand, then find out that PostgreSQL silently refuses to give you one and MySQL will. The first lesson in isolation is that the level you asked for is not always the level you get.
- 02The report that changes while you read it
- 03The lost update SQL will not stop for you
Read a row, compute a new value in your application, write it back. Two sessions doing that concurrently lose one update, and READ COMMITTED permits it. Four fixes, and only two of them are safe under every level.
- 04The row that appears in a range you already counted
- 05Two doctors go off call at once
The anomaly snapshot isolation cannot prevent, because the two transactions never touch the same row. This is the one that breaks real invariants in production, and the one REPEATABLE READ does not save you from.
- 06The locks you did not ask for
- 07What Postgres means by SERIALIZABLE
- 08The transaction that ate your disk
- 09Your own write, missing
- 10Where the anomaly comes back