Transactions and isolation anomalies
Trace concurrent schedules, distinguish isolation anomalies, protect reservation invariants, and compare measured B-tree and LSM write costs.
28 min8 steps
By the end you can
- Distinguish dirty reads, non-repeatable reads, phantoms, lost updates and write skew
- Choose constraints, locking or serializable execution for a reservation race
- Choose optimistic or pessimistic concurrency control and handle aborts
- Calculate physical write volume from measured B-tree and LSM amplification
Atomic is not the same as isolated
Atomicity: commit or abort as a unit. Isolation: what concurrent transactions can see. Durability: survives failure after ack. None sends an email atomically. Write the invariant first.
Name the read that changed
Dirty read
- Sees uncommitted data
- Writer may abort
Non-repeatable read
- Same row, new committed value
Phantom
- Matching set gains a row
A read-modify-write can erase another update
Read 10, both write 11: lost update. Fix with value = value + 1 or a version predicate, then check the affected-row count.
A snapshot can preserve reads and still break a rule
Two doctors on call; each checks the other, then goes off. Different rows, so both commit and nobody is on call. This is write skew. Predict the invariant, then compare snapshot with serializable.
Enforce the rule where the race happens
Enforce the rule where the race happens: a unique constraint on the seat. A prior SELECT cannot stop a concurrent insert. Multirow rules need serializable transactions or a shared locked record. Retry on constraint and serialization failures, and keep side effects outside.
Choose where contention is paid
Pessimistic
- Lock first
- Good when conflicts are common
- Deadlocks: lock in order
Optimistic
- Check at commit
- Good when conflicts are rare
- Bound the retries
Serializable
- Serial-equivalent result
- Transactions can abort
Isolation and storage structure answer different questions
B-tree: ordered pages. LSM: buffer and merge sorted runs. Neither name tells you the isolation level.
Write amplification = physical bytes written ÷ logical bytes updated.
- Logical50 MB
- B-tree physical (3×)150 MB
- LSM physical (8×)400 MB
Loading question…
Check your mastery
Score 80% or more. Name the anomaly, state the invariant, and name the database rule that enforces it.
Mastery check
5 questions on fresh variants. One attempt each, no time limit. Score 80% or more to master the lesson.
Replay the write-skew schedule under snapshot and serializable. Then see queues for coordinating a commit with message delivery.
Answer this step to continue.