Part 2 of 5 · 2 min read
Write skew
Two transactions each independently check a cross-row condition against their own snapshot, then both write in a way that violates the condition together.
Think of two on-call doctors. Each separately checks "is at least one doctor still on call besides me," and each independently sees yes. Each then independently decides it is safe to go off call, leaving nobody on call at all.
In plain words: write skew occurs when two transactions each read a set of rows to check some cross-row condition, such as "at least one of us is still available," and each finds the condition satisfied based on its own snapshot. Each transaction then writes a different row, based on that check, in a way that would have been unsafe if the other transaction's write were accounted for, but each transaction's own snapshot never reflects the other's not-yet-committed write. The result: both transactions commit successfully, and the cross-row invariant, "at least one doctor on call," ends up violated, even though neither transaction's individual constraint check ever failed.
A worked example. Doctor A and doctor B are both on call; transaction A checks that at least one other doctor is on call besides A, sees B, and sets A to off-call; concurrently, transaction B checks that at least one other doctor is on call besides B, sees A, still on call in B's own snapshot, and sets B to off-call; both commit, leaving nobody on call.
The key lines.
-- Both transactions run this check against their own snapshot
SELECT COUNT(*) FROM doctors WHERE on_call = true AND doctor_id != 'self';
-- Each then independently updates its own row if the check passes
UPDATE doctors SET on_call = false WHERE doctor_id = 'self';
Write skew
Write skew happens when two transactions each check a cross-row condition against their own unchanging snapshot, find it satisfied, and then each write a different row in a way that together violates the condition neither transaction's own check ever caught.
Example: two on-call doctors each independently confirming someone else is covering, then both going off call, leaving nobody on call, is write skew.