STAVENZA
← Selected work

Engineering Note / 2026

Selling the last one twice

When two people buy the last item at the same moment, or a marketplace sends the same order twice, a store can sell stock it does not have. Why it happens, the fixes that look right but are not, and what we would ship instead.

Engineering note. A technical write-up, not client work. There is no client and no results are claimed; it shows how we would solve the problem.

Type
Engineering note, no client
Industry
E-commerce engineering
Services
Custom web applications, Cloud, DevOps & care

Why it matters

Overselling looks like a small bug and costs like a big one: a cancelled order, a refund, an apology and, on marketplaces, a seller rating that drops. It rarely shows up in testing, because testing is one person clicking at a time. It shows up on launch day, during a sale, or when a marketplace retries a webhook. The first half of this note explains the decision in plain words. The code and the options we weighed follow, for the engineers.

What we set out to answer

  1. Why a checkout that works for one shopper can fail for two at once
  2. Which common fixes really stop it, and which only make it rarer
  3. What a repeated order event from a marketplace does to the count
  4. What to do when marketplaces see the stock count late

The problem, step by step

How a store sells the last unit twice, the fix we would ship, and how a repeated marketplace event is made harmless.

How it goes wrong
How it goes wrongCheckout ADatabaseCheckout BHow many are left?1How many are left?1Save 0Save 0Two orders for one unit. The count says 0, so nothing looks wrong.
Read the diagram as text
  1. Checkout A → Database: How many are left?
  2. Database → Checkout A: 1
  3. Checkout B → Database: How many are left?
  4. Database → Checkout B: 1
  5. Checkout A → Database: Save 0
  6. Checkout B → Database: Save 0
  7. Two orders for one unit. The count says 0, so nothing looks wrong.

Both checkouts read the same number before either writes. Each one is correct on its own; together they sell one unit twice, and the stock column still looks fine.

The fix we would ship
The fix we would shipCheckout ADatabaseCheckout BTake one, if one is leftTake one, if one is leftApplied one at a timeDone: 1 row changedSold out: 0 rows changedB hears “sold out” before paying, not after.
Read the diagram as text
  1. Checkout A → Database: Take one, if one is left
  2. Checkout B → Database: Take one, if one is left
  3. Applied one at a time
  4. Database → Checkout A: Done: 1 row changed
  5. Database → Checkout B: Sold out: 0 rows changed
  6. B hears “sold out” before paying, not after.

The check and the change become one statement. The database applies them one at a time, so only one checkout can take the last unit, and the other learns before payment, not after.

When a marketplace says it twice
When a marketplace says it twiceMarketplaceOrder hubDatabaseOrder 17Record order 17, take oneOrder 17 again (retry)Record order 17Already recorded: no changeOK, to both deliveriesOne order, one unit, however often it arrives.
Read the diagram as text
  1. Marketplace → Order hub: Order 17
  2. Order hub → Database: Record order 17, take one
  3. Marketplace → Order hub: Order 17 again (retry)
  4. Order hub → Database: Record order 17
  5. Database → Order hub: Already recorded: no change
  6. Order hub → Marketplace: OK, to both deliveries
  7. One order, one unit, however often it arrives.

Marketplaces deliver order events at least once, so a retry can arrive while the first delivery is still being handled. Recording the event id under a unique key, in the same transaction as the stock change, turns the repeat into a harmless no-op.

The code

Looks right, oversells under load
SELECT qty FROM stock WHERE sku = ?;
-- payment, network, other work
UPDATE stock SET qty = :qty_read_minus_one WHERE sku = ?;
Take one only if one is left
UPDATE stock SET qty = qty - 1
WHERE sku = ? AND qty >= 1;
-- 1 row changed: sold. 0 rows: sold out.
Handle each order event once
BEGIN;
INSERT INTO orders (event_id) VALUES (?)
  ON CONFLICT (event_id) DO NOTHING;
-- only if that inserted a row:
UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty >= 1;
COMMIT;

Options we weighed

Choosing what not to build is most of the work.

  • Rejected

    Read the count, then save the new number

    Two checkouts can read the same number and both save it, so one sale is lost. The count never goes below zero, which hides the damage.

  • Rejected

    Check first, then subtract one

    Makes overselling rarer, not impossible: every checkout that passes the check before the others write can still take the last unit.

  • Chosen

    Subtract one only if one is left

    The check and the change are one statement, so the database applies them one at a time. No lock is held while slow work such as a payment call runs.

  • Situational

    Lock the row while the order is processed

    Correct, but it holds a lock across slow work. We would reach for it when several rows must change together, such as a bundle.

  • Rejected

    A separate lock service

    Adds a second system that must stay in agreement with the database, to do a job the database already does.

  • Chosen

    Unique key on every incoming event

    A repeat fails the unique key inside the same transaction, so it cannot take stock again. Checking for the order first misses repeats that arrive while the first is still being handled.

  • Chosen

    Push every stock change to marketplaces as it happens

    The shorter the delay before marketplaces see a change, the smaller the window in which they can sell what is already gone.

  • Situational

    Hold units back from marketplaces

    A unit or two of margin helps on items where a marketplace is known to update slowly. It does not replace fast updates, and every unit held back is one a marketplace cannot sell.

What we would ship

  1. Take stock with a single conditional update, and treat “0 rows changed” as sold out before any payment is taken
  2. Give every incoming order event a unique key and record it in the same transaction as the stock change
  3. Push stock changes to marketplaces the moment they happen, rather than on a timer
  4. Hold back a unit or two only on items where a marketplace is known to update slowly
  5. Test checkout with many simultaneous buyers, because one person clicking will never find this

Your next chapter.

Start a project