Prevent overselling: fix the inventory race condition in PostgreSQL
Guide/ 13 min read
Two orders for the last unit, both accepted. Why read-then-write oversells, and two PostgreSQL fixes: SELECT … FOR UPDATE and a conditional UPDATE.
- Node.js
- Databases
In this article (8)
Overselling happens when two purchases read the same stock before either one subtracts it: both see one unit and both create an order. To prevent overselling in PostgreSQL, lock the row with SELECT … FOR UPDATE before deciding, or decide and subtract in a single conditional UPDATE with RETURNING.
This guide walks through the race condition with two simultaneous purchases, both fixes in SQL and in Node with the postgres driver (Postgres.js), how to choose, and a script that fires purchases in parallel to prove it. If you take payments online too, the sibling problem is charging twice, covered in idempotency keys for payments.
What an inventory race condition looks like
A race condition is a bug that depends on how two simultaneous operations interleave. With inventory the classic case is this: one hoodie left (product 1 in these tables), two shoppers pay at almost the same moment, and each server does exactly what it would do alone. These are the tables for the examples:
CREATE TABLE products (
id int PRIMARY KEY,
name text NOT NULL,
stock int NOT NULL CHECK (stock >= 0)
);
CREATE TABLE orders (
id serial PRIMARY KEY,
product_id int NOT NULL REFERENCES products (id),
qty int NOT NULL CHECK (qty > 0),
created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO products (id, name, stock) VALUES (1, 'Hoodie M', 1);
And this is the purchase written the most natural way, "load the product, subtract one, save":
const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
export async function buyNaive(sql, productId, qty, workMs = 0) {
return sql.begin(async (tx) => {
const [p] = await tx`SELECT stock FROM products WHERE id = ${productId}`;
if (!p || p.stock < qty) return null;
await sleep(workMs);
await tx`UPDATE products SET stock = ${p.stock - qty} WHERE id = ${productId}`;
const [order] = await tx`INSERT INTO orders (product_id, qty) VALUES (${productId}, ${qty}) RETURNING id`;
return order.id;
});
}
sql.beginopens a transaction on a reserved connection; everything that usestxruns inside it.- The
SELECTreads the stock, and Node decides whether there is enough. sleep(workMs)stands in for whatever happens between the read and the write: prices, shipping, a call to another service. In production that gap exists, even if it is milliseconds.- The
UPDATEwrites the value Node computed:p.stock - qty. - The
INSERTcreates the order, and the transaction commits when the function returns.
Now two shoppers at once. A reads 1. Before A writes, B reads 1 too, because under Read Committed, PostgreSQL's default isolation level, each query sees what was committed when it started. Both compute 1 - 1 = 0, both write 0, both create an order.
Scroll the figure sideways to see all of it
Two details trip people up. The transaction does not prevent it: BEGIN and COMMIT group statements, they do not stop another transaction from reading the same row. And a CHECK (stock >= 0) does not fire either, because nobody writes a negative number: both write 0. The stock looks right and the orders are wrong.
Fix 1: a transaction with SELECT … FOR UPDATE
The first fix is to lock the row when you read it. Per the explicit locking documentation, FOR UPDATE locks the rows it reads as though for update: other transactions that try to UPDATE, DELETE or SELECT … FOR UPDATE them wait until the current transaction ends. And the waiting SELECT … FOR UPDATE, once it proceeds, locks and returns the updated row.
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- your code decides: if stock < 1, ROLLBACK
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (product_id, qty) VALUES (1, 1) RETURNING id;
COMMIT;
In Node, with the same driver, only one thing changes from the naive version: the FOR UPDATE, plus the UPDATE now subtracts from the row's value rather than the one you read.
export async function buyWithLock(sql, productId, qty, workMs = 0) {
return sql.begin(async (tx) => {
const [p] = await tx`SELECT stock FROM products WHERE id = ${productId} FOR UPDATE`;
if (!p || p.stock < qty) return null;
await sleep(workMs);
await tx`UPDATE products SET stock = stock - ${qty} WHERE id = ${productId}`;
const [order] = await tx`INSERT INTO orders (product_id, qty) VALUES (${productId}, ${qty}) RETURNING id`;
return order.id;
});
}
- The
SELECT … FOR UPDATEreads and locks the product row. If B arrives while A holds the lock, B waits right there, reading nothing. - With the row locked, the decision in Node is safe: nobody else can change that stock until the
COMMITorROLLBACK. stock = stock - ${qty}subtracts in the database. Under the lock the value you read would work too, but this way the statement is correct on its own.- When the function returns,
sql.begincommits and releases the lock. B gets the row with stock 0 and returnsnull: no order.
Scroll the figure sideways to see all of it
The cost is that the lock lasts for the whole transaction. Do anything slow inside and every purchase of that product queues behind the previous one. Do not call the payment gateway while holding the row. And if one purchase touches several products, always lock them in the same order (by id, say): the docs recommend acquiring locks in a consistent order, because otherwise PostgreSQL detects the deadlock and aborts one of the transactions.
SELECT's locking clause has two variants: NOWAIT raises an error instead of waiting, and SKIP LOCKED skips locked rows. The docs say the latter gives an inconsistent view of the data: it is meant for work queues, not for stock, where it would make a locked product look like it does not exist.
Fix 2: a conditional UPDATE with WHERE and RETURNING
The second fix is to not split the decision from the write: one UPDATE that subtracts only if there is enough and returns the row if it did.
BEGIN;
UPDATE products SET stock = stock - 1
WHERE id = 1 AND stock >= 1
RETURNING stock;
-- 0 rows: out of stock, ROLLBACK; 1 row: create the order
INSERT INTO orders (product_id, qty) VALUES (1, 1) RETURNING id;
COMMIT;
In Node:
export async function buyConditional(sql, productId, qty) {
return sql.begin(async (tx) => {
const [p] = await tx`
UPDATE products SET stock = stock - ${qty}
WHERE id = ${productId} AND stock >= ${qty}
RETURNING stock`;
if (!p) return null;
const [order] = await tx`INSERT INTO orders (product_id, qty) VALUES (${productId}, ${qty}) RETURNING id`;
return order.id;
});
}
WHERE id = ${productId} AND stock >= ${qty}is the decision. For one unit it is the same asstock > 0; with quantities, compare against the quantity ordered.RETURNING stockreturns the new stock of every row actually updated. Per the UPDATE documentation, updating no rows is not an error: the result is just empty.- With no row,
pisundefinedand the function returnsnull: out of stock. With a row, it creates the order in the same transaction.
Why does this hold up with two purchases at once? Because of how Read Committed behaves: if another transaction is already updating the row, the second UPDATE waits for it to finish. If the first one commits, PostgreSQL re-evaluates the WHERE against the new version of the row. B finds stock = 0, stock >= 1 no longer holds, and nothing is updated.
There is a lock here too: the UPDATE locks the row until the COMMIT. The difference is that it starts at the UPDATE, not at an earlier SELECT, and no application logic runs in between.
Which one to use to prevent overselling
Scroll the figure sideways to see all of it
| Criterion | SELECT … FOR UPDATE | Conditional UPDATE |
|---|---|---|
| Statements per purchase | One SELECT and one UPDATE | One UPDATE |
| Where the decision happens | In your code, with the row locked | In the WHERE |
| Row locked from | The SELECT until COMMIT | The UPDATE until COMMIT |
| Rules that need to read first | Yes: per-customer limits, pre-orders, several tables | Only what fits in the WHERE |
| Risk if you do something slow inside | High: each purchase waits for the previous one | Low, as long as nothing runs between the UPDATE and the COMMIT |
| Use it for | Decisions that need several reads | Decrementing stock, the common case |
Our recommendation: start with the conditional UPDATE. It is one statement, it does not rely on the application code getting it right, and it locks the least. Move to FOR UPDATE when the decision needs to read more than the stock.
How to test it with concurrent requests
A sequential test always passes: one purchase finishes before the next one starts. To see the race you have to fire many purchases at once, each on its own connection. This script fires N purchases in parallel at the same product with each version and checks the result with assert:
import assert from "node:assert/strict";
import postgres from "postgres";
import { buyNaive, buyWithLock, buyConditional } from "./stock.mjs";
const N = Number(process.argv[2] ?? 20);
const sql = postgres(process.env.DATABASE_URL, { max: N });
await Promise.all(Array.from({ length: N }, () => sql`SELECT 1`));
async function race(name, buy, stock) {
await sql`TRUNCATE orders RESTART IDENTITY`;
await sql`UPDATE products SET stock = ${stock} WHERE id = 1`;
const start = performance.now();
const results = await Promise.allSettled(Array.from({ length: N }, () => buy(1)));
const ms = Math.round(performance.now() - start);
const [{ orders }] = await sql`SELECT count(*)::int AS orders FROM orders`;
const [{ stock: left }] = await sql`SELECT stock FROM products WHERE id = 1`;
const failed = results.filter((r) => r.status === "rejected").length;
console.log(`${name.padEnd(16)} stock ${stock}, ${N} buyers -> ${orders} orders, stock left ${left}, ${failed} errors, ${ms} ms`);
return { orders, left, failed };
}
try {
const naive = await race("read then write", (qty) => buyNaive(sql, 1, qty, 50), 1);
assert.ok(naive.orders > 1, "the naive version should oversell");
for (const [name, buy] of [
["FOR UPDATE", (qty) => buyWithLock(sql, 1, qty, 50)],
["UPDATE ... WHERE", (qty) => buyConditional(sql, 1, qty)],
]) {
assert.deepEqual(await race(name, buy, 1), { orders: 1, left: 0, failed: 0 });
assert.deepEqual(await race(name, buy, 5), { orders: 5, left: 0, failed: 0 });
}
console.log("OK: with both solutions, never more orders than units");
} finally {
await sql.end();
}
postgres(process.env.DATABASE_URL, { max: N })opens a pool of up to N connections. Postgres.js defaults to 10; with fewer connections than purchases, part of the test stops being concurrent.- The N initial
SELECT 1queries open every connection before the race, so connecting does not stagger the purchases. raceemptiesorders, sets the stock and fires the N purchases withPromise.allSettled, which waits for all of them even if some fail.- Then it counts the orders, reads the remaining stock and the errors, and prints them with the total time.
- The naive version, with 50 ms between read and write, must create more than one order for one unit; the script fails if it cannot show the overselling.
- With
FOR UPDATEand with the conditionalUPDATEit requires exactly 1 order with stock 1, and 5 orders with stock 5, with no errors.
You need a throwaway PostgreSQL, for example a Docker container on port 55432. Never run it against your production database: the script empties the orders table. With the database up:
export DATABASE_URL=postgres://postgres:test@localhost:55432/postgres
psql "$DATABASE_URL" -f schema.sql
node race.mjs 20
To watch the lock yourself, open two psql sessions. In the first run BEGIN; and the SELECT … FOR UPDATE, without committing. In the second, the same SELECT … FOR UPDATE: it hangs. COMMIT the first one and the second answers immediately with the updated row.
Common mistakes
- Thinking
BEGINandCOMMITare enough. Under Read Committed, a transaction does not stop another one from reading the same row before you write it. - Computing the new stock in the application.
SET stock = ${valueYouRead - 1}overwrites whatever another purchase wrote. Subtract in the database,stock = stock - 1, with the condition in theWHERE. - Doing slow work while holding the row. Calling the payment gateway inside the
FOR UPDATEqueues up every purchase of that product. - Using
SKIP LOCKEDfor stock. It skips the locked row, so the product looks missing while someone else is buying it. - Locking several products in different orders. Two carts with the same products in reverse order can deadlock. Sort by
id. - Relying on
CHECK (stock >= 0)alone. It is a good safety net when you subtract withstock - 1, because anUPDATEthat would go negative fails. But it cannot see the naive version, which writes 0 twice. - Testing with a small pool. With fewer connections than purchases, the test is not concurrent and passes even when the code is wrong.
Frequently asked questions
What if I raise the isolation level to REPEATABLE READ?
The second transaction that tries to change the row fails with could not serialize access due to concurrent update, and the docs say to retry the whole transaction. It prevents overselling, but you now have to write retries. For decrementing stock, the conditional UPDATE under Read Committed is simpler.
How do I handle several units or several products?
With quantities, the condition is stock >= quantity, as in the code above. With several products, one conditional UPDATE per product inside the same transaction, always in the same order; if any of them returns no row, ROLLBACK and nothing is subtracted.
What if the payment fails after the stock is decremented?
Our recommendation: decrement and create the order in one transaction, charge afterwards and, if the charge fails, give the unit back with stock = stock + quantity and mark the order as cancelled. To keep the charge from repeating, put an idempotency key on the payment.
Does this work with an ORM?
It depends on what it generates. If your code does "load the product, subtract in memory, save", that is the naive version. Look up how your ORM emits a conditional UPDATE or a SELECT … FOR UPDATE, or write just this step in plain SQL.
Checklist
- No purchase reads the stock and writes it later without a lock.
- The decrement is an
UPDATE … WHERE stock >= quantity RETURNING, or comes after aSELECT … FOR UPDATE. - A result with no rows is handled as "out of stock", not as an error.
- No slow calls to other services run inside the transaction.
- A cart's products are always locked in the same order.
- The table has
CHECK (stock >= 0)as a safety net. - A test with N parallel purchases, on at least N connections, leaves one order for the last unit.
Overselling and double charging are the same kind of bug: two simultaneous operations that each think they are alone. The other half is in how to stop double charges with an Idempotency-Key. If an AI agent is going to touch your checkout, ask it for a plan first with Claude Code plan mode.
Saw this on Instagram? Comment STOCK on the Reel and I'll send you the guide.
Written by

Published October 30, 2026 · updated November 1, 2026