CSE Roadmap

Core tier · about 4 hours · 7 parts

Part 5 of 7 · 2 min read

Write skew

Two transactions each check a condition and each change a different row, breaking a rule together.

Think of two doctors on call. The hospital rule: at least one doctor must remain on call. Alice and Bob both feel ill. Each checks the roster, sees two names, and concludes it is safe to go off. Both leave. Nobody is on call.

The book uses this very example. In each transaction the application first checks that two or more doctors are on call. Under snapshot isolation both checks return 2. Each then updates their own record to off call. Both commit. The requirement of at least one doctor is broken.

Write skew

Write skew happens when two transactions read the same set of rows, then each updates different rows, so that together they break a rule that each checked on its own.

Example: Alice and Bob each see two doctors on call and each takes themselves off call.

It is neither a dirty write nor a lost update, since the two transactions update different objects. Yet it is a race condition: run one after the other, the second doctor would have been refused. The book calls write skew a generalisation of the lost update problem. If both transactions update the same object, it becomes a dirty write or lost update.

The options are more limited than for lost updates:

  • Atomic single-object operations do not help, since several objects are involved.
  • Automatic lost-update detection does not catch it. It is not found by PostgreSQL's or MySQL's repeatable read, Oracle's serializable or SQL Server's snapshot isolation. Only true serializable isolation prevents it automatically (module 3).
  • Database constraints (uniqueness, foreign keys) help, but "at least one doctor on call" involves several rows, which most databases cannot express. Triggers or materialised views can sometimes do it.
  • The second best: explicitly lock the rows the decision depends on:
BEGIN TRANSACTION;
SELECT * FROM doctors WHERE on_call = true AND shift_id = 1234 FOR UPDATE;
UPDATE doctors SET on_call = false WHERE name = 'Alice' AND shift_id = 1234;
COMMIT;

More examples of the same pattern:

System Check Write Broken rule
Meeting rooms No overlapping booking exists Insert a booking Two overlapping bookings
Multiplayer game Square is empty Move a piece to it Two pieces on one square
User names Name is not taken Insert the account Two users with one name
Spending Sum of spending under limit Insert a spend Limit exceeded

A worked example with numbers. A bank allows two accounts of one customer to go negative as long as the total is at least 0. A and B each hold 60 (total 120). T1 withdraws 100 from A after checking 120 − 100 ≥ 0. T2 withdraws 100 from B after the same check. Both see a total of 120 in their snapshot. Result: A = −40, B = −40, total −80. Run one after the other, the second check would fail (20 − 100 < 0).

Check yourself:

  • Why does automatic detection of lost updates miss write skew?
  • Give one write skew example outside the hospital.