Episode 26 · The festival weekend queue

Locking and deadlocks

Friday, 10:03: the Lisbon festival sale opened three minutes ago. Booking p99 is 9 seconds, and 3% of two-night bookings fail with "deadlock detected".

When two people change the same thing at once, someone has to wait. Here's how databases take turns, and how they get stuck.

30 min intermediateRow locks and SELECT ... FOR UPDATEOptimistic vs pessimistic concurrency controlDatabase deadlocks and lock orderingTable locks, DDL and the lock queueTransactions & ACIDIsolation levels and concurrency anomalies

Problem
Many transactions touch the same rows at the same moment: the last room, a wallet balance, a stock count.
Why the simple system fails
Without coordination, two writers read the same value and both succeed, so you sell the last room twice. With careless coordination, transactions wait on each other forever or queue behind one slow migration.
Engineering concept
Locks serialize access to the rows that matter. Lock up front when conflicts are likely (pessimistic), detect conflicts at write time when they are rare (optimistic), and always acquire locks in a consistent order.
Technology
SELECT … FOR UPDATE, version columns, UPDATE … WHERE version = n, deadlock detection, lock_timeout, SKIP LOCKED.
Trade-offs
Pessimistic locking costs waiting and throughput on hot rows; optimistic locking costs retries under contention. Every lock held longer than needed is a throughput ceiling.

The problem

2 min

Friday, 10:03. The Lisbon festival sale opened at 10:00: 40 rooms, nights 12 and 13 June.

Omar · On-call SRE
Package bookings: p99 9 s. And 3% fail with deadlock detected.
Maya · Product manager
About 2,000 guests are trying for those 40 rooms.
Lina · Senior engineer, your mentor
This old path locks both nights with FOR UPDATE, then calls payment inside the transaction. What's happening?

The big idea

4 min

One row, one lane

The big idea

A hot row serves one writer at a time, so its capacity is 1 ÷ how long each lock is held. Keep locks short, and always take them in the same order.

Adding servers doesn't widen a single lane: it only sends more cars to queue. The lever is lock time, and the deadlocks come from a different mistake: two bookings locking the same nights in opposite orders.

How it works

5 min

One package booking, today

1
Lock both nights
A row lock marks a row as "I'm changing this"; other writers wait until the holder commits. SELECT … FOR UPDATE takes it on nights 12 and 13, in the order the guest clicked them.
2
Call payment
~400 ms typical, up to 3 s. The locks stay held the whole time.
3
Write
Decrement both nights, insert the booking. A few ms.
4
Commit
Locks released. The next waiting booking gets the rows.

At 500 ms per hold, the row serves about 2 bookings a second. About 3.3 guests a second arrive, so the queue grows and p99 climbs to 9 s. With a 3 ms hold, the same row serves about 300 a second.

The errors are a : two transactions each hold a lock the other needs. Guest A locks night 12 then 13; guest B locks 13 then 12. PostgreSQL detects the cycle within about a second and aborts one.

Two strategies decide when you handle conflicts. : lock as you read, because you expect conflicts; others wait. : no lock; save only if a version number is unchanged, otherwise retry or report a conflict. With 2,000 guests on 40 rooms, conflicts are certain, so optimistic would mostly produce retries.

Watch a decision being made

4 min

Worked example

You and Lina fix the festival checkout

Package bookings take up to 9 s and 3% fail with deadlocks. Make them fast and deadlock-free without ever overbooking the 40 rooms.

1Prove it's waiting, and compute the ceiling
pg_stat_activity shows 37 transactions waiting on locks on inventory, the oldest for 8 s. Hold ~500 ms → ceiling ≈ 2 bookings/s, against ~3.3 arriving.
Why: Slow can mean CPU, disk or waiting. Once arrivals exceed the ceiling, the queue grows without limit, and the number points at the lever: hold time.
2Shrink the hold
Short transaction 1 holds both nights; payment happens outside; short transaction 2 confirms. Lock time per booking: ~3 ms.
Why: The ceiling rises from ~2 to ~300 bookings/s on the same hardware.
3Fix the lock order
Always lock nights sorted by date.
sql
```
SELECT night, available FROM inventory
WHERE room_type_id = 7 AND night IN ('2026-06-13','2026-06-12')
ORDER BY night
FOR UPDATE;
```
Why: A deadlock needs opposite orders. One global order makes the cycle impossible; the second guest simply waits in line.
4Retry leftovers, then replay the sale
On 40P01 or 40001, retry the whole transaction up to 3 times with a small random wait. Staging load test, 2,000 guests for 40 rooms: p99 180 ms, 0 deadlocks, exactly 40 bookings.
Why: The database already rolled the victim back, so retrying is safe. Exactly 40 proves correctness held; 180 ms proves the queue is gone.

