xmuu
cases /

The oversell race: locking, versions, and idempotency at checkout

checking

October 9, 2026 · go · sqlite · open demo ↗

The demo runs as its own app. If it fails to load here, it is genuinely down — open fullscreen to check, or come back later. The write-up below stands on its own.

open demo ↗

Problem

Flash sale. Five units left. Twenty-five buyers hit checkout in the same second, and every one of their requests does the same three things: read the stock, decide there is enough, write a sale. Two of those requests read the same "5" — and both are told yes. The oversell is not a bug anyone wrote; it is the gap between checking and acting that every check-then-act flow contains. Payment systems, seat maps, promo codes, terraform state locks, Kubernetes leaders: same shape, same hole.

Context

The demo sells one SKU (5 units) under three concurrency strategies — identical schema, identical traffic (25 concurrent buyers per rush), identical Go code except for how the purchase guards its check-then-act window. Every attempt is journaled with its strategy and outcome, so after each rush you can read exactly who was told "confirmed" for a unit that never existed.

One implementation note that is itself the lesson: the SQLite handle in this demo opens multiple connections for the no-lock strategy on purpose. With a single connection, database/sql serializes every statement and the race disappears — the bug is hidden by the deployment configuration, not fixed. Run the same code on a real connection pool (or RDS with 200 connections) and it is back. My first test run "passed" for exactly this reason: green check, phantom safety.

Alternatives Considered

Optimistic concurrency (version columns). Each SKU row carries a version. The purchase reads stock+version, writes UPDATE ... WHERE version = ?, and — here is the part everyone forgets — decides what a failed write means: retry, or give up. Under contention most attempts lose, so you are choosing between retry storms and angry buyers. It is correct, but the correctness comes from conflict, and conflict is latency.

Pessimistic locking (BEGIN IMMEDIATE). Grab the write lock, then read, decide, and write inside one transaction. Buyers queue; correctness comes from scheduling. The cost is throughput on the hot row and, on network databases, lock waits that show up as your p99.

Redis reservation / queue-everything. Move the stock counter out of the database, or accept orders into a queue and settle them sequentially. Both are legitimate at scale; both add an infrastructure boundary — and a new consistency question ("what if the reservation service dies after the payment?") — before the race is even solved.

The demo keeps all three strategies in-process against one real SQLite file, so the only variable is the strategy.

Solution

The three purchases:

// no-lock: the bug. Check and act are two separate moments.
var stock int
_ = db.QueryRow(`SELECT stock FROM skus WHERE sku = ?`, sku).Scan(&stock)
if stock <= 0 { return reject() }
_, _ = db.Exec(`UPDATE skus SET stock = stock - 1 WHERE sku = ?`, sku)

// optimistic: the check and the act share a guard.
res, _ := db.Exec(`UPDATE skus
    SET stock = stock - 1, version = version + 1
    WHERE sku = ? AND version = ?`, sku, version)

// pessimistic: everyone waits their turn.
tx, _ := db.Begin()            // first statement grabs the write lock
_, _ = tx.Exec(`UPDATE _lock_anchor SET x = x + 1`)
// ...read, decide, deduct inside the tx...
_ = tx.Commit()

Measured on the demo rush (25 buyers, 5 units — rerun it, the journal keeps every attempt): no-lock confirms 14 orders for 5 units and finishes at stock −9; optimistic confirms exactly 5 and turns the other 20 into clean refusals; pessimistic does the same by lining buyers up. The oversold orders are classified honestly: after each deduction the strategy re-reads the actual stock, and a confirmed order that pushed stock negative is recorded as oversold — the journal shows the phantom sales, not a vague error count.

Two contract details matter as much as the strategy. Idempotency is a schema constraint, not hope: UNIQUE (sku, buyer) plus a fast-path check before the strategy runs, so a retried request can never double-deduct — putting that check after the deduction is a bug my own tests caught during development (stock 8 after one purchase and one replay). Classification requires the post-write read: from the buyer's stale local value, an oversold order is indistinguishable from a normal one.

Trade-offs

  • Optimistic: readers never block anyone and the happy path is one

round-trip — but under contention, retries multiply load exactly when you can least afford it. Cap the attempts (the demo allows 8) and have a real answer for attempt 9. Wrong for hot rows, right for rarely-contended ones.

  • Pessimistic: bounded, predictable, easy to reason about — and it makes

the database the choke point. On SQLite the lock is the file; on Postgres it is row locks and idle-in-transaction timers; on anything networked, every waiter is holding a connection. Correct by scheduling, paid in tail latency.

  • Idempotency keys are not optional. Whichever strategy wins, the network

will replay requests: timeout, retry, double-click. The first buyer to touch the endpoint owns the key; everyone after gets their original answer back. The demo's duplicate outcome is the system working correctly — show me a checkout without it and I will show you double charges.

  • Queueing is a product decision. "Sold out" versus "you are #14 in line,

restock incoming" are different businesses. Pessimistic locking naturally produces the second; optimistic produces the first. Neither is the default answer.

Production Notes

  • SQLite specifics: modernc/sqlite has no BEGIN IMMEDIATE through

database/sql, so the pessimistic path grabs the write lock by making a write the transaction's first statement. Multi-connection handles need busy_timeout, or concurrent writers fail with SQLITE_BUSY instead of waiting — the demo sets 5s. And MaxOpenConns(1) is a legitimate production choice for small SQLite services, but know what it is doing: it is a global mutex that also hides check-then-act bugs.

  • The deadlock I shipped while building this: the pessimistic rejected

path journaled the refusal while still holding the transaction — and with one pooled connection, the journal write waited forever on the connection the transaction held. End the transaction before touching anything else that needs the pool. Tests found it as a 15-second timeout hang; in production it would have been a pile of hung goroutines.

  • Postgres translation: BEGIN IMMEDIATE becomes the default

INSERT/UPDATE first-write behavior or SELECT ... FOR UPDATE; the version column works verbatim; UNIQUE (sku, buyer) works verbatim. The strategies are storage-agnostic; only the lock mechanics differ.

  • Real flash sales do one more thing: they hold the reservation (with a

TTL) at add-to-cart time, not at payment. The race is then between reservations, which are cheap, and only the winners ever reach the payment-side stock check. The strategies here are the building blocks; the TTL reservation is the orchestration around them.