Stock: the hard problem nobody warns you about

This is the lesson that separates people who have shipped a shop from people who have built one.

Stock looks like a number you subtract. It is a number two people can subtract at the same time, and everything hard about it follows from that.

The bug you cannot find by testing

Here is the obvious way to sell something:

const variant = await tx.variant.findUnique({ where: { id } });

if (variant.stock < quantity) {
  throw new Error("Not enough stock");
}

await tx.variant.update({
  where: { id },
  data: { stock: variant.stock - quantity },
});

Read it. It is correct. Every test you write by hand will pass, because you are one person clicking one button.

Now two customers buy the last bag of atta at the same moment:

time   request A                    request B
────────────────────────────────────────────────────────
t1     read stock = 1
t2                                  read stock = 1
t3     1 >= 1, fine
t4                                  1 >= 1, fine
t5     write stock = 0
t6                                  write stock = 0

Two orders. One bag. Stock says 0, so nothing looks wrong in the database. The shop finds out when the delivery person is standing in the shop with two orders and one bag, and somebody has to ring a customer and apologise.

This is a race condition, and specifically a read-then-write race. It happens in the gap between deciding and doing.

A transaction does not fix it

The first instinct is to wrap it in a transaction, and it does not help:

await prisma.$transaction(async (tx) => {
  const variant = await tx.variant.findUnique({ where: { id } });
  if (variant.stock < quantity) throw new Error("Not enough");
  await tx.variant.update({ where: { id }, data: { stock: variant.stock - quantity } });
});

PostgreSQL's default isolation level is READ COMMITTED, and so is Prisma's. Under READ COMMITTED, both transactions happily read stock = 1. The reads do not block each other; only the writes do, and by then both have already decided.

A transaction guarantees that your steps happen all or nothing. It does not guarantee that nobody else acted in between. Those are different properties and conflating them is extremely common.

What about `SERIALIZABLE`?

await prisma.$transaction(fn, { isolationLevel: "Serializable" });

This genuinely does fix it. PostgreSQL detects that the two transactions cannot be ordered and aborts one with a serialization failure.

The cost is that you must now catch and retry that failure, on every checkout, forever. And SERIALIZABLE takes predicate locks across the whole transaction, so every checkout pays for it — including the vast majority where nobody is competing for anything.

It is the right tool for genuinely complex invariants. For "do not let this number go below zero" it is a cannon aimed at a nail.

Put the condition in the WHERE clause

The fix is to stop reading:

const claimed = await tx.variant.updateMany({
  where: {
    id: item.variantId,
    isActive: true,
    stock: { gte: item.quantity },
  },
  data: { stock: { decrement: item.quantity } },
});

if (claimed.count === 0) {
  throw AppException.insufficientStock({ … });
}

One statement. The database evaluates stock >= quantity and performs the decrement while holding a row lock, because that is what a single UPDATE does.

Replay the race:

time   request A                         request B
──────────────────────────────────────────────────────────────
t1     UPDATE … WHERE stock >= 1
       (takes the row lock, matches)
t2                                       UPDATE … WHERE stock >= 1
                                         (waits for the lock)
t3     writes stock = 0, commits
t4                                       re-evaluates against stock = 0
                                         matches nothing → count: 0

Request B gets count: 0 and throws. No read, no race, no retry loop, no isolation level to reason about.

{ decrement: item.quantity } matters too. It compiles to stock = stock - $1 — arithmetic the database does on the current value, not a number your application calculated from a stale read.

`count === 0` is the only signal

if (claimed.count === 0) { … }

This is the part to be careful about. updateMany does not throw when nothing matches — it returns { count: 0 } and carries on. A bare await tx.variant.updateMany(...) with no check is a silent failure: the order is created, the stock was never taken, and nothing anywhere complains.

Every conditional claim must check its count. Treat an unchecked updateMany the way you would treat an ignored catch.

Why `updateMany` for a single row

It reads oddly, and it is deliberate. Prisma's update throws when the where matches nothing, and — more importantly — its where only accepts unique fields, so you cannot express id = X AND stock >= 3 at all. updateMany takes a full filter. That is the whole reason it is here.

Reading the real number, safely

