An application must guarantee an invariant that spans several rows — for example that at least one doctor stays on call for each shift. What are your options for making concurrent transactions respect that invariant, and what does each one cost?

## The organizing principle Write skew happens because a transaction's correctness depends on rows it reads but does not write, and nothing revalidates those reads. So every fix does one of two things: bring the reads into the conflict footprint, or remove the read-then-decide step altogether. Judge any proposed fix by which of those it does; if it does neither, it only narrows the window. ## Option 1 — SERIALIZABLE On a serializable snapshot isolation engine, the database records the rows and index ranges each transaction read, watches for the dependency pattern that makes an execution non-serializable, and aborts one participant. The application code is unchanged except for a retry loop. Strengths: handles any invariant, including counts, aggregates, and rules involving rows that do not exist yet. No lock ordering to design. Readers still do not block writers. Costs and obligations: - Serialization failures are normal control flow, not errors. You need a bounded retry count with jittered backoff, and transactions that are safe to run twice from the caller's perspective (usually via an idempotency key). - Abort rate is a capacity metric. It rises with transaction duration and with how concentrated the contention is, and it includes conservative false positives from coarsened read tracking. - On lock-based engines, SERIALIZABLE instead means range locking — no aborts, but blocking and deadlocks. Know which one you are running on; the operational profile is completely different. ## Option 2 — explicit locking of the read set Take a locking read on the rows the decision depends on before deciding. The second transaction blocks, then sees the committed result and takes the other branch. Strengths: deterministic, no aborts, easy to reason about locally, and works on any engine. Costs: - Blocking, which is a throughput ceiling on the contended set. - Deadlocks unless every code path acquires the rows in the same order, so you still need a retry path. - It is a discipline, not a guarantee: one code path that reads without the lock reopens the hole. That includes admin scripts, migrations and any second service touching the table. - It only locks rows that exist. If the invariant concerns rows that might be inserted — double booking, at-most-one-active — you need range locking or, better, a constraint. ## Option 3 — materialize the conflict Introduce a row that stands for the contended resource: one row per shift, per time slot, per overdraft group. Every transaction whose correctness depends on the invariant must update that row (even trivially). Now the transactions write the same object, so the engine's ordinary write-write conflict detection or row lock covers them. Strengths: works under plain snapshot isolation, requires no special isolation level, and makes the contention explicit and visible in the schema. Costs: an artificial row and the obligation for every writer to touch it; a deliberately hot row that serializes the whole resource; and yet another discipline that a new code path can forget. Treat it as the fallback when you cannot run serializable and the invariant will not fit a constraint. ## Option 4 — declarative constraints When the rule is expressible over a single row or an index — uniqueness, non-overlapping ranges, a check on one row's columns — put it in the schema. There is no read-then-write window at all: the check and the write are the same index operation, enforced against every writer regardless of code path, ORM or isolation level. The application catches a constraint violation instead of racing. This is the strongest and cheapest option where it applies, and it is under-used. Its limit is expressiveness: "at least one row remains true in this group" and "the sum stays under N" are not index-shaped. A common redesign is to restructure the data so they become index-shaped — for instance, storing the on-call assignment as a single row per shift naming the on-call doctor, so "exactly one" is a property of one row rather than an aggregate over many. ## Non-fixes worth naming explicitly - **Re-reading just before the write** — under a snapshot the value is unchanged, and without one the other transaction can still commit in the remaining window. - **Shortening the transaction** — narrows the window, never closes it. - **Retrying without changing the mechanism** — the retry has the same blind spot. - **A stronger but still snapshot-based read level** — improves what you see, not whether it stays true. - **An application-level mutex or advisory lock** — can work, but it is the locking option with the enforcement moved outside the database, so it is even easier for a code path to bypass, and it fails open if the process holding it dies without releasing. ## How to choose Ask: does the invariant fit a constraint? Use it. Otherwise, can callers retry? Prefer SERIALIZABLE. If retries are unacceptable or the engine does not offer SSI, use locking reads with a designed lock order, or materialize the conflict when the read set is awkward to lock. Whatever you choose, write a test that runs the two transactions concurrently and asserts the invariant — this class of bug is invisible to single-threaded tests.