Prevent Inventory Overselling in PostgreSQL (Tested)

To stop overselling in PostgreSQL, lock the stock rows with SELECT ... FOR UPDATE in a fixed order inside the allocation transaction. SKIP LOCKED is wrong here.

I built the allocation engine for a third-party logistics warehouse system, where two orders taking the same units would mean a picker walking to an empty shelf and a client's customer not getting their parcel. The rule we ended up with fits on one line: lock with FOR UPDATE, ordered by id, never SKIP LOCKED. For this post I tested each approach on PostgreSQL 17 on 10 October 2026, with two orders racing for the same ten units. Here is what each one did.

Key takeaways

• Read-then-write without a lock oversells. Two orders both saw 10 free and allocated 13.
• SKIP LOCKED invents out-of-stock. The second order saw 0 free while 3 units were really available, because it skipped rows the first order had locked.
• FOR UPDATE makes the second order wait, then read the true remaining stock. Both orders were served correctly.
• Lock rows in the same order everywhere, or two transactions can deadlock. Opposite orders deadlocked in my test; the same order never did.
• A CHECK constraint is a safety net, not the mechanism. It stopped overselling, but it also rejected an order that could have been filled.

The setup

One product, SKU-1, sits in two bins: 6 units in A-01 and 4 in B-07, so 10 on hand. Each row tracks on_hand and allocated, as fixed decimals (never floats for quantities or money). Two orders arrive at the same moment. The allocation code reads the free stock, does some work (in the test, a 300 ms pause standing in for pricing, rules and other queries), then updates the rows and commits.

CREATE TABLE stock (
  id        int PRIMARY KEY,
  sku       text,
  bin       text,
  on_hand   numeric(18,4) NOT NULL,
  allocated numeric(18,4) NOT NULL DEFAULT 0,
  CHECK (allocated <= on_hand)
);
INSERT INTO stock VALUES (1,'SKU-1','A-01',6,0), (2,'SKU-1','B-07',4,0);

The results

First, order A wants 7 and order B wants 6. Only one of them can be filled:

no lock                  | A: allocated 7 | B: allocated 6        | allocated 13/10
no lock + CHECK          | A: allocated 7 | B: ERROR ... violates check constraint "stock_check"
FOR UPDATE SKIP LOCKED   | A: allocated 7 | B: OUT OF STOCK (saw 0 free)
FOR UPDATE ORDER BY id   | A: allocated 7 | B: OUT OF STOCK (saw 3 free)

Then order A wants 7 and order B wants 3. Both can be filled exactly:

no lock                  | A: allocated 7 | B: allocated 3        | allocated 10/10
no lock + CHECK          | A: allocated 7 | B: ERROR ... violates check constraint "stock_check"
FOR UPDATE SKIP LOCKED   | A: allocated 7 | B: OUT OF STOCK (saw 0 free)
FOR UPDATE ORDER BY id   | A: allocated 7 | B: allocated 3        | allocated 10/10

Only the last line is right in both runs.

Why "no lock" oversells

Under PostgreSQL's default isolation (read committed), a plain SELECT doesn't block anyone. Both transactions read "10 free" before either wrote anything, both decided they had enough, and both allocated. The UPDATE ... SET allocated = allocated + n itself is atomic, so no update was lost, but the decision was made on stale data. That is the classic check-then-act race, and it only shows up under load, which is why it passes every manual test.

Why the CHECK constraint isn't enough

The constraint did its job in the first run: the database refused to let allocated exceed on hand, even though the code tried. But look at the second run. Order B had read "6 free in A-01, 4 in B-07" before A committed, so it tried to take its 3 units from A-01, which A had just filled. The row check failed, and B was rejected while B-07 still had 4 free units.

Keep the constraint. It protects you from bugs you haven't written yet. Just don't rely on it to allocate correctly; that needs the lock.

Why SKIP LOCKED is wrong for allocation

SKIP LOCKED is a great tool with a specific meaning: "give me rows nobody else is working on". That's exactly right for job queues and for handing out warehouse tasks, where any free task will do. My warehouse system uses it for those.

