CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

When to rely on isolation level versus an explicit application check

A database-level CHECK constraint or explicit lock can sometimes solve the same problem more cheaply than raising isolation level everywhere.

When to rely on isolation level versus an explicit application check

Think of a store with two options. It could lock its entire inventory room every time anyone enters, or instead simply put a physical counter-lock on the one shelf that actually needs protecting, solving the real problem far more cheaply.

In plain words: a cross-row invariant vulnerable to write skew can sometimes be protected more cheaply than raising the whole transaction to serializable isolation: an explicit SELECT ... FOR UPDATE can lock the specific rows involved in the check upfront, or a database-level constraint, where expressible, can enforce the invariant directly regardless of isolation level. The right choice depends on specifics: how often the risky pattern actually occurs, how expensive an occasional abort-and-retry is for this particular feature, and whether the invariant can be expressed as a constraint at all. There is no single universally correct answer; the goal is matching the fix's cost to the actual risk.

A worked example. The on-call doctors invariant, "at least one doctor on call," might be enforced more simply with an explicit SELECT ... FOR UPDATE locking both doctors' rows before either check runs, rather than raising every transaction touching the doctors table to serializable isolation.

The key lines.

BEGIN;
SELECT * FROM doctors WHERE doctor_id IN ('A', 'B') FOR UPDATE;
-- locks both rows upfront, preventing the write skew pattern directly
UPDATE doctors SET on_call = false WHERE doctor_id = 'A' AND (SELECT COUNT(*) FROM doctors WHERE on_call = true AND doctor_id != 'A') > 0;
COMMIT;

Explicit locking versus isolation-level escalation

Explicit locking uses a targeted mechanism, such as SELECT ... FOR UPDATE or a database constraint, to protect a specific invariant, as an alternative to isolation-level escalation, raising the entire transaction's isolation level to prevent the same anomaly more broadly.

Example: a physical lock on one specific shelf, instead of locking an entire inventory room, mirrors explicit locking as an alternative to isolation-level escalation.

Remember this

Name the anomaly, pick the minimum isolation level, or targeted mechanism, that prevents it, and resist defaulting to serializable everywhere out of caution. This level's whole arc, from ACID through serializability to concrete protocols and anomalies, exists to make this specific, traceable decision possible.

Quick notes

  • Start by identifying the specific anomaly a piece of logic is vulnerable to, not by reaching for the strictest level by default.
  • Serializable isolation prevents every anomaly but at a real waiting or abort-and-retry cost most transactions do not need to pay.
  • Isolation-level overreach applies serializable broadly where it was never actually needed.
  • An explicit lock or database constraint can sometimes solve a specific invariant more cheaply than escalating isolation level.
  • The right choice always matches the fix's cost to the actual risk, which varies by feature and workload.