Lesson 06 · What concurrency is allowed to do to you
Isolation levels: the anomaly theater
Perfect isolation has an unambiguous definition: serializable: concurrent transactions produce a result identical to some one-at-a-time ordering. Everything weaker is a named discount, and the price is paid in anomalies: specific, catalogued ways concurrent transactions can produce outcomes no serial execution could. The levels aren't abstract settings: each one is a list of which anomalies you've agreed to tolerate in exchange for speed. The only way to really learn them is to watch each anomaly happen:
The theater
Same interleavings, four isolation levels
Pick a level, then run each scene. Each scene is a fixed interleaving of two transactions; the level decides what T1 sees and whether the ending is a bug. The fourth scene: write skew: is the one that fools senior engineers.
level:
pick a level and a scene
level
dirty read
non-repeatable
phantom
write skew
who defaults to it
read uncommitted
possible
possible
possible
possible
almost nobody (PG treats it as read committed)
read committed
blocked
possible
possible
possible
PostgreSQL, Oracle, SQL Server
repeatable read
blocked
blocked
blocked (PG)*
possible
MySQL/InnoDB
serializable
blocked
blocked
blocked
blocked
those who've been burned
* The SQL standard permits phantoms at repeatable read; PostgreSQL's snapshot implementation happens to prevent them. Levels are minimum guarantees: implementations often give more, which is how code becomes accidentally dependent on one engine's generosity.
Why the defaults are "wrong," and why that's fine (usually)
Notice the industry consensus: nearly everyone defaults to read committed: a level that permits three of the four anomalies. This isn't negligence; it's a judgment that most transactions are short, touch disjoint rows, and don't compute cross-row invariants: so the anomalies rarely materialize, and the throughput/simplicity win is constant. The engineering discipline is knowing which of your transactions aren't like that: anything that reads data, makes a decision on it, and writes the consequence (checks-then-acts, uniqueness-by-query, balance and quota checks, scheduling constraints) is anomaly-shaped. For those, the toolbox in escalating order: atomic single statements (UPDATE ... SET x = x - 1 WHERE x > 0: the check and the act in one indivisible move), explicit locks (SELECT ... FOR UPDATE: pessimistically serialize just the rows in question), or the serializable level (let the engine detect the danger: modern PostgreSQL's SSI does this optimistically, aborting one transaction with a serialization error when patterns look dangerous, which is why serializable code must be written to retry).
The retry contract: under SERIALIZABLE (and often under REPEATABLE READ in Postgres), a perfectly correct transaction can fail with 40001 serialization_failure: not because it was wrong but because the combination was dangerous and someone had to lose. This is the same shape as the distributed systems course's optimistic patterns: proceed without locks, detect conflict, retry the loser. Frameworks that swallow this error, or apps that surface it to users as a crash, have misunderstood the deal. Wrap serializable transactions in a retry loop; keep them short; keep them idempotent (Lesson 1 of the distributed systems course, everywhere forever).
Check yourself
1. Under READ COMMITTED, a report transaction sums account balances while a transfer moves €100 between two accounts mid-report. The report shows the total off by €100. Which anomaly, and why did "read committed" allow it?
The subtle middle anomaly: no single read was wrong, the combination was. Fixes in order of cheapness: run reports at REPEATABLE READ (one snapshot for the whole transaction: MVCC makes this nearly free, Lesson 5), or use a single-statement SUM (one statement = one snapshot even at read committed). The diagnostic tell: "totals that don't add up in reports, but the data looks fine afterwards."
2. The on-call scheduler bug (scene 4): both doctors' transactions checked "≥2 on call," both saw 2, both went off duty. Why did REPEATABLE READ's snapshot isolation fail to stop it: no read was stale, was it?
The anomaly that survives snapshot isolation precisely because snapshot isolation only detects same-row write conflicts. Each transaction's logic was locally flawless. Defenses: SERIALIZABLE (SSI tracks read-write dependencies and aborts one), FOR UPDATE on the rows whose stability you're depending on (materializing the conflict), or a constraint/trigger enforcing the invariant. The general lesson: "I read X, therefore I may write Y" is only safe if X is protected until Y commits.
3. A team upgrades everything to SERIALIZABLE "to be safe" and production starts throwing intermittent 40001 errors under load, which the app reports to users as failures. What's the correct reading?
Optimistic serializability's whole deal: no blocking, full correctness, occasional deliberate abort. The retry loop isn't a workaround: it's the other half of the API. (Also fair: measure whether ALL transactions need it; a common architecture is serializable for the invariant-bearing few, read committed for the bulk. Correctness where it matters, throughput where it doesn't.)