The error message should say how many are actually left. But reading the number is the thing we just spent a page avoiding.

if (claimed.count === 0) {
  const current = await tx.variant.findUnique({
    where: { id: item.variantId },
    select: { stock: true },
  });

  throw AppException.insufficientStock({
    variantId: item.variantId,
    requested: item.quantity,
    available: current?.stock ?? 0,
  });
}

This is safe, and it is worth understanding why. The throw rolls the whole transaction back. Nothing has been taken from anybody. The number is only going into a message, and if it is one out by the time the customer reads it, the worst case is that they retry and get told again.

Reading is dangerous when you act on it. Reading to explain yourself is fine.

When is stock actually taken?

The other half of the problem: when.

When something is added to the cart. Now a basket somebody abandoned on the bus is holding the last bag of atta hostage. You need expiry, a background job to release it, and a rule for what happens when the job is behind. Big shops do this for high-demand items — a concert ticket, an iPhone on launch day — and they pay for it in complexity.

When the order is placed. Stock is real until the moment somebody commits. Two people can have the same last item in their carts, and the first to check out gets it. The second is told at checkout.

The reference repository takes the second option, and that is decision 0004. For a kirana shop it is obviously right: the items are cheap, restocking is daily, and the failure mode is "sorry, that just went" rather than a lost ₹40,000 ticket.

The cost is honest and worth stating in the lesson: somebody can be told at the last step that they cannot have something. That is why the cart shows stock problems the moment it loads, and why the checkout error links back to the cart. You cannot remove the failure, so you make it arrive early and explain itself.

Never expose the number

inStock: row.stock > 0,

A boolean, not a count, in every catalogue response. Two reasons.

Stock level is commercial information — a competitor can work out your turnover by polling it. And "only 2 left!" on a grocery site is the kind of manufactured urgency that, on a kirana shop, reads as a trick.

The cart is the exception: once something is in your basket and the shop cannot fill it, you have a right to know how many they do have, because you need it to decide.

Check your work

Why read-then-write is broken: both requests read the same value before either writes, so both decide it is fine.

Why a transaction does not fix it: a transaction is all-or-nothing, not "nobody else acted in between". Under READ COMMITTED both reads succeed.

What SERIALIZABLE costs: serialization failures you must catch and retry, on every checkout, for a problem a WHERE clause solves for free.

Why the condition goes in the WHERE clause: the database checks and writes in one statement under a row lock, so the second request re-evaluates against the updated row.

Why count === 0 must be checked: updateMany does not throw. An unchecked call creates the order without taking the stock.

Why updateMany rather than update: update throws on no match, and its where only takes unique fields, so it cannot express id = X AND stock >= 3.

Why reading the stock in the error path is safe: the throw rolls the transaction back, and the number is only being used to explain.

Why stock is taken at order placement: taking it at add-to-cart means abandoned baskets hold stock, which needs expiry, a job, and a plan for when the job lags.

The cost of that choice: somebody can be refused at the last step — so the cart warns early and the checkout error links back.

Why the API returns a boolean: the level is commercial information, and scarcity messaging on a kirana shop reads as a trick.

Practice

Set a variant's stock to 1. Place an order for it and confirm the stock is 0 afterwards.
Try to order it again. Confirm you get a 409 with code INSUFFICIENT_STOCK and that details.available is 0.
Rewrite claimStock as read-then-write. Fire two checkouts at once with curl … & curl … & wait and confirm the stock goes negative.
Put it back and run the same two requests. Confirm exactly one succeeds.
Delete the if (claimed.count === 0) check. Order something out of stock and confirm an order is created with no stock taken. Sit with that for a moment.
Change { decrement: n } to a value computed in JavaScript from an earlier read, and re-run the concurrent test.
Add a second item to the cart that is in stock, and a first that is not. Confirm the whole order rolls back and the in-stock item was not taken.
Try isolationLevel: "Serializable" with the read-then-write version. Watch for the serialization failure and write down what you would have to do about it in production.
Find where inStock is computed and change it to return the count. Look at the API response and decide whether you would ship it.
Empty a variant's stock while it sits in your cart. Reload the cart and read what it tells you. Then try to check out anyway.

Next: putting it all together — the checkout flow.