Matching: pick a lock or isolation level for each situation Intermediate

Eight concurrent-write situations, eight ways to handle them. Most of them “work” in several situations; the question is which one is correct for the business rule and cheapest.

If you miss, the game explains why that choice is close but not right. The most commonly confused pairs: FOR UPDATE vs atomic UPDATE, SKIP LOCKED vs NOWAIT, and FOR UPDATE vs SERIALIZABLE when the invariant spans several rows.

Matching

Which lock or isolation level does this case need?

Eight concurrent-write cases on PostgreSQL 17, default Read Committed. Drag the best-fitting approach onto each case.

Tip: ask in order "can the condition be written as a single SQL statement?", "is there a constraint that can enforce it?", and only then move on to locks and isolation.

Tap Choose answer on each case. On a computer you can drag a card below onto a case.

SERIALIZABLE + retryOptimistic: version columnAdvisory lockFOR UPDATE SKIP LOCKEDNOWAIT / lock_timeoutSELECT ... FOR UPDATEUNIQUE + ON CONFLICTAtomic conditional UPDATE

Solved 0/8 · 0 mistakes

1Decrement stock when an order is placed. 50 requests compete for 10 units at the same time. The only rule: stock must never go negative.

SELECT stock FROM product WHERE id = $1;   -- app: if (stock >= 1)
UPDATE product SET stock = $2 WHERE id = $1;

Choose answer ▾

28 workers pull email-sending jobs from a jobs table. Each job is independent. Right now the second worker sits waiting on the job the first worker has locked, even though the queue still holds 3,000 jobs.

Choose answer ▾

3Book seat 42. Before writing, the app must read the seat status, check the user's membership tier and ticket quota, compute the price by time slot, and only then INSERT the ticket and update the seat status.

Choose answer ▾

4A payment gateway sends payment.succeeded webhooks at-least-once: the same event_id can arrive 2–3 times, sometimes simultaneously on two pods. Each event may credit money only once.

Choose answer ▾

5Rule: every shift must keep at least one doctor on call. 5 code paths (request leave, swap shift, admin edits schedule, Excel import, mobile API) all read several rows and then write a different row. Two doctors request leave at the same time and both get approved.

SELECT count(*) FROM shifts WHERE shift_id = 7 AND on_call;  -- both see 2
UPDATE shifts SET on_call = false WHERE doctor = $me;

Choose answer ▾

6An editor opens the article edit form, reads and edits for 5–15 minutes, then clicks Save. Occasionally two people edit the same article and whoever saves last overwrites the other's content.

Choose answer ▾

7A "Refund" button on the order admin page. Processing takes ~2 seconds inside a transaction. If another staff member is refunding the same order, the UI must immediately say "Order is being processed" instead of spinning.

Choose answer ▾

8A cron job that recalculates reward points at 02:00 runs on all 3 pods. The job takes 20 minutes and is not tied to any particular row. Running it twice double-credits points.

Choose answer ▾

After playing, read Writing transactions in code for the pattern selection table and a demo of 50 requests competing for 10 products.