How do database engines actually implement SERIALIZABLE isolation, and what does each approach cost the application that uses it?

## Why implementation matters SERIALIZABLE is defined by an outcome — the schedule must be equivalent to some serial order — not by a mechanism. Two very different mechanisms deliver it, and they fail in opposite ways. Knowing which one you are on determines whether you tune for blocking or write retry code. ## Pessimistic: strict two-phase locking Two-phase locking (2PL) has a growing phase, where a transaction acquires locks, and a shrinking phase, where it releases them. *Strict* 2PL holds every lock until commit or rollback, which additionally guarantees recoverability — nobody reads data that might be rolled back. Row locks alone give you repeatable reads but not serializability, because a concurrent insert can add a row matching a predicate you already evaluated. To close that, the engine must lock the *predicate*, not just the rows. Real systems approximate predicate locks with index-range locks (often called next-key or gap locks): scanning `WHERE status = 'PENDING'` locks the index range covering `PENDING`, so an insert into that range must wait. Costs to the application: - **Blocking is the throughput ceiling.** A contended row or a hot index range effectively serializes those transactions. Latency percentiles, not averages, are where this shows up. - **Deadlocks are normal, not exceptional.** Two transactions taking the same locks in different orders form a cycle; the engine kills a victim. Mitigations: touch resources in a consistent order, keep transactions short, avoid user think-time inside a transaction, and still handle the deadlock error. - **Lock escalation and range locks surprise people.** Locking a range can block inserts of rows that do not exist yet — correct, but invisible in the table's current contents. ## Optimistic: serializable snapshot isolation SSI builds on multi-version concurrency control. Every transaction reads a consistent snapshot of committed data as of its start, so readers never block writers and writers never block readers. That alone gives snapshot isolation, which is *not* serializable. SSI adds detection. The theory result it relies on: every non-serializable execution under snapshot isolation contains a transaction with both an incoming and an outgoing **read-write dependency** (someone wrote data it read, and it wrote data someone else read) with those two conflicts adjacent in the dependency graph. The engine tracks read sets — using index range information so predicates are covered too — and when that dangerous structure appears, it aborts one of the participants with a serialization failure error. Costs to the application: - **Mandatory retry loop.** Any transaction can fail at commit with a retryable error. The retry must re-execute the transaction's logic, not just re-issue the commit, and side effects (emails, external calls) must not have escaped before commit. - **False positives.** Detection is conservative: some aborted transactions were in fact serializable. That is the price of tracking approximate read sets rather than exact predicates. - **Contention sensitivity.** Under high write contention on overlapping read sets, abort-and-retry becomes livelock-shaped throughput collapse. Backoff, shorter transactions and narrower read sets are the tuning levers. - **Long-running readers.** Read-only transactions extend the window in which dependencies must be tracked, increasing both memory for tracking state and abort probability elsewhere. ## Choosing and operating Guidance that holds either way: 1. **Keep transactions short and free of network waits.** This is the single biggest lever for both mechanisms. 2. **Shrink read sets.** A transaction that scans a whole table under SERIALIZABLE conflicts with almost every writer, whichever implementation you use. Add the index that lets it touch fewer rows or a narrower range. 3. **Write the retry loop once, centrally**, with jitter and an attempt cap, and make transactions idempotent so a retry is safe. 4. **Read-only transactions should be declared read-only.** Both families can then relax tracking; several engines fast-path them entirely. 5. **Consider the cheaper alternative.** Many invariants that people reach for SERIALIZABLE to protect are better expressed as a unique constraint, a check constraint, an atomic conditional update, or an explicit row lock on the one row that guards the invariant. Those cost far less than raising the level for the whole workload. Finally, measure. Lock-wait time and deadlock counts characterize the pessimistic world; serialization-failure rate and retry counts characterize the optimistic one. Whichever you run, that metric belongs on a dashboard before you rely on SERIALIZABLE in production.