Transactions and Isolation Levels, Explained Through Four Incidents

Isolation levels are the part of the database everyone was taught in a lecture, promptly forgot, and then re-learned at production temperature — usually via an incident with a name like "the double refund" or "the negative inventory". I'm going to teach them the way the incidents taught me: not as an ANSI table of anomaly names, but as four concrete bugs, each with the setting or pattern that kills it.

Ground truth: what your database actually defaults to

Postgres defaults to Read Committed; MySQL/InnoDB to Repeatable Read — and both implement them via MVCC (readers see a snapshot; writers don't block readers), which is why "readers block writers" folklore from the SQL Server 2005 era doesn't apply. The defaults are fine. The bugs below happen within the defaults, because isolation levels only govern what you can see — not the correctness of read-then-write logic. That distinction is the whole post.

Bug #1: the lost update (read-modify-write)

// Two requests, same wallet, same instant. Both read balance=100.
$wallet = Wallet::find($id);          // A reads 100      B reads 100
$wallet->balance -= 30;               // A computes 70    B computes 60
$wallet->save();                      // A writes 70      B writes 60 — A's debit vanished

No isolation level in the default range saves you — both transactions read committed data and wrote valid rows. The fixes, in order of preference: atomic writes (UPDATE wallets SET balance = balance - 30 WHERE id = ? AND balance >= 30 — push the arithmetic into the database and check affected-rows); pessimistic locking where the flow is genuinely read-decide-write (Wallet::lockForUpdate()->find($id) inside a transaction — second reader waits); or optimistic versioning (a version column checked in the WHERE, retry on zero rows) when contention is rare and holding locks is rude. The wallet gets pessimistic; the CMS article gets optimistic.

Bug #2: the phantom check (check-then-insert)

"Only one active subscription per user": code checks exists(), finds none, inserts. Two concurrent requests both pass the check — snapshots hide each other's uncommitted inserts — and now there are two. Isolation levels below Serializable cannot fix this, and the real fix doesn't want them to: a partial unique index (CREATE UNIQUE INDEX ... ON subscriptions(user_id) WHERE status = 'active') makes the invariant the database's job — one insert succeeds, the other gets a constraint violation you catch and handle. The general law, which I also preach in the locks post: uniqueness invariants belong in constraints, not application checks.

Bug #3: the inconsistent report

A reconciliation job reads orders, then reads payments — while writes land in between — and the totals don't reconcile because the two reads saw different moments. Under Read Committed, each statement gets a fresh snapshot; the fix is asking for a transaction-long one: run the job at Repeatable Read (one SET TRANSACTION line), and every read shares a single consistent snapshot regardless of concurrent writes. This is the legitimately great use of stepping up a level — read-heavy multi-statement consistency, no locks needed. (MySQL folks get this by default; know that you're relying on it.)

Bug #4: the constraint that lives across rows

"Sum of splits must equal the invoice"; "at most 10 seats per booking window" — invariants spanning multiple rows that no unique index can express. This is Serializable's actual job: the database detects dangerous interleavings and aborts one transaction with a serialization failure. The contract most teams miss: Serializable requires a retry loop — aborts are the mechanism, not an error. Wrap the transaction in retry-with-jitter (Laravel: DB::transaction(fn () => ..., attempts: 3) handles deadlocks similarly). Use it surgically, on the specific flows with cross-row invariants; running the whole app Serializable is a self-inflicted throughput incident.

Interlude: deadlocks are scheduling, not corruption

Two transactions locking rows in opposite orders → database kills one → your job is to retry (they're transient) and to reduce: lock rows in a consistent global order (sort IDs before lockForUpdate), keep transactions short — and above all, never hold a transaction across network calls. The transaction that calls a payment API mid-flight is both a deadlock magnet and a connection-pool arsonist; do the slow thing first, transact the state change after, and reconcile failures via idempotency.

The cheat sheet

Counter/balance updates        → atomic UPDATE with guard, check affected rows
Read-decide-write flows        → SELECT ... FOR UPDATE (pessimistic), short txn
Rare-conflict edits            → version column, optimistic retry
"Only one X" invariants        → (partial) unique constraint, catch violation
Multi-read consistency (jobs)  → Repeatable Read for that transaction
Cross-row invariants           → Serializable + mandatory retry loop
Everything                     → short transactions, no I/O inside, retries with jitter

Notice how rarely the answer was "change the global isolation level" — the craft is matching each flow to the cheapest tool that makes its specific race impossible. The database has been offering these tools for forty years; the incidents happen in the gap between the lecture and the muscle memory. Consider this post the bridge.

Got a "sometimes the numbers are wrong" bug that survives every log dive? Nine times in ten it's on this page — bring the flow, I'll bring the WHERE clause.