Part 5 of 5 · 5 min read
Exercises
Easy
Easy 1: Define write skew (15 minutes)
State, in one sentence, what write skew is.
Done when: two transactions each independently passing a cross-row check, then writing in a way that together violates it, is correctly described.
Hint
Two transactions each independently pass a cross-row check against their own snapshot, then write in a way that together violates the condition.
Easy 2: Name what passes despite the anomaly (15 minutes)
State whether each individual transaction's own constraint check passes or fails in a write skew scenario.
Done when: each individual check passing is correctly stated.
Hint
Each individual transaction's own check passes.
Easy 3: Name the shared root cause (15 minutes)
Name the one underlying cause shared by write skew and snapshot-isolation phantoms.
Done when: each transaction's snapshot being unable to reflect a concurrent transaction's changes is correctly named.
Hint
Each transaction's own fixed snapshot being unable to reflect a concurrent transaction's changes.
Medium
Medium 1: Identify write skew in a new scenario (25 minutes)
Two transactions each check "is the total of my team's two members' remaining leave days still above a minimum," based on their own snapshot, then each independently approves one more leave day for a different team member. Determine whether this is an instance of write skew.
Done when: yes is correctly concluded, since each transaction's own check passes against its own snapshot, but the combined effect of both approvals could violate the minimum, matching the write skew pattern.
Hint
Yes, this matches write skew exactly: each check passes individually against its own snapshot, but the combined effect of both approvals can violate the shared minimum.
Medium 2: Explain why the reservations phantom is not a non-repeatable read (25 minutes)
Explain why the double-reservation scenario is classified as a phantom, not a non-repeatable read, using the distinction from the isolation-levels page.
Done when: the explanation correctly notes a non-repeatable read concerns one specific row's value changing, while this scenario concerns an entirely new row, one that did not previously exist in either snapshot, being missed by a filtering query.
Hint
A non-repeatable read concerns a specific, previously read row's value changing; this scenario instead concerns an entirely new row that did not exist in either transaction's snapshot, which is the defining feature of a phantom rather than a non-repeatable read.
Medium 3: Decide whether a single-row UPDATE with a WHERE clause avoids write skew (30 minutes)
A transaction updates a single row with UPDATE doctors SET on_call = false WHERE doctor_id = 'self' AND (SELECT COUNT(*) FROM doctors WHERE on_call = true AND doctor_id != 'self') > 0;, checking the condition within the same statement. Explain whether this still has the same write skew vulnerability as the two-step version.
Done when: the explanation correctly notes that even within a single statement, the subquery's count is still evaluated against this transaction's own snapshot, which still cannot reflect a concurrent transaction's simultaneous, not-yet-committed update, so the vulnerability can still exist depending on exactly when each transaction's check is evaluated relative to the other's commit.
Hint
The subquery's count is still evaluated against this transaction's own snapshot, which still cannot see a concurrent transaction's simultaneous, uncommitted update, so the same vulnerability can persist depending on the exact timing of each transaction's evaluation relative to the other's commit.
Hard
Hard 1: Explain why write skew cannot be caught by a simple per-row constraint (30 minutes)
Explain why a CHECK constraint on the doctors table, covered in Level 2, cannot by itself prevent the on-call write skew scenario.
Done when: the explanation correctly notes a per-row CHECK constraint can only evaluate the row being written, not a cross-row condition spanning both doctors' current and concurrently changing status, so it cannot catch a violation that only emerges from combining two separate transactions' writes.
Hint
A per-row CHECK constraint only evaluates the single row being written, with no visibility into a different row's concurrently changing status, so it cannot catch a violation that only emerges once both doctors' writes are combined.
Hard 2: Explain why detecting write skew requires looking across transactions (35 minutes)
Explain, in general terms, what kind of mechanism would be needed to actually catch and prevent the write skew scenario, beyond what plain snapshot isolation provides.
Done when: the explanation correctly notes that preventing write skew requires detecting that two concurrent transactions both read overlapping data that the other is about to modify, which requires tracking cross-transaction read-write relationships beyond what a single transaction's own fixed snapshot can see, foreshadowing the serializable snapshot isolation covered on the next page.
Hint
Preventing write skew requires detecting that two concurrent transactions each read data the other is about to modify, which needs tracking relationships across transactions, something a single transaction's own fixed snapshot alone cannot see, pointing toward the mechanism the next page covers.
Hard 3: Compare the write skew and phantom scenarios' structural similarity (40 minutes)
Explain, structurally, why the doctors scenario and the reservations scenario, despite seeming different, are both instances of the same underlying snapshot-isolation limitation.
Done when: the explanation correctly notes both scenarios involve two transactions, each making a decision based on a read against their own snapshot, where that read cannot reflect the other transaction's concurrent change, whether that change is an update to an existing row's value, as in the doctors case, or the insertion of a new row, as in the reservations case, with both ultimately tracing back to the same snapshot blindness.
Hint
In both cases, two transactions each make a decision from a read against their own snapshot, and in both cases that read cannot reflect the other transaction's concurrent change, whether that change updates an existing row, as with the doctors, or inserts an entirely new one, as with the reservations, so both trace back to the same underlying snapshot blindness.