At ten o'clock on a Saturday morning in July, Marlow Books, the four person online bookshop that exists only in this course, sold thirteen signed first editions. It had twelve.
Twelve was not a guess and it was not a cached number. There were twelve physical books in the back room, each with the author's name in ink on the title page, and the founder had counted them twice on the Friday evening before typing the number into the admin form. The newsletter went out at 10:00. About ninety people pressed the buy button over the following twenty seconds, call it four and a half a second, and the shop took thirteen orders before the stock guard started refusing anyone.
By Monday the founder was writing an apology for a book that does not exist.
This was harder to write than the apologies in April. Lesson 009 told the April story: the overnight distributor file corrected a title's stock from sixty to twelve at 06:00, the cache still held the sixty, and forty checkouts read the stale copy, said yes, and walked the row down through zero to minus twenty eight. That bug had a satisfying shape to it. Something was out of date, and lesson 010 fixed it in the first week of May by splitting the key, so the book page keeps a cached shell while anything that decides reads the real row by primary key in about two tenths of a millisecond. Lesson 011's routing audit deliberately kept that guard on the primary, because a read that decides cannot be answered by a copy that is behind.
So in July the checkout read the true row. All thirteen buyers were handed a positive stock count, fetched from the only authoritative machine in the shop, two tenths of a millisecond old.
The shop oversold anyway.
The founder's own summary, over the phone on Monday, was this: "I check the stock right before I take the money." That sentence is the entire bug. Right before is not the same as at the same time, and at Marlow the space between them turned out to be three hundred milliseconds wide.
What you are actually buying
A transaction is a boundary you draw around a group of statements so the database treats them as one thing. BEGIN, some work, then COMMIT, or ROLLBACK if you change your mind. Four promises live inside that boundary, and they are nowhere near equally interesting.
Atomicity says all of it happens or none of it does. Marlow's checkout inserts an order row and decrements a stock row; atomicity is what stops the shop from having an order for a book it never took off the shelf, or taking a book off the shelf for an order nobody placed.
Durability says that once COMMIT returns, the write survives the machine losing power in the next millisecond. Lesson 011 already showed you the mechanism without naming it this way: the write-ahead log is flushed to disk before the commit returns, and that same log is what the replica replays. Lesson 012 added the correction worth keeping, which is that durable and visible are different words. Your commit is durable on the primary and may not have reached the replica yet.
Consistency, the C, is the weakest letter in the acronym and the most over-explained. It means that if the database's declared rules hold before your transaction, they hold after it. Constraints, foreign keys, that sort of thing. It is doing almost no work in ACID, and worse, it is a completely different word from the consistency in lesson 012's replication lag, which is about whether two copies agree. Lesson 023 owns that second word. Do not let the letter confuse you into thinking ACID has anything to say about replicas.
Isolation is the promise about what other transactions running at the same time can see of yours, and what you can see of theirs. It is the only one of the four sold to you in tiers, with a default you did not choose, and it is where the money goes.
Atomicity and durability are what people mean when they say "we use transactions", and they are the two you will almost never get wrong, because the database delivers them whether or not you thought about it. Isolation you get less of than you assume.
Wrapping it in BEGIN changes nothing
On the Monday the founder went looking for the missing BEGIN. It was already there. The same lesson 011 audit that pinned the guard to the primary had also put it inside the transaction that writes the order, and that is the shape the code still had in July.
BEGIN;
SELECT stock FROM books WHERE isbn = '9780571358939';
-- application decides: stock > 0, so charge the card
INSERT INTO orders ...;
UPDATE books SET stock = stock - 1 WHERE isbn = '9780571358939';
COMMIT;
That is the fix everybody ships, shipped a month before the bug it was supposed to prevent, and it does not work. Not partially, not mostly. At the isolation level Marlow's Postgres is running, and yours almost certainly is too, a transaction does not stop another transaction from reading the same row. Two of these can run side by side, both read a 1, and both decide to sell.
That surprises people, and it should, because "isolation" is a word that sounds like it means alone in a room. What the default actually promises is far narrower: every statement you run sees a snapshot of data that was committed at the moment that statement started. Nobody's half-finished work leaks into your read. That is the whole guarantee. It says nothing at all about somebody reading the same row you are about to change.
There is a second problem in that block, and it is worse than the first. The card charge sits inside the boundary. Marlow's payment provider takes about three hundred milliseconds end to end, the same order as the hundred and fifty lesson 004 measured at Stagefront, the ticketing service in this course, and for those three hundred milliseconds this transaction is open, holding whatever it holds, waiting on a server that belongs to somebody else. Hold that thought. It comes back at the end and it is the most portable idea here.
Before the interleaving, one piece of precision, because the textbook name for this bug is not quite the bug Marlow has.
The classic lost update is when two transactions read a value, each computes a new one in application code, and each writes the answer back. Read 12, compute 11, write 11. Two of those and the row says 11 when it should say 10. One update is genuinely gone. The fix is to stop sending computed absolutes and let the database do the arithmetic: SET stock = stock - 1 rather than SET stock = 11.
Marlow already writes it the good way. The decrement is relative, the arithmetic is perfect, and the row after thirteen sales reads exactly minus one, which is the correct total of twelve minus thirteen. Nothing was lost. What went wrong is that the branch was taken on a value that had gone out of date in the three hundred milliseconds it took to charge a card. Call it a stale decision. Relative updates cure the lost update and leave this one completely untouched, which is why a shop can fix the first bug, believe it fixed both, and sell a signed book twice.
Four and a half a second, three hundred milliseconds wide
Here is the last copy going out the door twice. Times are from the July log, rounded to the millisecond.
10:00:04.000 A SELECT stock ... -> 1
10:00:04.001 A charge card, ~300 ms
10:00:04.180 B SELECT stock ... -> 1
10:00:04.181 B charge card, ~300 ms
10:00:04.302 A UPDATE stock = stock - 1 -> 0
A INSERT order, COMMIT
10:00:04.483 B UPDATE stock = stock - 1 -> -1
B INSERT order, COMMIT
Buyer A read a 1 at four seconds past ten. Buyer B read the same 1 a hundred and eighty milliseconds later, and B was right to, because A's decrement was still a hundred and twenty milliseconds in the future. Both readings were true when they were taken. Both were correct, fresh, primary reads of the authoritative row, and one of them sold a book that was already gone.
Now the number worth carrying out of today. The window is the time between reading the value you decide on and writing the decision down. Marlow's is about three hundred milliseconds: two tenths of a millisecond for the query, a round trip of roughly half a millisecond (lesson 010's hundred milliseconds of round trips divided across its 201 queries), and then the card. Buyers arrived at four and a half a second. Lesson 002's busy room, which lesson 010 used for connection counts, gives the rest: four and a half a second times three tenths of a second is 1.35 buyers inside the window at any moment.
That average assumes the presses were evenly spaced, and they were not. Nothing arrives evenly, which is why A and B in the log are a hundred and eighty milliseconds apart rather than the two hundred and twenty two the average implies. Real arrivals clump, so the average understates how often two land close together and the model is the optimistic one.
So the expected overshoot is about one copy, and the shop sold one extra copy. That is not a coincidence, it is the formula. The oversell is the arrival rate times the window.
Run that against the shop's ordinary business and you see why nobody caught it for two months. A mid-list title sells three copies a day, one sale every eight hours, so the arrival rate times the window is a number with four zeros after the decimal point. The bug is present on every checkout Marlow has ever served and it costs nothing until a title becomes scarce and popular in the same minute. Twelve signed copies and a newsletter did both at once.
The anomalies, and the one that is not on the list
Isolation levels are described by which of a short list of misbehaviours they let through. The list is worth knowing mostly so you can ask a precise question about your own default.
A dirty read is seeing a row another transaction has written and not committed. Imagine the founder at a psql prompt at 23:10 typing an UPDATE and then going to make tea before typing COMMIT: a dirty read would let a customer's page show that uncommitted price for the length of the tea break, including the case where the founder thinks better of it and rolls back. You can stop worrying about this one. Postgres does not implement the level that permits it at all; ask for read uncommitted and you quietly get read committed instead. It is the anomaly the textbooks lead with and the one you are least likely to meet.
A non-repeatable read is reading the same row twice inside one transaction and getting two answers. Marlow's checkout reads the books row twice: once for the stock guard, and again three hundred milliseconds later to copy the title and the price onto the order line. At read committed those are two separate snapshots, so a price change committed in between means the order line records a price the guard never saw. Same row, same transaction, two answers.
A phantom is running the same query twice and finding rows that were not there before. Not a changed row, a new one. Count the unsold seats in row F at Stagefront at 10:00:02 and get four; count again at 10:00:02.4 and get two, because somebody inserted two holds in between. This is the anomaly constraints are worst at, because there is no row to hang a constraint on. You are asserting something about rows that do not exist yet.
And then there is Marlow's. Read a value, decide, write. It is not on the list. The SQL standard defines its levels in terms of those three phenomena and no others, and every one of them is a statement about what a read is allowed to see. The July oversell is a statement about what a decision is allowed to be based on, and the standard has no vocabulary for it.
That is not pedantry. It is the reason a team can read the isolation table in the manual, pick a level, tick the box, and still oversell, because the thing that got them was never in the table.
The levels are a price list
Stop reading them as a taxonomy. Read them as three things you can buy.
| Level | What you get | What it costs |
|---|---|---|
| Read committed | each statement sees a fresh committed snapshot | nothing, and it permits the July bug |
| Repeatable read | one snapshot for the whole transaction | a retry loop you have to write |
| Serializable | the result matches some serial order | retries, and throughput under contention |
Read committed is the default in Postgres, Oracle and SQL Server. Every statement inside your transaction takes a fresh look at the world, so two reads in one transaction can disagree, and nothing stops a second transaction reading the row you are about to write. It costs you nothing, which is why it is the default, and it is the level that sold the thirteenth book.
Repeatable read freezes one snapshot at the start of the transaction and shows you that same world until you commit. MySQL's InnoDB engine defaults to this one, which is worth knowing if you move between databases and assume the defaults match. In Postgres this level goes further than the standard requires and stops phantoms too. It also does something the standard does not describe: if you try to update a row that somebody else has changed and committed since your snapshot, the database refuses, and your transaction dies with could not serialize access due to concurrent update.
Read that again, because it is the honest shape of the whole bargain. A higher isolation level does not do the work for you. It stops the database quietly handing you a wrong answer and starts it loudly handing you an error. Both of those need code. The difference is that an error is code you know you have to write.
Serializable promises that whatever happened, the outcome is one you could have got by running the transactions one after another in some order. Postgres implements it by watching for the dangerous patterns of reads and writes between live transactions and aborting one of them when it spots one. That catches the July bug, including the version where your decision rests on a row you only read and never wrote. The price is the same retry loop, paid more often, plus real throughput loss when many transactions fight over the same rows.
None of these is free and none of them is a setting you flip on a Friday. Lesson 043 owns how the snapshots are actually built.
What actually fixes it
Four options, cheapest first, and the cheapest is very cheap indeed.
Let the database do the deciding. One statement, no window, because the read and the write are the same operation:
UPDATE books SET stock = stock - 1
WHERE isbn = '9780571358939' AND stock > 0;
Then look at how many rows it changed. One means the book is yours. Zero means somebody got there first, and you have not charged anything yet. There is no gap for a second buyer to fit into, because there is no moment where the decision is made somewhere other than where the data lives. This works even at read committed, and the reason is a lovely piece of Postgres behaviour: when an UPDATE finds its target row locked by someone else, it waits, and when the lock clears it re-checks its own WHERE clause against the new version of the row. Buyer B's statement wakes up, sees stock > 0 is now false, and changes nothing.
Add the constraint. Lesson 009 noted, without dwelling on it, that there was no constraint in Postgres to stop the row going to minus twenty eight. A CHECK (stock >= 0) turns the thirteenth commit into a failed transaction instead of a negative number nobody reads. That is not a fix, it is a smoke alarm, and it does not stop the fire. It is also one line, and the shop went years without it, which tells you something about how these get skipped.
Lock the row on the way in. SELECT stock FROM books WHERE isbn = ... FOR UPDATE takes the row and holds it until you commit, so the second buyer waits rather than reading. This is the right tool when the decision genuinely needs several rows or several steps. Price it before you reach for it, though: every buyer now queues on that one row for the full length of your transaction, and if the card charge is still inside, that is three hundred milliseconds each. Twelve copies is 3.6 seconds in which nobody else can touch that row, and Marlow gets away with that only because the stock runs out. One row held serially serves 3.33 buyers a second against the 4.5 arriving, so the queue grows for exactly as long as there is something left to sell. Stock the sale with five hundred copies instead of twelve and all ninety buyers go through the lock: 27 seconds of queueing against 20 seconds of arrivals, and the last buyer waits seven seconds. Locking also brings deadlock. Two buyers taking four adjacent seats each, one grabbing them left to right and the other right to left, will each hold what the other wants; the database notices after a second and kills one of them. Lock rows in a fixed order, always, and that entire class of problem goes away. Lesson 035 has the distributed version, which is much worse.
Raise the level and retry. Set repeatable read or serializable, catch the serialization failure, and run the whole transaction again. It is the general answer, it handles the cases where your decision spans rows that a single clever UPDATE cannot cover, and it costs you a retry loop that has to be genuinely safe to run twice. Lesson 019 owns that word.
Marlow shipped the first two on the Tuesday. Twenty minutes of work, including the migration.
Never hold a decision open across somebody else's network
The last one reshapes the problem instead of patching it, and it is the thing worth taking to your own service.
The reason Marlow's window was three hundred milliseconds rather than under one is that a third party's API call was sitting inside the decision. It was inside the transaction as well, which cost the shop a held row for the duration, but that is the smaller sin. The window is the gap between reading and writing whether or not there is a BEGIN around it. Every millisecond a payment provider spends thinking is a millisecond your stock count is a rumour.
Stagefront, where two hundred thousand people press the same button at ten in the morning, has never been allowed to do this, and now you can say exactly why. Lesson 007 put Stagefront's seat holds in the database rather than in a cache, because oversold seats are a lawsuit. What that hold actually is, in the vocabulary of today, is the decision and the payment taken apart.
Clicking a seat runs one short transaction that either claims the seat or does not. It talks to nothing outside the database and it is over in a millisecond. The buyer then goes off to find their card, which takes a minute and a half, and during that minute and a half no transaction is open and no row is locked, because the claim is already committed as data. When the payment comes back a second transaction turns the hold into a sale. Two fast decisions with a slow, unreliable world in between them, and nothing held across the gap.
Now price the alternative with figures lesson 004 already published. Stagefront's checkout tier makes five hundred payment calls a second at peak, and the provider costs about a hundred and fifty milliseconds each, which lesson 004 turned into seventy five calls in flight at any moment. Do the charging inside the transaction and that is seventy five open transactions holding seat locks, all of them blocked on a machine in somebody else's data centre, at the exact minute of the year the business exists for. One slow afternoon at the payment provider and Stagefront's database is full of transactions that are not doing anything.
What this shape costs you is honest and worth saying. A hold that is never confirmed has to expire, so you now own a timer and a sweeper, and every ticketing site's "your seats are reserved for 4:59" is that timer made visible. And the charge can succeed while the confirmation fails, which leaves money taken and no seat, the exact half-failed network call lesson 004 described and lesson 019 fixes. You have not made the problem disappear. You have moved it out of the database, where it was a lock queue, and into the application, where it is a state machine you can reason about at three in the morning.
Lesson 039 takes this across machines, where there is no single database to commit against and the compensation has to be written by hand. Today's version, on one machine, is the cheap one, and it is the one you should reach for long before anybody says the word saga.
Recap
A transaction is a boundary you draw around a decision. Atomicity and durability come for free and are the two you will rarely get wrong. Isolation is the one with a dial on it, set to something weaker than you assume, and the C in ACID is a different word from the consistency of replicas in lesson 012.
Right before is not the same as at the same time. A read that decides is only as good as the gap between it and the write that acts on it. Marlow read the true row from the primary, two tenths of a millisecond old, and still sold a book it did not have.
The oversell is the arrival rate times the window. Four and a half buyers a second against a three hundred millisecond window is 1.35 in flight and one extra copy sold. The same code on a title selling three a day is wrong roughly never, which is why this survives two months of production.
The anomaly that gets you is not on the standard's list. Dirty reads, non-repeatable reads and phantoms are all statements about what a read may see. Yours will be a statement about what a decision was based on, and no isolation table has a column for it.
A higher isolation level does not do the work, it changes the failure. Read committed hands you a wrong answer quietly. Repeatable read and serializable hand you a serialization error loudly and make you write the retry. Both need code; only one of them tells you so.
Never hold a decision open across somebody else's network. Claim it in one fast transaction, do the slow unreliable thing with nothing locked, then confirm it in a second. Stagefront's seat hold is that pattern, and the timer and the half-failed charge are what it costs.
Check your understanding
Marlow's fix was
UPDATE books SET stock = stock - 1 WHERE isbn = ? AND stock > 0, checking the affected row count. Explain why this is safe at read committed, and then describe one checkout rule the shop might add next year that this single statement could not express.A colleague proposes fixing the July bug by setting the whole application to serializable and leaving the code alone. Say what breaks on the first busy Saturday, and what has to be written before that setting is safe to ship.
Take the July interleaving above and change one thing: the payment provider now answers in 20 milliseconds instead of 300. Work out the new expected overshoot at four and a half buyers a second, and say whether you would now call the bug fixed.
Stagefront holds a seat for a few minutes while the buyer finds their card. Describe what has to happen when the payment provider returns success after the hold has already expired and the seat has been sold to somebody else, and name the two lessons that own the pieces of your answer.
A reporting query runs for forty seconds inside a transaction at repeatable read, summing a month of Marlow's orders while orders keep arriving. Say what total it returns and why, then say whether that is the answer the founder wants, and what you would change if it is not.
Next lesson
016 SQL, NoSQL and Blob Storage: Picking the Right Shelf. Today's answer to almost every correctness question was to make the row say no; next lesson asks which stores can make that promise at all, which ones charge differently for it, and where Marlow's 216 gigabytes of cover images should have been living all along.