In real life

3 min

Hold it first, or check at the end?

Wikipedia: optimistic editing

Anyone edits without locking; if someone saved first, you get the familiar "edit conflict" screen
Works because two people rarely edit the same section at once

Fits rare conflicts and long human edits.

Ticket sites: pessimistic holds

Big ticketing sites visibly hold your seats for a few minutes with a countdown
Works because thousands of fans collide on the same seats at on-sale time

Fits frequent conflicts over scarce items.

What goes wrong

3 min

Calling the payment provider inside the transaction

The lock is held for the whole network call; every booking for that night queues.

Instead

Hold in a short transaction, pay outside, then confirm.

Locking rows in whatever order they arrive

Opposite orders on the same rows is the textbook deadlock.

Instead

Sort keys before locking, on every code path.

Optimistic locking on a hot row

Most attempts fail at write time and retry, multiplying load at the worst moment.

Instead

Pessimistic or atomic updates where conflicts are frequent.

Trade-offs

3 min

Strategy	Fits when	You pay
Pessimistic (FOR UPDATE)	Likely conflicts, short critical section	Waiting; deadlocks if order varies
Atomic conditional update	The rule fits a WHERE clause (stock > 0)	Poor "why it failed" detail
Optimistic (version)	Rare conflicts, long human edits	Conflict errors; retry storms when hot

Wednesday, 10:00. Planning the bigger New Year's Eve sale.

Karim · CTO
Locks mean waiting. Switch inventory to optimistic locking: nobody waits, they just retry.
Lina · Senior engineer, your mentor
With 2,000 guests on 40 rooms, almost every attempt would conflict. That's a retry storm.
Karim · CTO
Your locks are why we had 9-second bookings.

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

The order rows are always locked in (nights sorted by date), as a team rule for every code path.
How long a lock may be held: nothing slow inside a transaction. The payment call moves out (~2 bookings/s on a hot row becomes ~300).
The rules for schema changes on busy tables: a short lock timeout, a check for long-running queries, a time you approve.

Tell Claude Code

During the Lisbon festival sale the multi-night booking path hit p99 9 s and 3% deadlocks. It locks nights with FOR UPDATE in click order and calls payment inside the transaction. Plan first. Lock nights in date order; hold the rooms in a short transaction, pay outside, then confirm idempotently. Retry up to 3 times on deadlock or serialization errors. Don't touch the single-night path. Show a load test at 3.3 bookings/s with mixed night orders: p99 and deadlock count.

Check before you accept

The nights are sorted before the first row is locked, whatever order the guest picked.
The payment call sits outside BEGIN/COMMIT.
If payment succeeds but confirm fails, the plan refunds or retries the confirm, never keeps money without a booking.

Red flags

It raises deadlock_timeout or adds sleeps instead of fixing the lock order.
It wraps the whole booking, payment included, in one long transaction "for safety".
It runs ALTER TABLE on a busy table at peak with no lock timeout.

Check yourself

2 min

Q1/3Debugging

Logs show ERROR: deadlock detected on ~1% of transfers between guest wallets during peak. Each transfer does UPDATE on the sender's row, then on the receiver's row. What is the best root-cause fix?

How confident are you?

Next episode: Maya wants fuzzy hotel search, Karim wants click analytics, and someone asks the dangerous question: "Shouldn't we just use a NoSQL database?"

Finished?

Mark it complete to update your skill map.

Next: The NoSQL landscape

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, Why it matters, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Fixing the festival checkout

Worked example

You and Lina fix the festival checkout

Festival package bookings take up to 9 s and 3% fail with deadlocks. Make them fast and deadlock-free without ever overbooking the 40 rooms.

