Two people tip the same writer in the same instant. Each request reads the balance, adds a tip and writes the new balance back. If both read 100, both write 110 instead of 120, and ten dollars is gone. Nothing failed and nothing was reported. That is a lost update, and it's what a database's transactions exist to prevent.
What can go wrong
A transaction groups reads and writes so they take effect together or not at all. Its promises are often summed up as ACID: atomic (all or nothing), consistent (the data's rules hold), isolated (from other transactions) and durable (kept once committed). This chapter is about the I. The trouble starts when two transactions run at the same time and touch the same data. The mistakes have names:
| Anomaly | What happens | Example |
|---|---|---|
| Dirty read | You read a change before it commits, which may still be rolled back | A balance shown while a transfer is still failing |
| Read skew | You read two rows on either side of another transaction's commit | A report where money has left one account and not yet arrived in the other |
| Non-repeatable read | You read a row twice and get two answers | A balance that changes between two looks at it |
| Phantom | You count the rows that match, twice, and a row appeared | An order that shows up in the second count |
| Lost update | Two read-modify-writes overlap and one change vanishes | Two tips to the same balance, one counted |
| Write skew | Two transactions each check something the other changes | Two doctors both go off call, since each saw the other on call |
Whether one happens depends on how the steps of the transactions happen to interleave. The lab below runs every possible order for you. T1 moves 50 between two accounts, and T2 reads both balances for a report.
Isolation levels
Isolation is how much a transaction is shielded from the others running beside it. Databases offer levels, and each one forbids more anomalies at a higher cost.
- Read uncommitted lets you see other transactions' unfinished changes.
- Read committed shows only what is committed, as of each read. This is the usual default.
- Snapshot (repeatable read) shows you the data as it was when your transaction began (PostgreSQL takes the snapshot at its first statement, not at
BEGIN), and aborts you if you try to change a row someone else changed since. - Serializable behaves as if the transactions ran one at a time. One way to get it, two-phase locking, is to lock everything you read, including the ranges a query searched so no new matching row can appear, and to abort one of two transactions that wait for each other. PostgreSQL does it differently: it lets them run, watches what each reads and writes, and aborts one as soon as they form a pattern no one-at-a-time order could give, at the latest at commit (serializable snapshot isolation). It errs on the safe side, so it sometimes aborts transactions that wouldn't really have clashed, and the app has to be ready to retry. The labs here use locking.
Here T1 reads a balance twice and counts open orders twice, while T2 changes the balance and adds an order.
Snapshot isn't the top of the ladder, though. Two doctors are on call, and at least one must stay. Each checks that the other is on call and then goes off call.
| Level | Typical default in | Still allows |
|---|---|---|
| Read uncommitted | almost nothing | dirty reads, and the rest |
| Read committed | PostgreSQL, Oracle, SQL Server | read skew, non-repeatable reads, phantoms, lost updates, write skew |
| Snapshot (repeatable read) | MySQL InnoDB, whose default repeatable read is close but doesn't abort the second writer, so it can lose updates; PostgreSQL offers it as "repeatable read", but not by default | write skew |
| Serializable | rarely the default; Oracle's "serializable" is really snapshot, so it allows write skew | nothing, at the highest cost |
The model here is a small one: it has row versions, locks and an abort on conflict, and leaves out indexes, gap locks and the details each database adds. It's enough to show what each level promises, and real databases differ at the edges.
Two tips at once
There are three ways to run a read-modify-write.
- No protection. Read, think, write. Overlapping transactions overwrite each other.
- Lock the row. Take a lock when you read, hold it until you commit. Overlapping transactions wait their turn. In SQL this is
SELECT ... FOR UPDATE. - Optimistic. Take no lock while you think. When you write, check that the row is the same as when you read it, and start over if it isn't. It's a version number or a compare-and-set.
Often there's a simpler fourth way: let the database do the arithmetic. UPDATE balances SET amount = amount + 10 reads and writes the row in one locked step, so there's no gap for another tip to fall into.
One busy row
A lock makes transactions on the same row queue, and a row can only be locked by one at a time. So a row's throughput is capped at one transaction per its own duration. A tip that locks the row when it reads holds it while the app thinks and until it commits: about 55 ms in all here, app think time included, or about 18 a second, however big the database is.
Giving up too early
An optimistic transaction that finds its row changed starts over, and the number of attempts is a setting. Too few and tips fail. More attempts help, until the row is so busy that retries keep colliding.
Locks that wait for each other
Locks bring a new way to fail. T1 holds a lock on one account and wants the other, and T2 holds the other and wants the first. Neither can move.
Holding a seat
Booking has a step tips don't: between choosing a seat and paying, a person takes minutes, and no transaction should stay open that long. The usual design is a hold: a row that reserves the seat for one buyer until an expiry, say ten minutes from now. Taking it is one short transaction: an update that succeeds only while the seat is free or its hold has expired, or an insert that a unique key on the seat limits to one hold per seat. Paying turns the hold into a booking. With the unique key, an expired hold still holds its row, so the next buyer's plain insert fails on it: either a job deletes expired holds, or the insert takes over an expired one (in PostgreSQL, INSERT ... ON CONFLICT (seat) DO UPDATE ... WHERE holds.expires_at < now()).
The expiry brings a race of its own: a payment can finish after its hold ran out and someone else took the seat. So each hold on a seat gets a version number one higher than the last (a fencing token), the booking carries its hold's number, and it's refused if a newer hold has replaced it. The buyer's payment is then refunded. The same pattern books tickets, hotel rooms and the last item in stock.
One change, two databases
Everything so far happens inside one database. When one change has to land in two, say a tip taken from a reader's wallet in one database and credited to the writer in another, no single transaction covers both. There are two classic answers.
- Two-phase commit (2PC). A coordinator asks each database to prepare: to make the change durable and promise it can commit. If all say yes, it tells them all to commit; if any says no, they all roll back. It's atomic, at a price: a prepared database holds its locks until it hears the outcome, so if the coordinator fails between the two phases, the others are stuck, locks held, until it comes back.
- Saga. Each step is a local transaction in its own database, and each has a compensation that undoes it, such as refunding the wallet if the credit fails. Nothing blocks, but there's no isolation between steps: others can see the money gone from the wallet before it reaches the writer, or before it's refunded.
In a saga, each step saves something and then tells the next one. That's the save-and-send problem a transactional outbox solves: the step writes its message to a table in the same transaction as its change, and a relay sends it afterwards (Queues and Workers covers it).
Deep dives
| They ask | What to say |
|---|---|
| Two people book the last seat | A lock on the seat row, or a unique constraint, so the second booking fails cleanly |
| Transfer between two accounts | One transaction touching both rows, locked in a fixed order so two transfers can't deadlock |
| A retry runs a payment twice | An idempotency key, stored in the same transaction as the payment |
| One celebrity account is hot | Split its balance into parts, or queue its writes and apply them one at a time |
| The database is serializable but slow | Shorten transactions: no network calls or waiting inside them |
| Two databases need one change | Avoid it, or use a saga or two-phase commit, and say what each costs |
One of those rows is worth trying. Two people book the last seat:
In the wild
The ideas in this chapter, and where you'll meet them in real systems and interviews:
| Idea | Where you'll meet it |
|---|---|
| Row locks held until commit | SELECT ... FOR UPDATE in PostgreSQL and MySQL |
| Optimistic checks | A version column, or DynamoDB's conditional writes |
| Isolation levels | Read committed as PostgreSQL's default, and snapshot isolation in Oracle and PostgreSQL |
| Serializable | PostgreSQL's serializable mode, and Google Spanner |
| Holds with an expiry | Ticket and hotel booking flows, and checkout carts that reserve stock |
| Two-phase commit and sagas | XA transactions and PostgreSQL's PREPARE TRANSACTION; sagas run by a workflow engine such as Temporal or AWS Step Functions |
Your practice
Inkwell Ledger credits tips to writers' balances, and some tips are being lost when two arrive for the same writer together. Stop the losses without failing tips, keep the busiest writer's wait short, and then give the database only the tier it needs.