ACID, SOLID, and Design Patterns: A Complete Software Design Reference

Part 4 of 26 · ~5 min

Isolation and the Concurrency Spectrum

Isolation decides how much of a still-in-flight, uncommitted transaction another transaction running at the same moment is allowed to see. On a sold-out on-sale, "the same moment" isn't a hypothetical to reason about abstractly — it's the literal condition for every millisecond of the first two minutes. Leave transactions unisolated and concurrent ones collide in three specific, well-named ways:

Isolation isn't a toggle — it's a spectrum of isolation levels, and climbing each step trades away concurrency to rule out one more anomaly, right at the moment concurrency is what you can least afford to lose:

(Serializable's row isn't just "everything blocked" as a coincidence — the way engines typically deliver it is by making transactions behave as though they'd run one after another, in some order, rather than actually overlapping.)

Postgres, SQL Server, and Oracle all default to Read Committed, and it's the sensible resting point for most workloads — dirty reads are almost never acceptable, but the locking Serializable demands is usually more than the problem needs. Worth flagging directly, because it trips people who've only worked with one engine: MySQL's InnoDB defaults to Repeatable Read, not Read Committed — "most databases default to X" is a real oversimplification the moment InnoDB is in the room, and it's worth checking your specific engine's default rather than assuming.

-- A transfer of a hold that must see a stable snapshot of the seat's
-- status, immune to a concurrent hold landing mid-decision.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

SELECT status FROM Seats WHERE id = 214;             -- read once
-- ... application logic decides the seat is offerable ...
UPDATE Seats SET status = 'held' WHERE id = 214 AND status = 'available';
INSERT INTO Holds (seat_id, customer_id, expires_at) VALUES (214, 8831, datetime('now', '+3 minutes'));

COMMIT;

Snapshot isolation: what the table above leaves out

That ANSI-standard table is the one every textbook prints, and it's also, in practice, not quite what several real engines actually do — which is worth knowing before you trust it as a spec for any particular database. Postgres implements Repeatable Read using snapshot isolation: instead of locking rows as they're read, each transaction sees a consistent snapshot of the database taken at its start, and any row changed by a transaction that committed after that snapshot is simply invisible to it. One side effect of that implementation choice is that Postgres's Repeatable Read prevents phantom reads too — a guarantee the ANSI table above says Repeatable Read doesn't have to make. It's a genuinely good example of why "which isolation level am I using" and "which anomalies am I actually protected from" are two different questions, answerable only by checking your specific engine's documentation, not by memorizing the standard's table.

Two named bugs are worth knowing cold — they come up in interviews for good reason, and both show up constantly on a hold desk under load:

• Lost update — picture a customer's mobile app sending a "still checking out" heartbeat that extends their hold by ninety seconds, and a flaky connection causing that heartbeat to fire twice, back to back, as two separate requests. Both requests read the hold's current expires_at before either one writes anything back; both independently calculate "ninety seconds past that"; whichever UPDATE lands second simply overwrites the first one's result, using the same stale starting point the first request also used. The customer walks away with one extension's worth of time instead of two, and there's no exception, no log line, nothing to indicate a write ever went missing. A version column checked at write time closes the gap — the second UPDATE matches zero rows because the version it's expecting has already moved on, and the app can detect that and retry against the fresh value. An explicit row lock or a stricter isolation level work too, at the cost of making other holders wait.
• Deadlock — near the very end of the on-sale, two customers each try to trade seats with a friend across the room. Customer A's transaction grabs a lock on seat 12 (their own) and then reaches out for seat 45, which belongs to their friend and is held by customer B. Customer B's transaction, kicked off a heartbeat later, already has seat 45 locked and is now reaching for seat 12. Each transaction is holding exactly what the other one needs next, and neither can move forward. The engine eventually spots the cycle and kills one of the two transactions outright to break it — which is the reason application code inside any transaction has to treat "the database just aborted me" as a normal, expected outcome worth retrying, not as a bug.