Sneaker Drop

A conditional UPDATE instead of SELECT … FOR UPDATE. Reserving runs UPDATE … SET availableStock = availableStock - 1 WHERE … AND availableStock > 0 (simplified: it also adds back a unit from the caller's own overdue hold). Both approaches serialize buyers on the drop row, so the difference is cost and fragility, not safety. The conditional update is one statement, with no read-modify-write in the application to get wrong, and a CHECK constraint on stock is the backstop if some other code path ever tries to oversell. FOR UPDATE stays the right tool when the decision needs several reads that can't be one predicate.

Purchase and expiry share one conditional transition. Both leave pending through an UPDATE … WHERE status = 'pending', so whichever commits first wins and the other matches no row. The old timer read a reservation without a lock and later wrote expired unconditionally, so a purchase committing in between was overwritten and the sold unit went back on sale. Purchase also checks expiresAt itself, so it never depends on the sweeper having run.

A database sweeper instead of a timer per reservation. Timers were lost on every restart, leaving holds pending forever. The sweeper runs once at startup, which recovers anything that expired during downtime, then every 5 seconds. It takes a transaction-scoped advisory lock so only one sweep runs at a time, and FOR UPDATE SKIP LOCKED so it skips a reservation that a purchase is changing instead of waiting behind it. Plain FOR UPDATE would still be correct, but the sweeper would stall behind every in-flight purchase. The cost is lag: a unit can return to stock up to one sweep interval after its deadline.

reserve releases the caller's own overdue hold first. Waiting for the sweeper meant re-reserving right after a deadline returned ALREADY_RESERVED (or SOLD_OUT if that hold was the last unit). It also deadlocked with the sweeper, because the two took the reservation and drop locks in opposite orders. Now reserve expires its own overdue hold before touching the drop, which is the sweeper's lock order.

19 integration tests against a real Postgres, run in CI on pushes to develop and on pull requests. After each scenario they assert that availableStock + pending + completed = totalStock and that purchases equal completed reservations.

40 concurrent reservers for 10 units: exactly 10 succeed, 30 get SOLD_OUT, stock is 0. One user making 10 concurrent attempts gets one reservation and nine ALREADY_RESERVED. Ten concurrent purchases of one reservation create one purchase, with the same id returned to every caller.

Forced purchase-versus-expiry races: one side's transaction is held open, the test waits until pg_stat_activity shows the other side queued on the lock, then commits. If expiry commits first, the purchase is refused with RESERVATION_EXPIRED and the unit returns once. If the purchase is in flight, the sweep returns without waiting and the sold unit is not restored. A stress test runs 60 users and 30 units over five rounds, with purchases (some duplicated) racing five parallel sweeps, and the invariant holds after every round.

npm run test:mutation injects eight deliberate bugs one at a time (for example: sweeper ignores status, purchase skips the deadline check, reserve has no stock condition), runs the suite, and exits non-zero if any survives. The writeup records no survivors, in about 13 seconds. Two of the eight are liveness bugs (no SKIP LOCKED, no advisory lock) that leave the data correct; the tests catch the first by checking that the sweep did not wait. The second's real risk, sweepers deadlocking across several drops, is not reproduced by the single-drop test.

Benchmark with npm run bench:reserve: one drop, 3000 reservations per run after 200 warm-up, each by a distinct user, pool of 64, three runs, on an Apple M4 with Node 24 and Postgres 16 in Docker on the same machine. At 1, 8, 32 and 64 concurrent workers, the conditional UPDATE did 1190–1346, 1480–1560, 1199–1224 and 949–1027 ops/s; SELECT … FOR UPDATE did 1056–1154, 1108–1186, 906–1018 and 676–848. At 8 workers p99 was 18–20 ms against 95–116 ms. The stock invariant held in every run. Both are correct; the difference is cost. Neither scales with concurrency, because every attempt queues on the same row lock, and run-to-run variance is about 15%.

Full reasoning, SQL and the mutation table: docs/reservations.md. writeup

Users are selected, not authenticated. userId comes from the request body, so any client can act as any user. It's a demo shortcut.

One hot row per drop: throughput is bounded at roughly 1000–1500 reservations a second on my laptop. Splitting the counter into buckets or putting a queue in front would raise it; I haven't built either.

Stock returns up to 5 seconds after a hold's deadline, except to the holder re-reserving the same drop.

The tests run in one Node process against one Postgres. The sweeper is built to be safe with several app instances, but I haven't run two.

The benchmark was run before the overdue-hold step was added to reserve, and I haven't rerun it.

The original 100-user HTTP race script still exists (npm run test:atomic) but needs a live backend and isn't in CI; the integration suite is what CI runs.