Part 5 of 5 · 4 min read
Exercises
Easy
Easy 1: Name the gap this page closes (15 minutes)
State the specific gap serializable snapshot isolation closes, compared to plain snapshot isolation.
Done when: detecting unsafe cross-transaction read-write interactions, such as write skew, is correctly named.
Hint
Detecting unsafe cross-transaction read-write interactions, such as the write skew pattern, that plain snapshot isolation cannot catch.
Easy 2: Name what detection relies on (15 minutes)
State what serializable snapshot isolation tracks to detect a dangerous structure, instead of acquiring locks upfront.
Done when: which rows each transaction has read and written is correctly named.
Hint
Which rows each transaction has read and which rows it has written.
Easy 3: Name the database's response to a dangerous structure (15 minutes)
State what happens to a transaction once a dangerous structure involving it is detected.
Done when: the transaction being aborted before it commits, typically to be retried, is correctly described.
Hint
It is aborted before it can commit, typically so the application can retry it.
Medium
Medium 1: Decide how the doctors scenario would resolve (25 minutes)
Under serializable snapshot isolation, trace what happens to the doctors scenario from the previous page, where transactions A and B each check availability and both try to go off call.
Done when: one of the two transactions (the one detected as part of the dangerous structure near its own commit) being aborted and retried, preventing both from successfully going off call, is correctly described.
Hint
One of the two transactions, detected as part of the dangerous structure as it attempts to commit, is aborted and retried, preventing both from successfully going off call at once.
Medium 2: Compare the cost model to two-phase locking (25 minutes)
Explain the key difference between when a cost is paid under two-phase locking compared to under serializable snapshot isolation.
Done when: the explanation correctly notes two-phase locking pays a waiting cost upfront, on every potentially conflicting operation, while serializable snapshot isolation only pays an abort-and-retry cost specifically when a dangerous structure is actually detected.
Hint
Two-phase locking pays its cost upfront, as waiting, on every potentially conflicting operation; serializable snapshot isolation only pays its cost, an abort and retry, specifically when a dangerous structure is actually detected.
Medium 3: Decide if serializable snapshot isolation prevents a dirty read (30 minutes)
Determine whether serializable snapshot isolation, being built on top of MVCC, still prevents dirty reads, using the anomaly definition from the isolation-levels page.
Done when: yes is correctly concluded, since it inherits MVCC's snapshot-based reads, which only ever read already-committed versions, the same property that prevented dirty reads under plain snapshot isolation.
Hint
Yes, since it is built on MVCC's snapshot reads, which only ever read already-committed versions, the same mechanism that prevented dirty reads under plain snapshot isolation.
Hard
Hard 1: Explain why detection can happen without blocking reads or writes (30 minutes)
Explain why serializable snapshot isolation can detect a dangerous structure without ever blocking a transaction's reads or writes during its normal execution.
Done when: the explanation correctly notes that reads and writes under MVCC never contend for locks in the first place, as covered two pages ago, so the detection mechanism only needs to track relationships after the fact and act at commit time, rather than intercepting and blocking the reads and writes themselves.
Hint
Reads and writes under MVCC never contend for locks to begin with, so detection only needs to track read-write relationships after the fact and act at commit time, with no need to intercept or block the reads and writes themselves.
Hard 2: Explain the trade-off of occasional aborts versus upfront waiting (35 minutes)
A workload has very rare write skew risk but very high read-write concurrency. Explain why serializable snapshot isolation is likely a better fit for this workload than two-phase locking.
Done when: the explanation correctly notes that with dangerous structures rare, the occasional abort-and-retry cost under serializable snapshot isolation is paid rarely, while two-phase locking would impose its waiting cost continuously, on every potentially conflicting operation, regardless of how rarely an actual violation would have occurred.
Hint
With dangerous structures rare, the occasional abort-and-retry cost is paid infrequently, while two-phase locking's waiting cost would apply continuously to every potentially conflicting operation, regardless of how rarely an actual violation would have occurred.
Hard 3: Connect this page back to optimistic concurrency control (40 minutes)
Explain the structural similarity, and the one key structural difference, between serializable snapshot isolation and optimistic concurrency control, covered four pages ago.
Done when: the explanation correctly notes both defer their real correctness check to close to commit time rather than locking upfront, but optimistic concurrency control validates a single transaction's own read set for staleness, while serializable snapshot isolation specifically tracks and detects a cross-transaction read-write pattern, the dangerous structure, spanning two or more transactions at once.
Hint
Both defer their real correctness check to close to commit time rather than locking upfront; optimistic concurrency control validates one transaction's own read set for staleness, while serializable snapshot isolation tracks and detects a pattern spanning two or more transactions' reads and writes together.