Episode 25 · One room, two confirmations

Transactions and ACID: never double-book the last room

Monday, 8:50: Ben arrived at Hotel Lumen in Lisbon on Saturday night and his room was taken. He and Ana booked the last double for 14 March 50 ms apart, and both got a confirmation.

Two guests click Book on the last room at the same instant. Here's what the database promises, and the gap where double bookings sneak in.

30 min intermediateTransactions & ACIDIsolation levels and concurrency anomaliesRow locks and SELECT ... FOR UPDATEDatabase constraints as enforced rulesPartial failures

Problem
One room is left for 14 March. Two guests press Book within the same 50 ms. Separately, a booking must decrement inventory, insert the booking and record the payment, all or nothing.
Why the simple system fails
Without transactions, a crash between steps leaves half-written data (payment recorded, no booking). With transactions but the default isolation and a naive 'check then insert', both concurrent requests see one room left and both succeed.
Engineering concept
Group related writes into a transaction (all or nothing, durable once committed) and control what concurrent transactions can see and change: lock the rows you're deciding on, use constraints, or run at Serializable and retry.
Technology
BEGIN/COMMIT/ROLLBACK, PostgreSQL MVCC, isolation levels (Read Committed, Repeatable Read, Serializable), SELECT … FOR UPDATE, conditional UPDATEs, unique and exclusion constraints, the write-ahead log.
Trade-offs
Stronger isolation and locking prevent anomalies but add waiting, deadlocks and retries; weaker isolation is faster but pushes correctness onto your query design. Long transactions hurt everyone.

The problem

2 min

Monday, 8:50. Ben's complaint arrived overnight.

Maya · Product manager
Ben arrived at Hotel Lumen, Lisbon on Saturday and his room was taken. Ana had it. Both got a confirmation.
Omar · On-call SRE
The last double for 14 March, booked 50 ms apart. 6 more like this last month.
You · Engineer at StayHub
That can't be. The booking code is wrapped in BEGIN / COMMIT.
Lina · Senior engineer, your mentor
It is. So what does a transaction alone actually promise?

The big idea

4 min

Both saw one room left

The big idea

A transaction is all-or-nothing, but not alone in the world: never check, then act on a value another transaction can change in between.

Each transaction did exactly what it was told. The gap was the 50 ms between Ana's read and her commit, and Ben's read landed inside it. The fix is to take the room only if it's still free, in one move.

How it works

5 min

A transaction is a group of reads and writes between BEGIN and COMMIT that succeed or fail as one unit. names its four promises. Let's see which one we were counting on.

What ACID gives you, and what it doesn't

1
Atomicity
All the writes happen, or none do. A crash after the INSERT but before the UPDATE leaves no trace. This worked.
2
Consistency
Every constraint holds at commit. But "never more bookings than rooms" wasn't a constraint, so nothing checked it.
3
Isolation
An sets how much concurrent transactions see of each other. At PostgreSQL's default, Read Committed, Ben saw the last committed value, 1, because Ana hadn't committed yet.
4
Durability
Once COMMIT returns, the booking survives a crash, thanks to the . This worked too.

So the bug is in isolation: a , a bug that appears only when two operations overlap in time. This shape is a . Three standard ways out:

Merge the check and the write into one conditional UPDATE … WHERE available > 0.
Lock the row while you decide: .
Use the strictest level, Serializable, and retry when the database aborts you.

Watch a decision being made

4 min

Worked example

Lina makes the last room safe

Room type 7 at Hotel Lumen has 1 room left for 14 March. Make sure concurrent requests can never both succeed, and a slow payment call can't block other guests.

