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) {
throw new Error("Out of stock");
}
// Another request can run this exact block between the read above
// and the write below. Both read stock = 1. Both pass the check.
await db
.update(products)
.set({ stock: product.stock - 1 })
.where(eq(products.id, productId));
}
The bug lives in the gap between the read and the write. Two requests interleave: both read stock = 1, both pass stock < 1 as false, both compute 1 - 1 = 0, both write 0. Two orders, one unit. The database did exactly what you told it — you told it wrong.
There are two independent things to fix here, and people often conflate them:
- The decrement must be atomic — the new value has to be computed from the value at write time, not from a value you read earlier and held in a variable.
- The availability check and the decrement must be serialized — no two transactions may both pass the "is there stock?" check for the last unit.
Fix One: Never Decrement From a Read Value
The first fix is cheap and you should do it unconditionally. Let the database compute the new value in the UPDATE, and put the guard in the WHERE clause so the write itself refuses to go below zero:
// Atomic conditional decrement — the check and the write are one statement
const result = await db
.update(products)
.set({ stock: sql`${products.stock} - 1` })
.where(and(eq(products.id, productId), gte(products.stock, 1)))
.returning({ stock: products.stock });
if (result.length === 0) {
// WHERE matched nothing: stock was already 0 at write time
throw new Error("Out of stock");
}
This is a single atomic statement. Postgres takes a row lock for the duration of the UPDATE automatically, so two concurrent writers can't both satisfy 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 <= stock)
);
-- available = stock - reserved
A reservation raises reserved. On successful payment, you convert it: stock down, reserved down, net available unchanged. On failure or expiry, you just drop reserved and the unit is available again — no separate restock path to forget. The reserved <= stock check constraint is your last line of defence: even if the application logic has a bug, the database physically cannot record more reservations than you have units.
Reservation is more moving parts — a reserved column, an expiry mechanism, a sweep job. But it's the model that survives asynchronous payment, and asynchronous payment is normal in European B2B. On pi-pi.ee the checkout accepts SEPA Direct Debit, bank transfer, and Multibanco alongside cards. Those settle later: the Stripe webhook arrives as payment_intent.requires_action first and payment_intent.succeeded only once the money moves. A hard decrement can't model "held but not yet paid" — a reservation can.
For a store with scarce stock and any async payment method, use reservations. If you're card-only and want the simplest thing that's correct, the atomic conditional decrement above is enough — just make sure your payment-failure webhook restocks.
Reserving a Multi-Line Cart Atomically
A real cart is several products at once, and the requirement is all-or-nothing: reserve every line or none. Reserving three of four items and failing the fourth leaves you holding stock for a sale that can't complete.
This is where you need an explicit transaction and row locking. Lock each product row, verify availability, then reserve — all inside one transaction that either commits whole or rolls back whole:
// lib/reservations.ts
export async function reserveCart(
cart: { productId: string; qty: number }[],
orderId: string,
ttlMinutes = 15
) {
// Lock rows in a deterministic order to avoid deadlocks (see below)
const ids = cart.map((l) => 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 < line.qty) {
throw new OutOfStockError(line.productId); // rolls back the whole tx
}
}
// All lines fit — now reserve
const expiresAt = new Date(Date.now() + ttlMinutes * 60_000);
for (const line of cart) {
await tx
.update(products)
.set({ reserved: sql`${products.reserved} + ${line.qty}` })
.where(eq(products.id, line.productId));
await tx.insert(reservations).values({
orderId,
productId: line.productId,
qty: line.qty,
expiresAt,
});
}
});
}
Two things are doing the work here.
.for("update") locks every product row for the transaction's lifetime. Any concurrent reserveCart touching the same product blocks at the SELECT until this transaction commits or rolls back — so it reads post-reservation numbers, never stale ones. The availability check and the reservation write can't be interleaved by another cart.
We check all lines before writing any. If line four doesn't fit, the exception rolls the transaction back and the reservations we already wrote for lines one through three never persist. All-or-nothing falls out of transaction semantics for free.
The Deadlock You Will Otherwise Hit
The .sort() on ids is not cosmetic. If two carts lock overlapping products in different orders, you deadlock: cart A locks product X and waits for Y; cart B locked Y and waits for X. Postgres detects the cycle and kills one transaction with a deadlock error — a checkout failure you caused yourself.
The fix is a discipline, not a feature: always acquire row locks in a consistent global order. Sorting by primary key before locking guarantees every transaction grabs shared rows in the same sequence, so a cycle can't form. This is the single most common way a "correct" locking implementation still falls over under load, and it never shows up in single-threaded tests.
The Payment-Confirmation Window
Here's the part the naive decrement can't touch at all. Between reserving stock at checkout and confirming payment, there's a window. For cards it's seconds. For SEPA or bank transfer it can be days. During that window the unit is held but not sold, and three things can happen: payment succeeds, payment fails, or nothing happens because the customer walked away.
You handle all three, and the reservation model makes each one a small, obvious operation.
Payment succeeds. The Stripe webhook fires payment_intent.succeeded. Convert the reservation into a real decrement, atomically:
// In the Stripe webhook, on payment_intent.succeeded
await db.transaction(async (tx) => {
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 after the first oversold order is always more expensive than getting it right up front. If your store sells scarce stock and you want the checkout to survive its own busiest day, get in touch.
Top comments (1)
The deterministic lock ordering point is the one people skip, so good on you for giving it its own section. One thing I would add, in the same spirit of "correct in review, wrong under load".
In Fix One you check the outcome of the write:
That check is what makes the atomic decrement safe. The WHERE either matched or it did not, and you find out which. But inside reserveCart the reservation write does not do the same thing:
If that UPDATE ever affects zero rows, the INSERT still runs. You get a reservation row recorded against a product whose
reservedcounter never moved, and becausereserveddid not move, thereserved <= stockconstraint you are relying on as the last line of defence never fires. The oversell comes back, silently, and now it is invisible in the products table too.The FOR UPDATE above makes that unlikely, since the row was just locked and read. The case where it bites is when something narrows the write that did not narrow the read. The common one in this stack is row-level security: if
productshas RLS enabled with an UPDATE policy, the update is filtered by that policy independently of what your SELECT returned. A row you locked and verified can still fail to update, and Postgres reports that as zero rows rather than as an error.Cheap fix, same shape as Fix One:
Two benefits beyond the RLS case. The availability condition now lives in the WHERE as well as in your pre-check, so the write is self-guarding even if the loop is ever refactored away from the locked read. And a failure rolls back the whole transaction, which is the behaviour you already want. The same applies to the webhook's commit, which also assumes it matched.
On read replicas: agreed, and the subtler version is that RLS-filtered reads look exactly like replica lag. If a policy hides a row the query returns fewer rows rather than an error, so "the stock count looks wrong" and "this role cannot see this row" produce identical symptoms at the application layer. Worth checking relrowsecurity before blaming replication.