Skip to content

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)
  1. What an inventory race condition looks like
  2. Fix 1: a transaction with SELECT … FOR UPDATE
  3. Fix 2: a conditional UPDATE with WHERE and RETURNING
  4. Which one to use to prevent overselling
  5. How to test it with concurrent requests
  6. Common mistakes
  7. Frequently asked questions
  8. Checklist

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;
  });
}
  1. sql.begin opens a transaction on a reserved connection; everything that uses tx runs inside it.
  2. The SELECT reads the stock, and Node decides whether there is enough.
  3. 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.
  4. The UPDATE writes the value Node computed: p.stock - qty.
  5. The INSERT creates 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.

1 unit left, 2 purchases at the same timetimeshopper Areads 1writes 0order #1shopper Breads 1writes 0order #2B reads before A writesresult: 2 orders for 1 unit; stock ends at 0, not at -1each purchase decided on a stale valuea CHECK (stock >= 0) cannot see it: nobody wrote a negative number

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;
  });
}
  1. The SELECT … FOR UPDATE reads and locks the product row. If B arrives while A holds the lock, B waits right there, reading nothing.
  2. With the row locked, the decision in Node is safe: nobody else can change that stock until the COMMIT or ROLLBACK.
  3. 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.
  4. When the function returns, sql.begin commits and releases the lock. B gets the row with stock 0 and returns null: no order.
SELECT … FOR UPDATE: B waits for A to finishshopper Alocks, reads 1writes 0COMMITrow products id = 1 lockedshopper BFOR UPDATEwaits, reads nothingreads 0no orderinside A's transactionSELECT stock FROM products WHERE id = 1 FOR UPDATEUPDATE products SET stock = stock - 1 WHERE id = 1B gets the updated row: 0 unitsthe lock lasts until A commits or rolls back

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;
  });
}
  1. WHERE id = ${productId} AND stock >= ${qty} is the decision. For one unit it is the same as stock > 0; with quantities, compare against the quantity ordered.
  2. RETURNING stock returns the new stock of every row actually updated. Per the UPDATE documentation, updating no rows is not an error: the result is just empty.
  3. With no row, p is undefined and the function returns null: 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

three ways to sell the last unitread then writeFOR UPDATEUPDATE … WHEREoversellsyesnonostatementsSELECT + UPDATESELECT + UPDATE1 UPDATErow lockednofrom SELECTfrom UPDATElogic betweenon stale dataon a held rownoneuse it fornothingrules that readplain decrementboth fixes leave exactly one orderunder Read Committed, an UPDATE that waited re-checks its WHERE

Scroll the figure sideways to see all of it

CriterionSELECT … FOR UPDATEConditional UPDATE
Statements per purchaseOne SELECT and one UPDATEOne UPDATE
Where the decision happensIn your code, with the row lockedIn the WHERE
Row locked fromThe SELECT until COMMITThe UPDATE until COMMIT
Rules that need to read firstYes: per-customer limits, pre-orders, several tablesOnly what fits in the WHERE
Risk if you do something slow insideHigh: each purchase waits for the previous oneLow, as long as nothing runs between the UPDATE and the COMMIT
Use it forDecisions that need several readsDecrementing 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();
}
  1. 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.
  2. The N initial SELECT 1 queries open every connection before the race, so connecting does not stagger the purchases.
  3. race empties orders, sets the stock and fires the N purchases with Promise.allSettled, which waits for all of them even if some fail.
  4. Then it counts the orders, reads the remaining stock and the errors, and prints them with the total time.
  5. 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.
  6. With FOR UPDATE and with the conditional UPDATE it 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 BEGIN and COMMIT are 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 the WHERE.
  • Doing slow work while holding the row. Calling the payment gateway inside the FOR UPDATE queues up every purchase of that product.
  • Using SKIP LOCKED for 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 with stock - 1, because an UPDATE that 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 a SELECT … 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

Luis Morales

Luis Morales

Frontend and Shopify developer, EcomLabs

@luismoralesdev on Instagram

Published October 30, 2026 · updated November 1, 2026

← Blog