1Reproduce and name the race
Two psql sessions both BEGIN; SELECT available (both see 1), then write and commit. Two bookings. Check-then-act at Read Committed: a lost update on one row.
Why: A race you can trigger is one you can prove fixed. The name picks the fix: one row means guarding that row.
2Merge the check into the write
One statement that only succeeds if a room is still free.
sql
```
UPDATE inventory SET available = available - 1
WHERE room_type_id = 7 AND night = '2026-03-14' AND available > 0
RETURNING available;   -- no row → sold out → ROLLBACK
```
Why: No separate read to go stale. A concurrent UPDATE waits on the row, then re-checks available > 0 against the committed value.
3Add the safety net, keep slow calls outside
CHECK (available >= 0) on inventory. Then: short transaction to hold the room, payment call (1–3 s), short transaction to confirm.
Why: The constraint turns any future bypass into an error, not a stranded guest. Locks last until COMMIT, so payment inside would make everyone for that night wait 3 s.
4Test the race again
Fire 10 concurrent bookings for the last room: exactly 1 confirmation and 9 "sold out". Before the fix: 4 confirmations.
Why: The test that proved the bug now proves the fix, and goes into CI so nobody brings the race back.

In real life

3 min

Never wrong, or always available?

Payment ledgers: strict transactions

Money movements must balance exactly; a doubled update is a real loss
Commonly built on ACID databases; accept some waiting to never be wrong

Fits data where a wrong answer costs money.

Amazon's shopping cart (Dynamo paper, 2007)

Publicly described keeping "add to cart" available even during failures, merging conflicts later
The paper notes the cost: a deleted item could occasionally reappear

Fits data where a rare glitch is cheaper than refusing a write.

What goes wrong

3 min

Believing BEGIN/COMMIT alone prevents double booking

At Read Committed, two transactions can read the same available = 1.

Instead

A conditional UPDATE, a row lock, or Serializable with retries, plus a constraint.

Writing back an absolute value from a stale read

SET available = 0 computed earlier is a lost update waiting to happen.

Instead

Write relative to the current row (available - 1) with a guard.

Calling external APIs inside a transaction

Locks and a connection are held for the whole network call.

Instead

Hold → external call → confirm, each step short.

Trade-offs

3 min

Fix	Fits when	You pay
Conditional update + constraint	The rule fits on one row: stock, seats, balances	Doesn't cover rules spanning rows
SELECT … FOR UPDATE	Real contention on specific rows, short transactions	Waiting; deadlocks if lock order varies
Serializable + retries	Rules span several rows, moderate contention	Aborts under load; every caller needs retries

Monday, 11:00. Deciding the permanent fix.

Karim · CTO
I never want to think about races again. Serializable everywhere, with a retry wrapper.
Lina · Senior engineer, your mentor
For bookings, one conditional UPDATE and a CHECK do it. Serializable everywhere means more aborts on our busiest nights.
Karim · CTO
Your way, the next engineer writes check-then-act somewhere else.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

Which steps must succeed or fail together (take the room off sale and save the booking) and therefore share one transaction.
How two simultaneous bookings for the last room are settled: a conditional update or a row lock, chosen knowing the trade-offs.
What stays outside the transaction: the 1–3 s payment call.

Tell Claude Code

Hotel Lumen's last Double for 14 March was booked twice, 50 ms apart, though the code runs in BEGIN/COMMIT at Read Committed: it SELECTs available, then inserts and sets available to 0. Plan first. Make only one of two simultaneous bookings succeed, the other gets "sold out". Prefer a conditional update on the inventory row, add CHECK (available >= 0), keep the payment call outside the transaction. Write a test firing 10 bookings at once for the last room: exactly one wins.

Check before you accept

The concurrency test: 10 at once, exactly 1 success, 9 "sold out", available ends at 0, never -1.
Claude can point at the one statement that makes check and write atomic.
The payment call is not between BEGIN and COMMIT.

Red flags

"The code already uses a transaction, so races are impossible."
SERIALIZABLE with no retry for error 40001.
A test that books twice one after the other, which can't reveal a race.

Check yourself

2 min

Q1/3Debugging

This runs inside BEGIN/COMMIT at PostgreSQL's default isolation. Two guests book the last room simultaneously and both succeed. Why?

SELECT available FROM inventory WHERE room_type_id = 7 AND night = '2026-03-14';
-- app: if available > 0
INSERT INTO bookings (...) VALUES (...);
UPDATE inventory SET available = 0 WHERE room_type_id = 7 AND night = '2026-03-14';

