Learn

Architecture & pattern guides

Read the idea, build it on a Grid, prove it in a challenge. Short, practical guides — illustrated with RodGrid's own nodes — on system design, design patterns, flow modeling, and how to get the most out of the app.

All articles

Transactions & isolation levels

What a transaction actually promises, the three read anomalies plus the lost update and write skew, and when SERIALIZABLE is worth its price versus a conditional update.

Two guests, one room, two confirmations

A hotel has one Deluxe room left for 14 March. Two guests press Book a few milliseconds apart, the requests land on two instances of the booking API, and the code in both looks careful: open a transaction, check availability, write, commit.

  1. API (a) opens a transaction and runs SELECT available FROM inventory WHERE room_type = 'deluxe' AND night = '2027-03-14'. It reads 1.
  2. API (b) opens its own transaction and runs the same SELECT. It also reads 1, because (a) has not written anything yet.
  3. API (a) sees 1 > 0, runs UPDATE inventory SET available = 0 … for that row and commits.
  4. API (b) also saw 1 > 0, runs the same UPDATE and commits.
  5. Both guests receive a confirmation for the same room.

Nothing failed and nothing was logged. The row ends at 0, as if one room had been sold, and two were. This is a lost update: (b) wrote a value computed from a read that was already stale, and (a)'s write disappeared under it. It happened inside transactions, at READ COMMITTED, the default level in PostgreSQL.

What a transaction promises

A transaction groups several statements so they land as one unit. ACID is the four-part promise: atomic (all or nothing), consistent (constraints hold before and after), isolated (concurrent transactions don't see each other's half-done work), durable (once committed, it must survive a crash). Atomicity and durability come from the engine's logging and recovery machinery. The interesting knob, the one that just sold a room twice, is isolation, and it is a dial, not a switch. (The C in ACID is about a database's own rules holding; it is not the C in CAP, and Consistency models: strong vs eventual (CAP) keeps the two apart.)

Isolation is defined by which read phenomena it forbids. A dirty read sees another transaction's uncommitted write. A non-repeatable read re-reads a row inside one transaction and gets a different value because someone committed in between. A phantom re-runs a range query (WHERE room_type = 'deluxe' AND night BETWEEN '2027-03-14' AND '2027-03-16') and gets a different set of rows, such as one that wasn't there before. Each level buys protection against more of these, and pays for it in locks, retries or version tracking. The trace above shows that this list is not the whole story: the lost update, and write skew further down, do their damage in what gets written, not in what a read sees.

  • READ COMMITTED — no dirty reads; every statement sees a fresh snapshot. The default in PostgreSQL and Oracle. Cheap, and enough for most CRUD.
  • REPEATABLE READ — a row read twice in one transaction reads the same. PostgreSQL implements it as snapshot isolation: the transaction's plain reads all see one snapshot instead of a fresh one per statement. It is the default in MySQL InnoDB, whose version has engine-specific differences (see the note below). Non-repeatable reads vanish; under snapshot isolation, write skew (two transactions each read, both decide, both write disjoint rows, the invariant breaks) does not.
  • SERIALIZABLE — the outcome equals some serial order (as if the transactions had run one at a time), so none of the three phenomena, phantoms included, can appear. Postgres implements it optimistically (SSI): no extra blocking, but a transaction can fail at commit with a serialization error and must be retried — a cost your code has to pay.

Would a stricter level have saved the room? It depends on the engine, which is a first warning about these names. PostgreSQL at REPEATABLE READ will not let (b) update a row that another transaction changed after (b)'s snapshot was taken, so (b) fails with a serialization error and the code must retry. MySQL InnoDB at REPEATABLE READ lets (b)'s UPDATE act on the latest committed row, so an UPDATE that writes a value computed from the earlier read still loses the update. SERIALIZABLE closes the race in both, at the price of retries.

A cheaper fix: put the check inside the write

Here is a better mental model: isolation is insurance against a race you can name. If you can't name the race, don't buy the level. Most bookings and stock decrements are a single invariant on a single row, and a conditional update at READ COMMITTED closes that race exactly: UPDATE inventory SET available = available - 1 WHERE id = ? AND available > 0, then check the affected-row count, and insert the booking row in the same transaction so that both commit or neither does.

Replay the trace with it. Whether (b)'s UPDATE waits for (a)'s row lock or simply runs after (a) commits, its WHERE clause is evaluated against the row as (a) left it. available > 0 is false, the update affects 0 rows, and (b) tells its guest the room has just sold out. The database serialises writers to that row for you; no lock table, no retry loop.

One row, one invariant: a conditional update instead of SERIALIZABLE

Two API instances race for the last unit; the row-level write lock lets exactly one UPDATE affect a row.

A lock service in front of the database is the heavier answer to the same race, and even then the resource has to check what it is told; Distributed locks & leader election explains why. Here, the condition in the WHERE clause is that check.

When the rule spans more than one row

The same hotel's staff app enforces another rule: at least one duty manager stays on call each night. Ana and Bruno are both on call tonight, and both ask to go home at the same moment.

  1. Ana's request counts tonight's managers on call: 2.
  2. Bruno's request, in its own transaction, runs the same count: 2.
  3. Ana's request sees 2 > 1 and sets Ana's row to off call.
  4. Bruno's request also saw 2 > 1 and sets Bruno's row to off call.
  5. Both commit. Nobody is on call tonight.

Each transaction wrote a different row, so there was no conflicting write for the database to notice. Snapshot isolation happily lets two managers each see the other still on the rota and both go home; trusting REPEATABLE READ to enforce a rule like this is a common mistake. This is write skew, and a conditional update cannot see it either, because the invariant spans rows the update does not write. So make the database see the race:

  • A constraint, when the rule can be written as one. A UNIQUE or EXCLUDE constraint rejects the second write whatever the timing. "At least one on call" cannot be expressed this way, but rules shaped like "no two X for the same Y" often can.
  • A lock on a row both transactions must touch. SELECT … FOR UPDATE on the rows the rule reads makes Bruno's request wait for Ana's to finish; at READ COMMITTED it then reads those rows as Ana left them, decides, and writes in the same transaction. (At PostgreSQL's REPEATABLE READ the wait ends in a serialization error to retry instead.)
  • Materialise the conflict into a row you do write: a per-night managers_on_call counter, decremented with WHERE managers_on_call > 1. The rule turns back into the single-row race, and the conditional update closes it. The hotel's inventory row is this trick already: if availability were computed by counting booking rows, two guests who each counted 9 of 10 and inserted a booking would be write skew too.
  • Or run SERIALIZABLE and retry.

If you choose the last option, do not expect SERIALIZABLE to remove the retry. Under SSI it creates the retry: a serialization failure is the normal, expected outcome of a collision, so the code that runs the transaction needs a loop around it, or the guest sees an error for what was only bad timing.

NoteIsolation levels are the same word with different meanings across engines: Oracle's SERIALIZABLE is snapshot isolation, MySQL's REPEATABLE READ locks gaps on some statements and not others. State the engine before you state the level.

How far a transaction should reach

Back at the booking, the next step is charging the guest, and putting the payment call inside the same transaction looks tidy. It is a trap. A transaction that spans a call to a payment provider holds its locks for as long as the provider takes, hundreds of milliseconds on a good day, and every other booking for that row queues behind it; a slow third party becomes a database pile-up. Worked design: hotel booking shows the usual shape instead: hold the room in one short transaction, pay outside it, then confirm in another.

A transaction lives inside one database. Once the booking is in one service and the payment in another, no single local transaction covers both. Cross-database atomic commit (Two-phase commit) exists, at a price: an extra round of coordination on every commit, and participants that can be left waiting if the coordinator fails mid-protocol. The usual substitute is a Saga, a sequence of local transactions with a compensating action (refund, release the hold) for each step a later failure could strand. A saga is not atomic: it aims for all or nothing in the end, but only as far as each compensation succeeds and can undo its step (a refund is not an un-charge), and meanwhile intermediate states are visible to other requests, which is why every step must be safe to retry (see Idempotency & exactly-once illusions).

Draw the last-room race

Open the Hotel Booking System challenge and focus on one thing: draw the last-room race as two API instances hitting the same inventory row, and write the conditional-update predicate on the edge. A good run names the level (READ COMMITTED) and why it is enough. Event Ticketing is the same race at ten thousand concurrent buyers, where every writer queuing on that one row makes it a hot spot to design around; Two-Phase Commit Flow shows what the cross-database alternative costs.

Practice this