Problem Statement
Explain write skew under snapshot isolation and how to prevent it.
Explanation
Under snapshot isolation, two units can each read a state that seems valid, then perform disjoint writes that together violate a constraint. Because neither row is updated by both, no direct conflict is detected, and the invariant breaks.
Prevent by adding a locking read on the predicate (SELECT … FOR UPDATE on the relevant set), using Serializable isolation, or enforcing the invariant with a constraint and retrying on violation.
Code Solution
SolutionRead Only
-- Guard with predicate lock SELECT * FROM shifts WHERE doctor_id=:d AND overlaps(:start,:end) FOR UPDATE;
Practice Sets
This question appears in the following practice sets:
