Stopping Black Friday Oversells: A Stock Reservation Data Model

The bug that only shows up during the sale

Most checkout flows work fine on a normal Tuesday. You read the stock count, check that it is above zero, take payment, and decrement it. The flaw stays hidden until many buyers go after the same discounted item in the same few seconds. Then two requests both read stock = 1, both pass the check, and both charge a card for an item that only exists once.

This post walks through a data model that closes that gap. It uses short-lived reservations, atomic updates, and idempotent payment handling. The examples use PostgreSQL syntax, but the ideas carry over to any store that supports transactions or atomic conditional writes.

Why check-then-write fails

The naive version looks like this:

-- Request A and Request B run this at the same time
SELECT stock FROM products WHERE id = 42;   -- both see 1
-- application code: if stock > 0 then...
UPDATE products SET stock = stock - 1 WHERE id = 42;  -- both succeed, stock = -1

The read and the write are separate steps, and nothing ties them together. Wrapping them in a transaction does not fix this by itself under the default isolation level. The fix is to make the condition part of the write.

Step 1: Make the decrement conditional and atomic

UPDATE products
SET available = available - :qty
WHERE id = :product_id
  AND available >= :qty
RETURNING available;

If no row comes back, the item is sold out and the request fails cleanly. The database serializes the row update, so two buyers cannot both take the last unit. Add a CHECK (available >= 0) constraint as a backstop, so a bug elsewhere fails loudly instead of quietly producing negative stock.

That solves the race. It does not solve a second problem: stock taken by people who never finish paying.

Step 2: Separate "available" from "sold" with reservations

If you decrement at add-to-cart, abandoned carts lock up inventory for the whole sale. If you decrement only at payment capture, you are back to overselling. A reservation table sits between those two options:

CREATE TABLE stock_reservations (
  id           uuid PRIMARY KEY,
  product_id   bigint NOT NULL REFERENCES products(id),
  cart_id      uuid NOT NULL,
  qty          int NOT NULL CHECK (qty > 0),
  status       text NOT NULL CHECK (status IN ('held','committed','released')),
  expires_at   timestamptz NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON stock_reservations (status, expires_at);

The lifecycle:

1. Hold when the buyer starts checkout (not at add-to-cart). Run the conditional decrement on products.available and insert a held row with an expiry, in one transaction.
2. Commit when payment succeeds. Flip the status to committed. Stock is already gone from available, so nothing else changes.
3. Release when the hold expires or payment fails. Flip it to released and add the quantity back to available.

Pick the hold duration based on how long your real checkout takes, including 3-D Secure or bank redirects. Show the buyer a timer so the expiry does not surprise them.

Step 3: Release expired holds safely

A background worker reclaims expired reservations. Several workers can run it at once without fighting if each one skips rows that another has locked:

WITH expired AS (
  SELECT id, product_id, qty
  FROM stock_reservations
  WHERE status = 'held' AND expires_at < now()
  ORDER BY expires_at
  LIMIT 500
  FOR UPDATE SKIP LOCKED
), released AS (
  UPDATE stock_reservations r
  SET status = 'released'
  FROM expired e
  WHERE r.id = e.id
  RETURNING e.product_id, e.qty
)
UPDATE products p
SET available = p.available + s.total
FROM (SELECT product_id, sum(qty) AS total FROM released GROUP BY product_id) s
WHERE p.id = s.product_id;

The locking clause is documented in the PostgreSQL SELECT reference. Because the status filter is held, a reservation that was committed a moment earlier is never released by mistake.

Step 4: Make payment confirmation idempotent

The commit step is triggered by a payment event, and payment events are messy. Buyers double-click, mobile networks retry, and webhooks can arrive more than once or out of order. Two rules keep this under control:

• Send an idempotency key on the payment request. Use the reservation or order ID, so a retried request does not create a second charge. Stripe, for example, documents this pattern in its idempotent requests guide. Check your own provider's equivalent.
• Make the commit a conditional state transition.

UPDATE stock_reservations
SET status = 'committed'
WHERE id = :reservation_id AND status = 'held'
RETURNING id;

If the row comes back, this is the first time you have processed the event. If it does not, either the event was already handled (do nothing) or the hold expired before payment landed. That last case is the one teams forget.

The edge case: paid after the hold expired

A buyer who stalls on a bank redirect can pay after the worker has released their units. Decide your policy before the sale:

on payment_succeeded(reservation):
  if commit(reservation) succeeds: fulfil
  else if reservation.status == 'released':
      try conditional decrement on products.available
      if it succeeds: create committed reservation, fulfil
      else: refund automatically, notify buyer, log for support

Automatic refunds with a clear message are better than a support team finding the oversell by hand days later.

Keeping hot rows from becoming a bottleneck

When one doorbuster SKU gets most of the traffic, every checkout queues on the same row. Options, from simplest to most complex:

• Keep the transaction that touches products as short as possible. No network calls inside it.
• Split stock for a hot SKU into several bucket rows and decrement a random bucket with stock left.
• Move the counter for that SKU into an atomic in-memory counter (for example, a Redis DECRBY guarded by a script) and reconcile it to the database. This is fast, but now you have two sources of truth to reconcile.

Start with the first option and measure before you reach for the third.

Pre-sale verification list

• A concurrency test that fires many parallel checkouts at a SKU with stock of 1 and asserts exactly one success.
• A replay test that delivers the same payment webhook several times.
• A test for payment arriving after expiry.
• A dashboard for available, held and committed per hot SKU.
• An alert if the expiry worker falls behind.

Inventory is only one part of getting a store ready for a sale. Traffic, speed, payments and support all need attention too. The team at Disruptive Studio covers the broader checklist. If your stock counts hold up under concurrent checkouts, you avoid the most expensive kind of Black Friday bug: one that costs you money and annoys the customer.