Two customers try to buy the last item at the same time. How do you handle inventory?

Availability is a claim you grant, not a number you read

The race is a symptom of a missing concept, so start with the model. A row saying on_hand = 1 cannot answer the question checkout is asking. Checkout is not asking "is there one left", it is asking "may I have the one that is left", and only the second can be made safe, because only the second produces a record of who was told yes.

Split the quantity into three parts. on_hand is what the warehouse believes it physically holds, reserved is the sum of outstanding claims that have not yet shipped, and the safety buffer is a number you choose rather than one the system derives. What the storefront advertises is on_hand - reserved - buffer, conventionally called available-to-promise, and it is the only figure a customer should see.

Adding to a cart grants nothing, which is deliberate: carts are abandoned at rates that make cart-time reservation ruinous for anything genuinely scarce. At a defined point in checkout, usually immediately before payment authorisation, you create a reservation that moves one unit from available into reserved, and shipment converts that reservation into a permanent reduction of on_hand. With availability expressed as a granted claim the original question has a boring answer: one reservation succeeds, the other fails, and you have a row explaining which.

The check and the decrement have to be one statement

The defect that produces oversell is almost never the absence of a lock. It is application code shaped like this: read the quantity, decide in memory that it is sufficient, then write the decrement. Between the read and the write another request does the same thing, both decide from the same stale value, and both proceed. Wrapping the two statements in a transaction does not fix it, because the read took no lock and read-committed isolation permits both transactions to observe the same starting value.

The fix is to put the condition where the database can enforce it, in the write itself.

-- The guard lives in the WHERE clause, so the database - not the
-- application's memory - decides whether the decrement is legal.
UPDATE inventory
   SET reserved = reserved + 1
 WHERE sku = 'ABC-1'
   AND on_hand - reserved - buffer >= 1;
-- Zero rows affected means you lost the race. Treat that as the
-- expected outcome for one of the two customers, not an error.

The subtlety worth being precise about is what happens to the second statement while the first is uncommitted. Under read committed in PostgreSQL, the second UPDATE blocks on the row lock, and when the first commits it re-evaluates its WHERE clause against the newly committed row rather than its original snapshot, so the guard holds. Under repeatable read the same situation raises a serialisation failure instead, which your code must catch and retry. Either behaviour is safe; silently assuming the first without checking your isolation level is how this bug ships.

Optimistic and pessimistic, and what each costs

Pessimistic control takes the lock first: SELECT ... FOR UPDATE on the inventory row, then compute, then write, all inside one transaction. It serialises every buyer of that SKU behind a single row lock, which is correct and, for one hot item during a launch, is exactly the wrong shape. Lock wait time grows with concurrency, and because the lock is held for the duration of the transaction, any slow call made while holding it - a fraud check, a pricing service, a payment gateway - multiplies into every waiting request. If you take this route, the rule is that no network call happens inside the locked span.

Optimistic control takes no lock and detects interference instead: read the row with its version, then make the write conditional on the version being unchanged. A mismatch means someone else got there first, so you retry from the read. That is cheaper when contention is rare and worse when it is not, because under heavy contention most attempts do the work twice and discard it. The conditional UPDATE above is the compact form of the same idea, using the invariant itself as the condition rather than a version counter, which avoids the retry loop for the common case.

Reservations must expire, and expiry is a design decision

A reservation with no expiry is a slow leak that converts into lost sales. Every abandoned checkout permanently removes a unit from availability, and because abandonment is silent you find out from a stock report weeks later.

So a reservation carries an expiry, typically ten to fifteen minutes for a standard checkout, and something longer where the payment method itself is slow - bank transfers and cash-on-delivery variants can legitimately need hours. Two mechanisms exist and you want both. A sweeper job releases expired reservations on a schedule, which keeps advertised availability honest for browsing customers. Lazy expiry excludes already-expired rows from the availability calculation at read time, which means a customer is never blocked by a reservation that the sweeper has not reached yet. Relying only on the sweeper makes your correctness a function of cron latency.

The window itself is a trade: short windows recycle stock quickly and will cut off real customers mid-payment, long windows are gentler and starve availability. Pick the number from your observed checkout-completion time distribution, say which percentile you chose, and extend it once when the payment provider confirms an authorisation is genuinely in flight.

Overselling versus lost sales is a business choice, not a bug

The instinct is to say oversell must never happen. That instinct is wrong often enough that saying it unprompted is a strong signal. Driving oversell to zero means holding a buffer, and every unit of buffer is stock you own and refuse to sell. On a fast-moving fungible item where a shortfall means a one-day delay and an apology, a small oversell rate is far cheaper than the inventory you would freeze to prevent it. On a single-unit listing - a used item, a piece of art, an allocated seat - oversell is not a delay, it is a cancellation and a refund, and there the buffer is worth its cost.

So you are choosing a point on a curve between two costs: the margin lost on stock you would not sell, and the goodwill lost on orders you cannot fill. That makes oversell rate a metric with a target rather than an incident class, and it tells you what to build - a defined path for a shortfall, meaning partial shipment, substitution, backorder, or cancellation with compensation, chosen by category rather than improvised by whoever is on support that afternoon.

The warehouse is the record, and it will disagree with you

Here is where adequate answers stop and strong ones continue. Everything above makes the database internally consistent, and internal consistency is not the same as being right. The number in the row is a belief about a physical shelf, and that belief decays continuously: units are damaged, misplaced on the wrong shelf, mispicked, stolen, returned in unsellable condition, or counted as one SKU when they are another. In omnichannel retail the drift is worse, because a store associate sold the last one over the counter and the point-of-sale sync runs on a delay.

So a perfectly serialised inventory service will still promise stock that does not exist, and no amount of concurrency control touches that failure mode. What does is treating the warehouse as the authority and the database as a cache with a measurable error rate: cycle counting to correct drift continuously rather than one annual count, reconciliation that reports the size of the discrepancy so you can see whether it is growing, and a pick-failure event that flows back into the order as a first-class outcome instead of an exception handled by email.

Concurrency control is the easy half of this question. The design that survives production is the one that assumes the count is wrong, measures how wrong, and has a defined path for an order it has already promised and cannot fill.