1See who's waiting
Query pg_stat_activity for sessions with wait_event_type = 'Lock'. Result: 37 transactions waiting on inventory, the oldest for 8 s.
Why: Slow can mean CPU, disk or waiting. This proves it's waiting on locks, so a bigger machine won't help.
2Compute the ceiling
Each transaction holds the night rows for ~500 ms (the payment call). Ceiling ≈ 1 ÷ 0.5 s = 2 bookings/s. Arrivals: ~3.3/s.
Why: When arrivals exceed the ceiling, the queue grows without limit. The number tells you which lever matters: the hold time.
3Shrink the hold
Move payment out: short transaction 1 takes a hold on both nights, payment happens outside, short transaction 2 confirms. Lock time per booking: ~3 ms.
Why: The ceiling rises from ~2 to ~300 bookings/s on the same hardware. Nothing else we could change comes close.
4Fix the lock order
Always lock nights sorted by date: add ORDER BY night to the FOR UPDATE query (or update nights in date order).
sql
```
SELECT night, available FROM inventory
WHERE room_type_id = 7 AND night IN ('2026-06-13','2026-06-12')
ORDER BY night
FOR UPDATE;
```
Why: A deadlock needs two transactions locking the same rows in opposite orders. One global order makes that cycle impossible.
5Retry the rare leftovers
Wrap the transaction: on 40P01 (deadlock) or 40001 (serialization), retry the whole transaction up to 3 times with a small random wait.
Why: Other code paths may still collide occasionally. The database already rolled the victim back, so retrying is safe and invisible to the guest.
6Replay the sale
Load test on staging: 2,000 guests in 10 minutes for 40 rooms. Result: p99 180 ms, 0 deadlocks, exactly 40 bookings.
Why: Exactly 40 proves correctness held; 180 ms proves the queue is gone. Both numbers matter.

sql

BEGIN;

-- Lock every night of the stay, ALWAYS in the same order (by night).
SELECT night, available
FROM inventory
WHERE room_type_id = 7
  AND night BETWEEN '2026-06-12' AND '2026-06-13'
ORDER BY night
FOR UPDATE;            -- other writers on these rows now wait

-- App checks every night has available > 0, then:
UPDATE inventory SET available = available - 1
WHERE room_type_id = 7
  AND night BETWEEN '2026-06-12' AND '2026-06-13';

INSERT INTO bookings (guest_id, room_type_id, check_in, check_out, status)
VALUES (81723, 7, '2026-06-12', '2026-06-14', 'held');

COMMIT;                -- locks released here, a few ms after BEGIN

ORDER BY night makes every transaction acquire locks in the same order. No network calls between BEGIN and COMMIT.

// A hotel manager edits a description in an admin form for several minutes.
const hotel = await db.query(
  "SELECT id, description, version FROM hotels WHERE id = $1", [hotelId]);

// ...later, on Save:
const res = await db.query(
  `UPDATE hotels SET description = $1, version = version + 1
   WHERE id = $2 AND version = $3`,
  [newText, hotelId, hotel.version]);

if (res.rowCount === 0) {
  // Someone saved first. Don't overwrite them silently.
  throw new ConflictError("This hotel changed while you were editing. Reload and re-apply.");
}

Optimistic locking. HTTP APIs often expose the version as an ETag and require If-Match, returning 412 on a mismatch.

Lock / technique	What it blocks	Typical use
Plain SELECT (MVCC snapshot)	Nothing: readers don't block writers in PostgreSQL	Normal reads
UPDATE / DELETE	Other writers of the same rows until commit	Every write
SELECT … FOR UPDATE	Other writers and other FOR UPDATE readers of those rows	Read-check-write on contended rows
FOR UPDATE SKIP LOCKED	Nothing: skips rows someone else holds	Job queues: each worker grabs a different row
FOR UPDATE NOWAIT	Fails immediately instead of waiting	UI that should say 'busy, try again' at once
Version column (optimistic)	Nothing; conflict detected at write	Long human edits, low contention
ALTER TABLE (most forms)	Everything on the table, including reads	Migrations: always set lock_timeout

PostgreSQL behaviour; other engines differ in details.

Visual diagram

Guest A (nights 12→13)

Guest B (nights 13→12)

Transaction T1

Transaction T2

inventory: night 12

inventory: night 13

Deadlock detector

Click a component

