8.10 Worked examples
① Which anomaly is this? Classify each:
| Scenario | Anomaly |
|---|---|
| T1 writes x=3 (uncommitted); T2 reads x=3 | Dirty read |
| T1 writes x (uncommitted); T2 overwrites x | Dirty write |
| T2 reads x=1, then later in the same transaction reads x=2 | Read skew / nonrepeatable read |
| T1 and T2 both read balance=100, both write 90 | Lost update |
| T1 counts 2 doctors, T2 counts 2 doctors, both remove one | Write skew |
| T1 counts 0 bookings, T2 counts 0 bookings, both insert | Write skew via a phantom |
② Why SI can't stop write skew, precisely. Under SI, both transactions read from snapshots taken before either wrote. Neither writes an object the other read-and-wrote, so there is no write-write conflict to detect. The conflict is read-write: T1 read a set that T2's write changed. Only a mechanism that tracks reads — predicate/index-range locks (2PL) or SIREAD tracking (SSI) — can see it.
③ SSI abort probability. N transactions/s each holding read-write conflict potential over a shared hot key, with average transaction duration D seconds. Roughly, the expected number of concurrent conflicting transactions is N × D. If N=1000 and D=5 ms, that's 5 concurrent — modest abort rate. If a code change makes D=200 ms, it's 200 concurrent, and the abort rate goes superlinear. This is why SSI requires short read/write transactions, and why "just add a slow external API call inside the transaction" is catastrophic.
④ 2PC in-doubt cost. Coordinator crashes with 50 in-doubt transactions, each holding exclusive locks on ~20 rows. Restart takes 20 minutes. 1,000 rows are unwritable for 20 minutes, and anything queuing on them backs up. If the coordinator's log is lost: 1,000 rows locked indefinitely, surviving database restarts, until an administrator manually resolves 50 transactions by inspecting every participant.
⑤ Serial execution throughput budget. Single core, 3 GHz. A stored procedure doing 5 in-memory index lookups + 3 writes ≈ 50 µs → ~20,000 txn/s per core. Shard across 16 cores → 320,000 txn/s — if every transaction is single-shard. Introduce 5% cross-shard transactions at 1,000/s ceiling: those 5% now cap total throughput at 20,000 txn/s overall. A 5% cross-shard rate costs you 94% of your throughput.
⑥ Exactly-once without 2PC — the four crash points. Verify §5.6 covers every window:
- Crash before ② — abort, redeliver, ① finds nothing, clean reprocess. ✔
- Crash between ② and ③ — redeliver, ① finds the ID, drop and ack. ✔
- Crash between ③ and ④ — no redelivery; a stale ID row remains, harmless. ✔
- A concurrent retry — the
UNIQUEconstraint onmessage_idrejects the second. ✔
No window produces a double side effect, and no window loses the message. This is why §5.6 is the right default.