Checkpoint: Transactions & Concurrency

You opened this section with a single transaction and the comforting acronym ACID, and a vague sense that a database mostly keeps its promises.

Since then you have watched a lost update quietly eat thirty dollars. You have proved that "repeatable read" means three different things and that Postgres will not hand you a dirty read no matter how you ask. You have raced two writers for one row and found that contention decides who wins. You have hit the wall where a transaction cannot span two databases, and met its two escapes — one that blocks and one that gives up isolation. You have made an event survive a crash, and then made a consumer survive the duplicate that survival costs you. And you have watched a naive flash sale sell 149 of 100 units — then fixed it with a single line that was also the fastest thing on the board.

Ten lessons. A whole cabinet of tools — locks, isolation levels, 2PC, sagas, outboxes, idempotency keys, atomic writes.

And every one of them is an answer to the same question.

Here is the one sentence the whole section was building toward.

A transaction is a promise the database makes to each user: "you are the only one here." Concurrency is the truth that they are not.

Every tool you learned is a different way to keep that promise while the truth leaks in — and there are only ever two moves:

When two operations want the same data at once, do you make them TAKE TURNS, or let them RACE and reconcile?

• Take turns — serialize them: a pessimistic lock, two-phase commit, SERIALIZABLE. You get correctness and isolation… and you block. Throughput collapses, and a single frozen participant (a dead coordinator, a stuck leader) takes everyone down with it. Wait more → then unavailable.
• Race and reconcile — let them run and clean up after: optimistic compare-and-set, the atomic conditional write, a saga's compensations, at-least-once delivery plus an idempotent consumer. You get speed and availability… but you must detect the conflict and retry, or compensate the half-done work, or dedup the duplicate. Wait less → then wrong, unless you do the cleanup.

There is no third move, and no free one. That is not a limitation of any particular database; it is the shape of the problem, and it has a name:

You cannot have full isolation and full concurrency.

Serializability means everyone runs as if alone; throughput means everyone runs at once; those pull in opposite directions. The isolation ladder is the price list, and the anomalies are exactly what you bought by turning it down — which is why the level names don't matter, only which anomalies leak. And when you can, the cheapest way to buy correctness is not a bigger lock at all — it is a smaller, atomic operation, pushed down to where the data lives.

Read this table slowly. It is the section, restated in one line each — and the point is not the ten rows, it is that they are all the same row.

