Write Skew: The Phantom Anomaly of Snapshot Isolation

WHY IT EXISTS Snapshot Isolation (SI) was created to improve database concurrency over stricter, slower serializable isolation. It lets transactions work on a consistent 'snapshot' of the database, avoiding conflicts unless two transactions try to update the exact same data. This optimization, however, opens the door for a subtle anomaly called write skew.

THE MENTAL MODEL Imagine a hospital rule: at least one doctor must always be on-call. Two doctors, Alice and Bob, are currently on-call. Alice checks the schedule, sees two doctors are available, and decides to go off-call. Concurrently, Bob checks the same schedule, also sees two doctors, and also decides to go off-call. Alice updates her record, and Bob updates his. Because they modified different rows, Snapshot Isolation sees no direct conflict and allows both transactions to commit. The result: zero doctors are on-call, violating the business rule.

HOW IT WORKS A write skew occurs when two transactions read a shared set of data, and then each makes a decision to update a different piece of data based on that initial read. Since the writes don't overlap on the same rows or data items, the database's conflict detection mechanism in Snapshot Isolation doesn't trigger. The final state of the database is one that could not have been achieved if the transactions had run serially, leading to logical data corruption.

WHEN TO USE IT You don't 'use' write skew; you defend against it. You must be aware of this risk whenever you use Snapshot Isolation, which is the default level in some databases like PostgreSQL and Oracle. It is most likely to appear when your application logic enforces a constraint that spans multiple rows or tables, such as preventing overbooking, managing inventory based on warehouse capacity, or ensuring a user doesn't exceed a spending limit determined by multiple accounts.

WHEN NOT TO USE IT To prevent write skew, you must either use a stronger isolation level like Serializable, which guarantees no anomalies at the cost of performance, or implement manual prevention tactics. One common method is using explicit locking with a SELECT ... FOR UPDATE statement. This locks the rows that are read to make the decision, forcing the second transaction to wait for the first to complete. Another advanced technique is to 'materialize' the conflict by forcing both transactions to write to a shared dummy row, making the conflict visible to the database engine.

ONE CANONICAL EXAMPLE Consider a table doctors(name, on_call). The rule is COUNT(*) WHERE on_call = true must be >= 1. Initially, Alice and Bob are both on call. Transaction T1 reads the count (2) and updates Alice's row to on_call = false. Concurrently, Transaction T2 reads the count from its own snapshot (also 2) and updates Bob's row to on_call = false. Both commit successfully under SI, as they modified different rows. The final state has zero doctors on-call, breaking the rule.