Write skew under snapshot isolation

WHAT THIS TESTS The interviewer probes deep understanding of isolation: that snapshot isolation, despite preventing dirty, non-repeatable, and many phantom reads, still permits write skew.

A GOOD ANSWER COVERS Write skew occurs when two concurrent transactions read an overlapping data set, each makes a decision based on that consistent snapshot, and then each writes to different rows. Neither write conflicts with the other, so both commit, yet together they violate an invariant that depended on the combined state. Simple row-level locking cannot prevent it because the two transactions modify disjoint rows; there is no shared row to lock and create a conflict. The real dependency is on a predicate over a set of rows, not a single row, so prevention requires Serializable isolation (such as Serializable Snapshot Isolation, which tracks read-write dependencies), explicit predicate or range locks, materializing the conflict by writing to a shared lock row, or using SELECT FOR UPDATE on the read set.

COMMON WRONG ANSWERS Claiming snapshot isolation is the same as Serializable. Asserting row locks fix it, ignoring that the writes target different rows. Confusing write skew with a lost update, where both transactions write the same row.

LIKELY FOLLOW-UPS How does Serializable Snapshot Isolation detect the dangerous dependency? Why does SELECT FOR UPDATE on the read set help? How is write skew different from a lost update or phantom?

ONE CONCRETE EXAMPLE A hospital rule requires at least one doctor on call. Two doctors are on call; each opens a transaction, reads that two are on call, sees that removing themselves still leaves one, and each takes themselves off call. Both updates touch different rows and both commit under snapshot isolation, leaving zero doctors on call and violating the invariant.