the lesson	…is really asking
ACID, Precisely	What does "you're alone" even promise? A and D survive failure (the WAL's two halves); I survives concurrency — a dial, off by default; C is your invariant.
Isolation Levels & Their Anomalies	Exactly how much of the "you're alone" lie do you enforce? Each rung = which anomalies you stop telling. The names lie; write skew is the leak snapshot isolation can't plug.
Pessimistic vs Optimistic Locking	Take turns (lock first) or race and check (compare-and-set)? Contention decides — and it decides asymmetrically.
The Distributed Transaction Problem	The same choice, when the data lives on two machines — where there is no shared lock and no shared commit. Coordinate, compensate, or avoid.
Two-Phase Commit	Take turns, globally. Correct and isolated — until the coordinator dies and everyone blocks, holding locks.
Saga	Race and reconcile, globally. Never blocks — but each step commits, so isolation is gone, and you undo with compensations, not rollbacks.
Outbox + CDC	How do you tell the world "it happened" without a second write that can tear off? One atomic commit + a relay → at-least-once.
Idempotency as a Discipline	Since delivery is at-least-once, make repetition harmless — so a race you couldn't prevent becomes a duplicate you don't care about.
Flash Sale: Inventory Under Contention	All of it, on one hot row, at once. The fix is a smaller atomic op, not a bigger lock.
Codelab: Trigger Isolation Anomalies	Prove it with your own hands. Reads fixed for free (a snapshot); writes fixed by an abort you must retry.

Every row is the same trade: hold the line on correctness and pay in waiting, or let go and pay in cleanup. The whole art of this section is knowing, for a given piece of data, which bill is cheaper — and noticing when the answer is neither, because a set, a client-supplied id, or a one-line atomic write dissolves the conflict before it starts.

Enough theory. Here are four scenarios, each with a hard constraint, and the whole toolbox you have spent ten lessons filling.

A warning before you start: not one of the four answers is the one you will reach for first. Each scenario opens with the safe-sounding instinct — a lock, two-phase commit, "exactly-once delivery," a higher isolation level — and each of those instincts is a trap the section named out loud. Pick a strategy, watch it hold or break, and find the call that actually works.

Flash sale — one hot row. Your instinct is a lock: SELECT … FOR UPDATE. It's correct — and the slowest thing on the board (1322 tps), because every buyer queues on the row across a round trip. The naive read-check-write is worse: it oversells (149 for 100). The answer is a smaller operation, not a bigger lock — UPDATE … WHERE stock > 0 does the check and the decrement in one atomic statement, correct and the fastest (3088 tps).

Money across two services. Your instinct is two-phase commit — atomic, safe. But 2PC blocks the moment the coordinator dies mid-decision, with everyone holding locks. Since you can tolerate brief inconsistency and can't hold locks across the flow, the answer is a saga: local commits plus compensations, available at the cost of isolation. (And "just retry" double-charges — retry is only safe on top of idempotency.)

At-least-once consumer. Your instinct is to trust the broker's "exactly-once," or to dedup by hashing the payload. But exactly-once delivery is impossible, and two customers legitimately ordering the same item share a payload — hashing would merge two real orders into one. The key is the intent, not the bytes: an idempotency key plus an atomic dedup gives effectively-once.

At least one doctor on call. Your instinct is to raise the isolation level to REPEATABLE READ. But that's snapshot isolation, and write skew slips right through it — both doctors read "2 on call," both leave, on_call = 0, no error. Only SERIALIZABLE watches the read/write dependencies and aborts one.

⭐ Look at the shape of the four answers: a smaller atomic op, a saga, an idempotency key, and true serializable. Three of the four dissolved the conflict or reconciled after it; only the last one took turns. The instinct — lock it, coordinate it, raise the level — was the trap almost every time.

Eight calls. Every wrong option is a trap this section warned you about by name — they are lifted straight from the ten lessons' Mental-Model Corrections. If one of them looks obviously right to you, that is worth knowing before it's a production incident.

Pass mark: 6 of 8.

Concretely — things you could not do ten lessons ago:

• Separate the four letters of ACID for real — A and D are failure (the WAL's two halves), I is concurrency (a dial that ships turned down), C is your invariant — and never again say "atomic" when you mean "isolated."
• Read an isolation level by what it leaks, not what it's called — and know that Postgres has no dirty read, its REPEATABLE READ blocks phantoms, and nothing but SERIALIZABLE stops write skew.
• Choose pessimistic vs optimistic from the contention, not from taste — optimistic for low conflict, pessimistic (or a smaller atomic op) once the retries start to thrash.
• Recognise the distributed-transaction wall on sight — no cross-database commit, no shared lock, retry is a double-apply — and pick coordinate / compensate / avoid deliberately.
• Name 2PC's real flaw (it blocks, holding locks, on coordinator death) and a saga's real price (no isolation; compensations, not rollbacks).
• Stop chasing exactly-once delivery and build effectively-once instead: at-least-once + an idempotent consumer keyed on the intent, not a payload hash.
• ⭐ Reach for a smaller atomic operation before a bigger lock — and know, with a number, that it can be both the correct answer and the fast one.
• Expect the abort. At the levels that keep you correct under write contention, serialization_failure (40001) is not a bug — it's the design working, and your app needs a retry loop.

• ⭐⭐⭐ The law: a transaction promises "you're alone"; concurrency is the truth you're not. Every tool is one of two moves — take turns (serialize → block) or race and reconcile (optimistic / atomic / saga / idempotency → retry, compensate, dedup). There is no third move and no free one.
• ⭐⭐⭐ You cannot have full isolation AND full concurrency. The isolation ladder is the price list; the anomalies are the bill. The level name is not a portable guarantee — only which anomalies leak.
• ⭐ The winning move is usually a smaller atomic op, not a bigger lock. UPDATE … WHERE stock>0 was correct and the fastest (3088 vs the lock's 1322 tps). Push correctness down to the data.
• ⭐ Snapshot isolation is not serializable. Write skew slips through REPEATABLE READ with no error (on_call = 0); only SERIALIZABLE catches it (on_call = 1).
• Distributed atomicity has no free version. No cross-database transaction exists; 2PC blocks on coordinator death; a saga drops isolation and undoes with compensations, not rollbacks.
• Exactly-once delivery is a myth; effectively-once is the craft. At-least-once + an idempotent consumer (keyed on intent). And whatever you let race, be ready to retry — 40001 is the design working.

That closes Transactions & Concurrency — how a system stays correct when everyone touches the same data at once. Next: §7 — Storage Systems. You've spent this section deciding who may write what, when; now you go one layer down, to how the bytes are actually laid out on disk — B-trees and LSM-trees, the write path, and why the shape of your storage engine quietly decides everything you just learned to reason about.