A rule requires at least one engineer on call. Two engineers simultaneously remove themselves; both transactions check the rule, both pass, both commit. Nobody is on call. Name the anomaly and three fixes.

What is being tested

Whether you recognise write skew — the anomaly that snapshot isolation permits and that most engineers assume their database prevents.

The anomaly

Write skew. Two transactions read an overlapping set of rows, each verifies an invariant that holds in its snapshot, and each writes a different row. Neither conflicts with the other by the usual definition, because they touched different rows. Individually correct; together they violate an invariant that spans rows.

Snapshot isolation and "repeatable read" in most engines permit this. A unique constraint does not help, because no two rows are the same. This is the key insight: the invariant lives across rows, so no row-level mechanism can defend it.

The same shape appears constantly in business systems:

• Two bookings each checking that capacity remains.
• Two withdrawals each checking a combined balance across accounts.
• Two approvals each checking that an aggregate limit is not exceeded.
• Two inventory allocations each checking available stock.

Three fixes

1. Serializable isolation. The engine detects that the two transactions could not have occurred in any serial order and aborts one. Correct, general, and requires that the application retries on serialisation failure — the retry is part of the pattern, not a workaround for a flaw.

2. Materialise the conflict into a single row. Introduce a row that both transactions must update — a rota row holding the on-call count, or a lock row for the schedule. Now the two transactions genuinely conflict, and standard row-level concurrency control handles it. Cheaper than serializable and works at any isolation level. This is the technique worth reaching for first, because it converts a hard problem into an easy one.

3. An explicit lock on the predicate's scope. SELECT ... FOR UPDATE over the affected rows, so the second transaction blocks until the first commits and then re-evaluates against the new state. Effective; be careful about lock ordering to avoid deadlock, and never hold it across a network call.

A fourth, worth mentioning as the architectural answer: make it a constraint the database can enforce. If the invariant can be expressed as a check on a single row or as an exclusion constraint, the database enforces it against every writer including the batch job and the manual fix, which application-level checks never do.

What a strong answer adds

Naming the related trap: read-modify-write lost updates. Reading a value, computing a new one in application code, and writing it back loses one of two concurrent updates at every isolation level below serializable. The fix is a conditional update on a version column — an optimistic concurrency check — or doing the arithmetic in the database.

And noting that isolation level names are not portable: "repeatable read" means materially different things in different engines, so the guarantee must be verified rather than assumed from the name.