How would you decide which isolation level the transactions in a production service should run at, and how do you keep that decision safe as the codebase grows?

## Framing the decision Isolation level is not a per-query optimization knob; it is a correctness policy for a codebase that many people will edit. The failure mode is never 'we picked the wrong level once' — it is 'nobody knows which level anything runs at, and a transaction added last quarter silently depends on a guarantee we do not provide'. So the goal is a small, explicit, testable policy. ## Step 1 — a documented default Pick one default and write it down next to the code that configures it. READ COMMITTED is the right default for the vast majority of services: it is the shipped default on most engines, it never shows uncommitted data, it does not pin a snapshot for the whole transaction, and it keeps lock hold times short. Anything else as a *global* default should be a deliberate, argued choice, because it changes the behaviour of every transaction including ones written before the change. ## Step 2 — find the transactions that actually depend on isolation The risky pattern is always the same: **read, decide, write** where the write's validity depends on the read still being true. Examples: check a balance then debit; count reservations then insert one; verify no row exists then insert. Sweep the codebase for that shape rather than for level names. For each such transaction, name the invariant in one sentence ('total reserved never exceeds capacity'). If you cannot state it, isolation is not your problem — the design is. ## Step 3 — prefer enforcement that does not depend on the level Ranked by cost and durability: 1. **Schema constraints.** Unique, check, foreign key, exclusion constraints. The engine enforces them under every isolation level, at every concurrency, forever. If the invariant fits a constraint, this is the answer. 2. **One atomic statement.** `UPDATE inventory SET qty = qty - 1 WHERE sku = ? AND qty >= 1` performs the read and the write in a single statement whose row lock is taken before the check is evaluated. Check the affected-row count instead of pre-reading. 3. **Explicit locking of the guard row.** Take a write lock on the aggregate/parent row that the invariant is defined over, so concurrent transactions serialize on exactly that row and nothing else. This is targeted and predictable. 4. **Raise the isolation level** for that transaction. Do this when the invariant spans rows or predicates that none of the above can cover, and accept the retry contract that comes with it. The reason for that ordering is maintenance: options 1–3 are visible in the code or schema at the point of use, and they keep working if someone changes the transaction manager's default. Option 4 is invisible at the call site and quietly breaks when configuration drifts. ## Step 4 — the retry contract Any transaction at a strict level can fail with a retryable error (serialization failure or deadlock victim). That is not exceptional; it is part of the contract. Requirements: a single shared retry helper with bounded attempts and jittered backoff; transaction bodies that are pure and idempotent, with side effects (emails, payment calls, message publishes) moved outside or made outbox-driven; and metrics on attempts so retry storms are visible. ## Step 5 — operational limits Whatever the level, these dominate real behaviour: - **Transaction duration.** Every lock hold, every pinned snapshot, every dependency-tracking window scales with it. No user think-time, no HTTP calls, no queue waits inside a transaction. - **Read-set size.** A transaction under a strict level that scans a large range conflicts with nearly everything. Index it so it touches a narrow range. - **Read-only declaration.** Marking read-only transactions as such lets engines skip tracking and often route them cheaply. - **Connection pool interaction.** Longer, stricter transactions occupy pool slots; contention can surface as pool exhaustion long before it surfaces as a database problem. ## Step 6 — keep it true over time - Configure the level in one place, declaratively, and make exceptions explicit and named at the call site. - Add a review question: 'this transaction reads then writes — what protects it?' with the answer being a constraint, an atomic statement, an explicit lock, or a named level. - Test it: concurrency tests that run the two conflicting paths against a real engine catch what unit tests structurally cannot. - Dashboard the four numbers — lock waits, deadlocks, serialization failures, oldest open transaction — and alert on trend, not threshold. ## The honest tradeoff statement A principal-level answer says out loud that there is no universally right level. Raising it globally buys correctness insurance and pays in throughput, retry complexity and tail latency; leaving it low buys throughput and pays in a class of bugs that only appear under production concurrency and are nearly impossible to reproduce. The resolution is not to pick a side but to move as many invariants as possible somewhere the level does not matter, and then make the residual choices explicit.