How confident are you?

Next episode: a festival weekend in Lisbon, and booking requests start queuing behind one locked row while the payment provider takes 3 seconds to answer.

Finished?

Mark it complete to update your skill map.

Next: Locking and deadlocks

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Visual diagram, Analogy, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Making the last room safe

Worked example

Lina makes the last room safe

Room type 7 at Hotel Lumen has 1 room left for 14 March. Change the booking path so concurrent requests can never both succeed, a crash can't leave half a booking, and a slow payment call can't block other guests.

1Reproduce the race
Open two psql sessions. In both, BEGIN; SELECT available …; (both see 1). Then INSERT and UPDATE in both, and COMMIT both. Two bookings.
Why: A race you can trigger on demand is a race you can prove you fixed. Without it, "it seems fine now" means nothing.
2Name the anomaly
Check-then-act on a value read before the other transaction committed: a lost update. Isolation level: Read Committed.
Why: The name picks the fix. A lost update on one row is solved by guarding that row; a rule spanning many rows (write skew) would need more.
3Merge the check into the write
Replace the SELECT + UPDATE with one statement that only succeeds if a room is still free.
sql
```
UPDATE inventory SET available = available - 1
WHERE room_type_id = 7 AND night = '2026-03-14' AND available > 0
RETURNING available;   -- no row → sold out → ROLLBACK
```
Why: There's no separate read to go stale. A concurrent UPDATE waits on the row, then re-checks available > 0 against the committed value.
4Add the safety net
ALTER TABLE inventory ADD CONSTRAINT available_non_negative CHECK (available >= 0);
Why: The partner import, an admin tool, or next year's refactor might skip the condition. The constraint turns any such bug into an error instead of a stranded guest.
5Keep slow calls outside
Short transaction 1: take the room as a hold (with an expiry). Then call the payment provider (1–3 s). Short transaction 2: confirm the hold.
Why: A transaction holds its row locks until COMMIT. Waiting 3 s for payment inside it would make every other guest for that night wait 3 s too.
6Test the race again
Fire 10 concurrent booking requests for the last room. Expect exactly 1 confirmation and 9 "sold out". Before the fix: 4 confirmations.
Why: The same test that proved the bug now proves the fix, and it goes into CI so nobody quietly brings the race back.

Anomaly	What happens	Read Committed (PG default)	Repeatable Read	Serializable
Dirty read	See another transaction's uncommitted data	Prevented	Prevented	Prevented
Non-repeatable read	Same row read twice gives different values	Possible	Prevented	Prevented
Phantom read	Same query returns new rows the second time	Possible	Prevented in PostgreSQL	Prevented
Lost update	Two read-modify-writes; one overwrites the other	Possible	Detected: one fails, retry	Prevented
Write skew	Two transactions check a condition, write different rows, break it together	Possible	Possible	Prevented (one fails, retry)

PostgreSQL behaviour. MySQL/InnoDB defaults to Repeatable Read with different details; check your database's documentation.

See , , , and in the glossary. PostgreSQL implements isolation with : readers see a snapshot and never block writers. The other two standard fixes:

sql

BEGIN;
SELECT available FROM inventory
WHERE room_type_id = 7 AND night = '2026-03-14'
FOR UPDATE;                                           -- Ben's transaction waits here until Ana's commits

-- application: if available = 0 → ROLLBACK, show "sold out"
INSERT INTO bookings (guest_id, room_type_id, night, status) VALUES (81723, 7, '2026-03-14', 'confirmed');
UPDATE inventory SET available = available - 1
WHERE room_type_id = 7 AND night = '2026-03-14';
COMMIT;

Pessimistic locking: competing bookings for the same room-night run one after another.

async function withTxRetry<T>(fn: (tx: Tx) => Promise<T>, attempts = 3): Promise<T> {
  for (let i = 1; ; i++) {
    try {
      return await db.transaction(fn, { isolation: "serializable" });
    } catch (e: any) {
      const retryable = e.code === "40001" || e.code === "40P01";
      if (!retryable || i >= attempts) throw e;
      await sleep(Math.random() * 20 * 2 ** i); // small jittered backoff
    }
  }
}

