Write skew, and the exact boundary of snapshot isolation

What single shape does every write skew share, and where exactly does snapshot isolation stop being serializable?

Write skew: one generative shape

Every instance of write skew has the same shape: read a set, check an invariant over it, write into that set.

Examples: two doctors both going off call when at least one must stay; room double-booking; unique-username check-then-insert; non-negative total over two accounts; inventory reservation. It is easy to search for: look for SELECT count/sum/exists followed by INSERT/UPDATE in the same transaction, and you have your list of candidates.

Anika's second bug, step by step. Initially main = 60 and savings = 40, so the sum is 100.

The final sum is −20 + (−40) = −60. A and B wrote different rows, so snapshot isolation's write-set check finds no conflict. Neither transaction saw the other's write. In the dependency graph, A read savings, which B overwrote (rw edge A→B), and B read main, which A overwrote (rw edge B→A). That is a cycle of two rw edges. Run one after the other, the second withdrawal would have seen the first and been refused.

Here is what write skew actually is, and it is the sharpest idea in this article:

This is the mismatch rule in a correctness role (it is usually met as a cost). It also explains the standard fix without any need to memorize it: materialize the conflict. Force both transactions to write one shared row, for example a joint_balance_lock row or a per-customer total row that every withdrawal updates. Now both transactions write the same row, SI's first-committer-wins check sees the collision, and one aborts. You have coarsened the detector's unit until it matches the invariant's unit.

-- Materialize the conflict: every withdrawal for this owner touches the same row.
UPDATE owners SET version = version + 1 WHERE id = 42;
-- ... then check the sum and update main or savings

Serializable, and the exact boundary of SI

Fekete's result is a real characterization, not a rule of thumb. Any cycle in an SI execution must contain two consecutive rw edges. That means some transaction, the pivot, has both an incoming and an outgoing anti-dependency, and the transaction that its outgoing edge points to must commit first. (In Anika's case, A and B each form a pivot with the other.) This pattern is called the dangerous structure.

It is necessary, but not sufficient: some dangerous structures are harmless. That is why SSI's cheap test (two flags per transaction: "has an incoming rw edge", "has an outgoing rw edge") over-approximates and pays in false aborts. This is again the approximation rule. The mechanism belongs to concurrency control. Here we only need the result: the pivot structure converts "SI might be fine" into a checkable property of a transaction mix.

There are three ways to hold serializability. The level name hides which one you bought:

And one thing none of them buys: serializability says nothing about recency. A serializable read-only transaction may legally be ordered arbitrarily far in the past. That hole is what The unification and its price closes with external consistency.