A booking service checks under snapshot isolation that a meeting room has no overlapping reservation, then inserts one; occasionally two overlapping reservations both end up committed. Explain the anomaly and give the options for eliminating it, including ones that keep the service on snapshot isolation.

## The anomaly Write skew is the signature failure of snapshot isolation. Its shape is always the same: a transaction **reads a set of rows**, evaluates an invariant over that set, and then **writes a row that is not in the set it read**. Two such transactions running concurrently each see a snapshot in which the invariant holds, each makes a legal write, and the union of the writes violates the invariant. Because the write sets are disjoint, snapshot isolation's only conflict rule — two transactions must not write the same row — never fires. The booking case is the phantom-insert variant. Transaction A queries reservations for room 5 between 10:00 and 11:00 and finds none. Transaction B, running concurrently with a snapshot from the same moment, runs the identical query and also finds none. A inserts its reservation; B inserts its own. Neither read the other's row, because that row did not exist in either snapshot and never becomes visible to the other transaction. Both commit. The database now holds two overlapping reservations, a state no serial order of A and B could produce — in a serial order, the second transaction's query would have returned the first one's row. Note what does *not* help. Re-running the SELECT immediately before the INSERT changes nothing: the re-read comes from the same frozen snapshot. Wrapping the check and the insert in the same transaction is necessary but not sufficient — they already are in one. Making the transaction shorter narrows the window but does not close it. ## Option 1 — declare the invariant to the engine If the invariant can be expressed as a constraint, this is the correct answer and the one interviewers most want to hear. Constraint checks are performed against the actual committed state of the index or table, not against the transaction's snapshot, so a unique index on (room_id, slot_start) rejects the second insert with a constraint violation no matter which isolation level is in force. For interval overlap, an exclusion-style constraint over a range type does the same thing where the engine supports it. The cost is that the invariant must be structural, and the failure surfaces as a constraint error the caller must translate into a domain-level "slot taken" response rather than a serialization retry. Many invariants are not expressible this way — "at least one doctor must remain on call", "the sum of these two account balances must stay non-negative" — and those need one of the following. ## Option 2 — materialize the conflict Create a row that stands for the contended *resource* rather than the contended *fact*, and make every transaction that touches the invariant write it. A `room_slot(room_id, slot, ...)` table pre-populated with one row per bookable slot turns the booking into an UPDATE of that row: now both transactions target the same row, snapshot isolation's write-write rule fires, and one aborts with a serialization error. Coarser variants work too — a per-room or per-day guard row that every booking bumps. The tradeoff is deliberate: you have manufactured contention, so the guard row's granularity sets your concurrency ceiling, and rows that are too coarse serialize unrelated work. ## Option 3 — lock the rows you read Promote the read to a locking read so that the check takes a lock that conflicts with concurrent writers. This converts the read-write dependency into something the lock manager can see. It works well when the invariant ranges over rows that *already exist* — the on-call doctors case, the two-account balance case. It does not by itself handle phantoms: locking zero rows locks nothing, which is exactly the booking situation, unless the engine takes range or predicate locks. That is why the booking example specifically needs option 1 or 2, or option 4. ## Option 4 — run true serializability Switch the transaction to a genuinely serializable isolation level. Serializable Snapshot Isolation keeps the non-blocking MVCC reads but tracks read-write anti-dependencies at runtime and aborts a transaction when a dangerous structure appears; strict two-phase locking gets there by blocking instead. Either way the anomaly becomes an abort, not a corrupt state. The non-negotiable companion is a **retry loop**: serializable isolation converts correctness problems into transient failures, and code that does not retry converts them into user-visible errors. The retry must restart the whole transaction from the beginning — re-reading, re-deciding, re-writing — because the point is to run against a fresh snapshot. It must be bounded and backed off, and the operation must be safe to re-execute. In practice that means the retry lives at the transaction boundary, not inside business logic, and side effects that cannot be undone (emails, payment calls) stay outside the transaction. ## Choosing A practical ranking: express it as a constraint if you can; otherwise, if the workload is high-throughput and the contended resource is naturally identifiable, materialize the conflict; if the invariant is complex, ad hoc, or spread across many statements, pay for serializable isolation and build the retry path once, centrally. Whichever you choose, write a concurrent test — the failure only appears under real overlap.