A hot table takes thousands of writes per second, and one code path must be able to trust that no other row matches its search condition before it inserts. Compare the ways to eliminate the phantom in that check — SERIALIZABLE with retries, explicit range locking, and pushing the rule into an index constraint — and explain how you would choose.

## Frame the problem correctly The code path is a read-then-write decision over a predicate: "nothing matches, so I may insert". Preventing the phantom means making that decision durable against every concurrent transaction. Every option below is a different place to pay for that: in the index, in aborted work, or in blocked work. ## Option 1 — push the rule into the index A **unique constraint** covers equality-shaped rules ("one active membership per user"). An **exclusion constraint** covers overlap-shaped rules ("no two bookings for a room with overlapping time ranges"), which are the classic case where a unique index cannot express the rule. Why it is usually the right default: - There is no window. The check and the write are the same index operation, so no isolation level is required to make it correct. - It is enforced for every writer regardless of code path, ORM, migration script or ad-hoc session. Application-level checks are only as strong as the least careful caller. - The cost is one index probe plus normal insert maintenance. Contention is per-key, not per-range. Limits: the rule must be expressible over the columns of one row (or one index expression). Rules that quantify over other rows — "at most N rows matching", "the sum stays under a limit" — do not fit and need one of the other options, or a redesign that materializes the aggregate into a single row that can be locked or constrained. ## Option 2 — SERIALIZABLE via serializable snapshot isolation SSI-style implementations run transactions optimistically on snapshots, track which key ranges each transaction *read*, and abort a transaction when a dependency pattern that could produce a non-serializable outcome is detected. Phantoms are covered because reads are tracked at range granularity, not row granularity. What it buys: correctness for arbitrary invariants, without blocking, and without you having to reason about lock ordering. What it costs, and what you must build: - **A retry loop is mandatory.** Serialization failures are a normal outcome, not an error condition. Transactions must be idempotent from the client's perspective — either naturally, or via a request key — and retries need bounded attempts plus jittered backoff to avoid a retry storm. - **Abort rate is your capacity metric.** It rises with transaction duration, with the width of ranges read, and with how concentrated writes are on a hot key range. Long transactions are the usual culprit: do external calls outside the transaction, keep the write set small, and never hold a transaction open across user think time. - **False aborts exist.** Conflict tracking is conservative and coarsens under memory pressure, so some aborts correspond to executions that were actually fine. That is acceptable in exchange for never missing a real conflict, but it means abort rate is not the same as true conflict rate. - **Latency becomes multi-modal**: fast path plus occasional retried path. Set SLOs on the retried distribution, not the mean. ## Option 3 — explicit range locking Take locks that cover the predicate's key range for the duration of the transaction. No aborts, deterministic ordering, easy to reason about locally. Costs: - **It is a throughput ceiling.** Everything touching that range serializes. On a hot table, that is the whole point of the exercise and also the bottleneck. - **The lock footprint follows the access path**, so an unselective or missing index silently widens it, sometimes to a full scan's worth of key space. - **Deadlocks** appear whenever two transactions acquire overlapping ranges in different orders, which means you still need a retry path — so you pay for both mechanisms. It is the right tool when contention is genuinely low but must be handled correctly, when latency must be bounded and retries are unacceptable, or when the range is naturally partitioned so each lock is narrow. ## Reducing contention regardless of mechanism - **Partition the key space** so hot ranges become many cold ones — per-tenant, per-day, per-shard prefixes. This shrinks lock ranges and conflict windows for every option. - **Keep transactions short**, since every mechanism's cost scales with hold time or read-tracking window. - **Move the invariant to a narrower object** when possible: a counter row you lock, or a queue slot you claim, is a single-row conflict rather than a predicate conflict. - **Consider whether the invariant must be synchronous.** Some rules tolerate detect-and-repair — accept the write, detect the violation asynchronously, and compensate — and that trade buys enormous throughput. It is a business decision, not a database one, and should be made explicitly rather than by accident. ## How to present the choice Rank by where the enforcement lives: index constraint (strongest, cheapest, least expressive) → SSI with retries (fully expressive, costs aborts) → explicit range locks (deterministic, costs concurrency) → asynchronous detection (cheapest at runtime, weakest guarantee). Pick the leftmost option the invariant actually fits, and be explicit about the operational obligations each one creates.