What is write skew in a database, and how can two transactions that never write the same row still leave the data violating a business rule?

## The anatomy Write skew has three ingredients, and all three are necessary: 1. **An invariant spanning multiple rows.** "At least one doctor on call." "The sum of the two account balances stays non-negative." "No more than N active sessions." No single row expresses the rule. 2. **Overlapping reads.** Each transaction reads the rows the invariant covers, to check whether its action is permitted. 3. **Disjoint writes.** Each transaction then modifies a *different* row (or inserts a different new row). Because the write sets do not intersect, nothing in a conventional write-conflict detector notices anything. Each transaction is individually correct against the state it read. The composite state is one that no serial ordering could have produced, which is exactly the definition of a non-serializable execution. ## The worked example, step by step Table `doctors(name, shift_id, on_call)`, with Alice and Bob both `on_call = true` for shift 42. Rule: a shift must always have at least one doctor on call. ``` T1 (Alice) T2 (Bob) BEGIN; BEGIN; SELECT count(*) FROM doctors WHERE shift_id=42 AND on_call; SELECT count(*) FROM doctors -- 2, so it is safe for me to leave WHERE shift_id=42 AND on_call; -- 2, so it is safe for me to leave UPDATE doctors SET on_call=false WHERE name='alice'; UPDATE doctors SET on_call=false WHERE name='bob'; COMMIT; COMMIT; ``` Result: zero doctors on call. Run them one after another and the second returns 1 and aborts its own decision. So the concurrent result is not equivalent to either serial order. ## Why it is not one of the classic three anomalies The textbook phenomena — dirty read, non-repeatable read, phantom — are all about a *reader* observing something inconsistent. In write skew, neither transaction observes anything inconsistent. Both saw a perfectly valid, committed, stable snapshot. The anomaly is entirely in the *outcome*: the pair of writes taken together contradicts the reads that authorized them. That is why it does not appear in the classic isolation table at all; it was catalogued later, when snapshot isolation became widespread and needed anomalies of its own to be described by. ## Where it appears in real systems - **Booking and capacity**: two requests each check "seats sold < capacity" and each insert a booking. - **Financial rules across accounts**: an overdraft rule defined over the combined balance of a customer's accounts, with each transaction debiting a different account. - **Uniqueness enforced in application code**: two sign-ups each query for an existing username, find none, and each insert their own new row. (Here the overlapping read is over rows that do not exist yet — the phantom-shaped variant of the same bug.) - **State machines with a quorum rule**: two approvers each check "approvals < required" and each add their own approval, or two operators each drain a different node while the rule is "keep two nodes live". - **Meeting-room double booking**: each transaction checks for an overlapping reservation, finds none, and inserts its own. ## How to recognize it in a code review Look for the sequence: a `SELECT` that aggregates or counts over a set, an `if` in application code that branches on that result, and then a write to a row *other than* the ones counted. That triangle is the signature. The tell-tale phrase in a design doc is "we check first, then update" — safe only when the check and the update concern the same row and the engine detects the conflict, and unsafe as soon as the check spans rows the write does not touch. ## The relationship to lost update A lost update also involves a read-then-write, but both transactions write the *same* row, so one write is silently overwritten. Engines detect that: a same-row write conflict is exactly what first-updater-wins or row locking catches. Write skew is the variant that escapes those mechanisms precisely because the writes are disjoint — which is why it needs a fundamentally different defence rather than a stronger row-level one. ## The takeaway sentence Write skew is what happens when a transaction's correctness argument depends on rows it reads but does not write; unless the database is told to treat those reads as part of the conflict footprint, nothing will stop two such arguments from being valid separately and wrong together.