Serializable: correct for any logic, but every caller needs a retry wrapper for 40001 (serialization) and 40P01 (deadlock).

Visual diagram

Ana: Book

Ben: Book

API worker 1

API worker 2

inventory row (available = 1)

bookings

WAL on disk

Click a component

Two concurrent booking transactions for the last room, with and without a row lock.

Analogy

At a hotel front deskIn a database

At a hotel front deskFill the whole form or tear it upIn a databaseAtomicity
At a hotel front deskForms in the binder are in inkIn a databaseDurability
At a hotel front deskTwo clerks glance at the same last keyIn a databaseA weak isolation level
At a hotel front deskFirst clerk takes the key off the hookIn a databaseLocking the row
At a hotel front deskA supervisor makes one clerk start overIn a databaseThe strictest level, then retry

Where the analogy breaks: clerks can talk to each other. Transactions can't. They only coordinate through locks, rules and the isolation level you choose.

Check your understanding

Q1/3Scenario

Rule: a hotel must always have at least one manager on call. Two managers, both on call, each run a transaction at Repeatable Read: "count on-call managers; if ≥ 2, set myself off call". Both commit. Now nobody is on call. Which anomaly is this?

How confident are you?

Practice ladder

Step 1 · Trace

Trace two transactions on a wallet

Scenario Warm-up

StayHub gives loyalty credit. Ana's wallet has €100. Two refunds arrive at the same moment, each running this code inside a transaction at PostgreSQL's default isolation (Read Committed):

BEGIN;
SELECT balance_cents FROM wallets WHERE guest_id = 81;   -- read
-- app computes new_balance = balance + refund
UPDATE wallets SET balance_cents = :new_balance WHERE guest_id = 81;
COMMIT;

Refund A is €30, refund B is €50. Both SELECTs run before either UPDATE. Trace the steps and give Ana's final balance. Then name the anomaly.

Step 2 · Complete

Finish the hold-pay-confirm flow

Code Practice

Lina's new booking flow has three steps. Fill in the SQL for steps 1 and 3, and say what step 3 must do if the hold already expired.

1. Short transaction: take the room as a hold (expires in 10 minutes)
   ___
2. Call the payment provider (1–3 s), OUTSIDE any transaction
3. Short transaction: confirm the hold
   ___

Tables: inventory(room_type_id, night, available) with CHECK (available >= 0), and bookings(id, guest_id, room_type_id, night, status, hold_expires_at) where status is 'held', 'confirmed' or 'expired'.

Step 3 · Solve

Free upgrade vouchers

Design Practice

Maya launches a promotion: the first 100 guests who book in Porto this weekend get a free upgrade voucher. The current code:

BEGIN;
SELECT count(*) FROM vouchers WHERE campaign = 'porto-upgrade';
-- app: if count < 100, insert a voucher
INSERT INTO vouchers (campaign, guest_id) VALUES ('porto-upgrade', :guest);
COMMIT;

The campaign goes live at 09:00 to 30,000 newsletter readers. Explain what will go wrong, then design a fix that guarantees at most 100 vouchers and one voucher per guest.

Step 4 · Decide

Overbooking on purpose?

Decision Stretch

Karim brings data from a large partner hotel: on average 6% of its bookings are no-shows or late cancellations. Airlines sell more seats than they have. Karim proposes the same for this hotel: allow up to 3 extra bookings per night above its 50 rooms.

The hotel agrees, but asks: "If everyone shows up, what happens to the extra guests?" StayHub would have to pay for another hotel nearby (about €180 per night) plus a taxi.

Write a decision about how StayHub's booking database should handle this: "We chose X over Y because…, accepting the risk of…". Say how the rule is enforced in the database.

Explain it yourself

Explain why two guests can both book the last room even though the code uses a transaction, and the ways to fix it.

0 words