Two multi-night bookings lock the same inventory rows in opposite order. Step through to see the deadlock form and get resolved.

Analogy

In everyday lifeIn a database

In everyday lifeBorrowing a library bookIn a databasePessimistic locking
In everyday life"This file changed, reload?"In a databaseOptimistic locking
In everyday lifeReserving one deskIn a databaseA row lock
In everyday lifeClosing the whole reading roomIn a databaseA table lock
In everyday lifeTwo people stuck in a narrow corridorIn a databaseA deadlock

Where the analogy breaks: in a corridor, people negotiate. A database just picks one transaction and cancels it, so your code must be ready to retry.

Why it matters

Concurrency bugs hide in development, where you're one user making one request. They show up in production.

Two requests both see "1 room left" and both book it: a double booking. Two refunds each read €100, add €20, and both save €120: €20 vanished. That's a .

Waiting can also take a site down. A database change grabs a table lock behind a long report. Every booking queues up behind it. Nothing crashed, yet the site returns errors.

Good engineers look at any write and ask: what does this lock, for how long, and in what order?

Check your understanding

Q1/3Debugging

Two guests booked the last room within 30 ms of each other. Both requests ran inside a transaction at Read Committed. What is the most likely cause?

BEGIN;
SELECT available FROM inventory WHERE room_type='double' AND night='2026-06-12';
-- app: if available > 0 ...
UPDATE inventory SET available = 0 WHERE room_type='double' AND night='2026-06-12';
INSERT INTO bookings ...;
COMMIT;

How confident are you?

Practice ladder

Step 1 · Trace

Trace two wallet transfers

Scenario Warm-up

Guest wallets live in wallets(guest_id, balance_cents). A transfer runs:

BEGIN;
UPDATE wallets SET balance_cents = balance_cents - :amt WHERE guest_id = :from;
UPDATE wallets SET balance_cents = balance_cents + :amt WHERE guest_id = :to;
COMMIT;

At the same moment, T1 moves €20 from guest 5 to guest 9, and T2 moves €10 from guest 9 to guest 5. Both first UPDATEs run before either second UPDATE.

Trace what each transaction holds and waits for, what PostgreSQL does, and what the app should do with the error.

Step 2 · Complete

Finish the optimistic description editor

Code Practice

Two managers of Hotel Lumen edited the hotel description at the same time; the one who saved second silently erased the other's new check-in time. Lina started the fix with a version column. Fill in the three gaps.

// When the form opens:
const row = await db.query(
  "SELECT description, checkin_time, ___1___ FROM hotels WHERE id = $1", [hotelId]);

// On Save (minutes later):
const res = await db.query(
  `UPDATE hotels
     SET description = $1, checkin_time = $2, version = ___2___
   WHERE id = $3 AND ___3___`,
  [desc, checkin, hotelId, row.version]);

if (res.rowCount === 0) {
  // what should happen here?
}

Step 3 · Solve

A job queue for 20 workers

Design Practice

StayHub sends notifications to hotels (new booking, cancellation) through a PostgreSQL table jobs(id, status, payload, created_at): about 300 new jobs a second at peak, each taking 50–500 ms of HTTP work. 20 worker processes poll the table.

The first version did:

BEGIN;
SELECT * FROM jobs WHERE status = 'queued' ORDER BY id LIMIT 1 FOR UPDATE;
-- send the HTTP notification (50–500 ms)
UPDATE jobs SET status = 'done' WHERE id = :id;
COMMIT;

Throughput is terrible and the jobs pile up. Explain why, and design a version where 20 workers run in parallel.

Step 4 · Decide

Split the hot row for New Year's Eve?

Decision Stretch

New Year's Eve in Lisbon: Maya expects 8,000 guests in 5 minutes going for 300 rooms across 6 hotels, with most demand on 2 hotels. After this episode's fixes, each booking holds a night's row for ~3 ms.

Omar suggests splitting each hot night into 10 inventory buckets (each holding a tenth of the rooms; a booking picks a random bucket and tries others if it's empty). Lina thinks the current design is enough.

Do the math, then write a decision: "We chose X over Y because…, accepting the risk of…".

Explain it yourself

Explain to a teammate why your booking endpoint deadlocked under load, how you fixed it, and how you'd choose between optimistic and pessimistic locking for a new feature.

0 words