Preventing Overselling: Inventory Locks Under Concurrent Checkouts

Two customers are looking at the same product. One unit left. Within the same second, both click Pay. If your checkout reads the stock count, decides there's enough, and then writes the decrement, both requests pass the check and both succeed. You've now sold two units of something you had one of. That's overselling, and it's not a rare edge case - it's the default behaviour of any checkout that treats "check stock" and "reduce stock" as two separate steps. The window is small, but on a product that's nearly sold out, or during a launch when everyone hits the same SKU at once, small windows fire constantly. I've built the order pipeline for two production e-commerce platforms - pikkuna.fi and pi-pi.ee - where concurrent webhooks and concurrent checkouts hit the same order and product rows. This is the layer I reach for when a store sells finite stock. I covered the bare SELECT ... FOR UPDATE primitive briefly in PostgreSQL Production Patterns; this article is the whole system built on top of it - reservations, multi-line carts, the payment window, and the parts that actually bite you in production. When You Don't Need Any of This Start with the honest disclaimer, because it decides everything downstream. Both pikkuna.fi and pi-pi.ee are made-to-order. A vinyl curtain is cut to the customer's dimensions; a waterless urinal system ships from a supply chain, not a shelf with a hard unit count. When there's no fixed quantity to run out of, overselling isn't a failure mode - you can't sell the tenth unit of something you manufacture on demand. So neither of those platforms needs a row lock on a stock column, and I didn't build one there. You need this article when you sell discrete, finite stock: limited runs, event tickets, one-off items, anything where "5 left" is a real number and selling the sixth is a promise you can't keep. If your catalogue is print-on-demand, made-to-order, or backed by effectively unlimited supply, stop here - the locking below is complexity you'd be maintaining for a race that can't happen. Building the reservation layer for a store that can't run out of stock is exactly the kind of over-engineering I'd talk a client out of. The rest of this assumes you genuinely have finite stock and concurrent buyers. The Race, Precisely Here's the naive version. It looks correct in every code review and passes every test that runs requests one at a time. // DO NOT SHIP THIS async function buy(productId: string) { const product = await db.query.products.findFirst({ where: eq(products.id, productId), }); if (!product || product.stock = 1 on the last unit - one wins, the other's WHERE no longer matches and it updates zero rows. The .returning() tells you which happened. For a single-line, decrement-at-checkout store, this alone prevents overselling. No explicit FOR UPDATE , no transaction block. Reach for the atomic conditional UPDATE before anything heavier - it's the smallest thing that's correct. So why does the rest of this article exist? Because two realities break the one-statement approach: - Carts have multiple lines, and you need all-or-nothing across them. - Payment isn't instant. You confirm availability at checkout, but the money lands seconds - or with SEPA and bank transfer, days - later. What happens to the stock in between? Fix Two: Reservations vs Hard Decrements There are two models for holding stock, and choosing between them is the real design decision. Hard decrement subtracts from stock the moment the order is placed. Simple, one column, no background jobs. It works when payment is synchronous and near-instant - card payments that succeed or fail in the same request. Its weakness: if the payment then fails, or the customer abandons a redirect-based method, you've decremented stock for a sale that never happened. You need a compensating restock, and if that compensation is missed, the unit is silently locked away forever. Reservation splits the count in two. You don't decrement stock ; you increment reserved . Available stock is a derived value: CREATE TABLE products ( id UUID PRIMARY KEY, stock INTEGER NOT NULL CHECK (stock >= 0), reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0), CONSTRAINT reserved_within_stock CHECK (reserved l.productId).sort(); return db.transaction(async (tx) => { // Lock all involved product rows up front, in sorted order const locked = await tx .select({ id: products.id, stock: products.stock, reserved: products.reserved }) .from(products) .where(inArray(products.id, ids)) .for("update"); const byId = new Map(locked.map((p) => [p.id, p])); // Verify availability for every line before writing anything for (const line of cart) { const p = byId.get(line.productId); if (!p) throw new OutOfStockError(line.productId); if (p.stock - p.reserved { const rows = await tx .select() .from(reservations) .where(and(eq(reservations.orderId, orderId), eq(reservations.status, "held"))) .for("update"); for (const r of rows) { await tx .update(products) .set({ stock: sql${products.stock} - ${r.qty}, reserved: sql${products.reserved} - ${r.qty}, }) .where(eq(products.id, r.productId)); } await tx .update(reservations) .set({ status: "committed" }) .where(eq(reservations.orderId, orderId)); }); stock and reserved drop together, so available stock is unchanged - the unit was already accounted for at reservation time. This is the moment the sale becomes real. Payment fails (payment_intent.payment_failed , or the async method is declined). Release the reservation - drop reserved , leave stock alone - and the unit is instantly available to the next buyer. No restock arithmetic, no risk of double-restocking, because you never touched stock . Nothing happens. The customer closed the tab. This is why reservations carry expiresAt . A background sweep releases anything past its expiry: // Runs on a schedule - release stale reservations await db.transaction(async (tx) => { const stale = await tx .select() .from(reservations) .where(and(eq(reservations.status, "held"), lt(reservations.expiresAt, new Date()))) .for("update", { skipLocked: true }); // don't fight the webhook for rows it's committing for (const r of stale) { await tx .update(products) .set({ reserved: sql${products.reserved} - ${r.qty} }) .where(eq(products.id, r.productId)); } await tx .update(reservations) .set({ status: "expired" }) .where( inArray( reservations.id, stale.map((r) => r.id) ) ); }); SKIP LOCKED matters here. The sweep and the success-webhook can race for the same reservation: the customer pays at the very moment the sweep runs. FOR UPDATE SKIP LOCKED tells the sweep to skip any row another transaction is already holding, rather than block on it. The webhook wins, commits the sale, and the sweep simply moves on - it never expires a reservation that's mid-commit. Without SKIP LOCKED you either block the sweep behind the webhook (fine, but slower) or, worse, if you got the ordering wrong, expire a paid order. Match the reservation TTL to the payment method. Fifteen minutes is reasonable for cards. For bank transfer, where settlement legitimately takes days, a 15-minute reservation would release stock out from under a paying customer - you either extend the TTL for those methods or don't reserve scarce stock for them at all and accept the backorder. A backorder the buyer agrees to up front is a business choice; silently overselling stock you don't have is not. That decision belongs to the business, not to the code. Where This Still Bites I'd rather name the limits than pretend the pattern is bulletproof. Lock contention on a single hot SKU. If ten thousand people hit one product at launch, they all queue on that one row's lock and serialize. Correct, but slow - checkout latency climbs as the queue grows. Row locking prevents overselling; it does not make a flash sale fast. Genuinely extreme concurrency wants a different tool: decrement a Redis counter first as a fast admission gate, and treat Postgres as the durable source of truth behind it. That's a real increase in moving parts, and only worth it when you've measured the contention - not by default. The webhook must be idempotent. Stripe retries webhooks. If payment_intent.succeeded is delivered twice and you decrement twice, you've corrupted your stock in the opposite direction. The status transition (held → committed ) above is the guard: a second delivery finds no held reservation and does nothing. Getting that exactly right is its own problem - I wrote it up in Idempotency Keys for API Retries. Reservations leak if the sweep dies. If your background job stops running, expired reservations pile up and reserved creeps toward stock , choking availability for real buyers. The sweep is infrastructure, and it needs the same monitoring as anything else you depend on. A reservation system without a working expiry sweep is worse than a hard decrement, not better. Read replicas lie. If you check availability against a read replica for speed, it may lag behind the primary and show stock that's already reserved. Availability checks that gate a purchase must hit the primary. Display counts on a product page can tolerate lag; the checkout decision cannot. Overselling is a race condition, not an inventory problem. The fix is the same discipline every time: make the decision and the write one indivisible operation, serialize the transactions that compete for the last unit, and model the gap between "reserved" and "paid" explicitly so no unit is ever both sold and available. Start with the atomic conditional decrement, move to reservations only when async payment or multi-line carts force it, and don't build any of it for a store that can't run out of stock. This is the correctness work under a checkout that sells real, finite stock - the difference between a store that quietly holds its promises and one that emails customers to apologise for a unit it can't ship. It's the kind of thing I build into e-commerce projects from the start, because retrofitting it