For allocation, the rows another order has locked still contain stock you might need. Skipping them means pretending that stock doesn't exist. In the test, order B reported out of stock with 3 units sitting on the shelf. In production that is a lost sale, or a backorder email to a customer whose item was in the building.

The fix: FOR UPDATE, in a fixed order

async function allocate(db, sku, qty) {
  await db.query('BEGIN');
  try {
    const { rows } = await db.query(
      `SELECT id, on_hand - allocated AS free
         FROM stock
        WHERE sku = $1 AND on_hand > allocated
        ORDER BY id
          FOR UPDATE`,
      [sku],
    );
    const free = rows.reduce((s, r) => s + Number(r.free), 0);
    if (free < qty) { await db.query('ROLLBACK'); return { ok: false, free }; }

    let left = qty;
    for (const r of rows) {
      if (!left) break;
      const take = Math.min(left, Number(r.free));
      await db.query('UPDATE stock SET allocated = allocated + $1 WHERE id = $2', [take, r.id]);
      left -= take;
    }
    await db.query('COMMIT');
    return { ok: true };
  } catch (e) {
    await db.query('ROLLBACK');
    throw e;
  }
}

The second transaction's SELECT ... FOR UPDATE blocks until the first commits, then PostgreSQL re-checks the locked rows and returns their new values. Two notes for real code: use Prisma.Decimal or a decimal library instead of Number for fractional quantities, and keep the transaction short, because every order for that SKU queues behind it.

In production the "which bins first" decision usually comes from rules (expiry date first, the pick face before reserve stock), so the ORDER BY becomes something like expiry_date, bin_rank, id. Keep id last so the order is always the same.

Lock order and deadlocks

When two transactions each lock one row and then want the other's, PostgreSQL detects the cycle and kills one of them. I ran exactly that:

--- opposite lock order
tx1: ok
tx2: deadlock detected
--- same lock order
tx1: ok
tx2: ok

Every query that locks stock rows has to lock them in the same order, which is why the allocation query ends in ORDER BY ..., id. If you still see occasional deadlocks, retry the transaction once; PostgreSQL has already rolled it back cleanly.

What else protects stock in a real warehouse system

• One function changes stock. In my system nothing writes the balance table except a single postMovement() function, so there is one place to get locking right.
• An append-only ledger. Every movement is a new row that a database trigger refuses to edit or delete; corrections are reversing rows. A nightly job checks the balances still equal the sum of the ledger.
• Idempotency keys on every write. Handheld scanners retry on bad Wi-Fi. The key is claimed before the handler runs, so two retries arriving together can't both allocate.
• Constraints as the last line: no negative stock, no allocation above on hand, unique serial numbers.

The same "claim first, then act" idea protects payments; I wrote it up for Stripe webhooks.

Frequently asked questions

Does SERIALIZABLE isolation solve overselling?

It prevents the anomaly, but by aborting one of the conflicting transactions with a serialization error that you must retry. Row locks with FOR UPDATE make the second order wait instead, which is simpler to reason about for allocation.

When should I use SKIP LOCKED?

For work queues: background jobs, task assignment, "give me the next pick task nobody has claimed". Any free row is as good as another there. Never for reading quantities you need to be accurate.

Isn't a CHECK constraint enough to prevent overselling?

It prevents allocated stock from exceeding on-hand stock, so it stops the worst outcome. In my test it also rejected an order that could have been filled from another bin, because that order planned its allocation from stale data. Use it as a safety net next to proper locking.

Will FOR UPDATE slow my app down?

Only orders for the same rows wait for each other, and only for as long as the transaction lasts. Keep the transaction short: read, decide, update, commit, with no network calls in between.

Does this work with Prisma?

Prisma has no FOR UPDATE in its query builder, so run the locking select with $queryRaw inside an interactive $transaction, then do the updates in the same transaction.

Building inventory or order software?

I build warehouse, inventory and order systems where stock stays correct under real load: allocation, ledgers, handheld scanner apps and client portals. See my web development services or describe your stock problem.