Day 3: Orders without overselling

Python Backend Basics: Build the ShopStream API ~4 min read 30 min hands-on

Send 50 buyers at 10 items: read-then-write oversells 40, one atomic UPDATE in a transaction sells exactly 10.

A limited sneaker drop: 10 pairs, and 50 people press "Buy" in the same second. If your code reads the stock, checks it in Python and then writes it back, you will sell far more than 10 pairs. Today you watch that happen and fix it with one SQL statement.

What you build

  • An orders table (migration 003) and POST /orders {"sku", "qty", "customer"}.

  • GET /products/{id}/sales: stock left, units sold and number of orders.

  • Two ways to take stock, switchable for the demo with ?mode=:

    python

The stock change and the INSERT INTO orders run in one transaction: they commit together, or a 409 rolls both back.

Why the naive version oversells

Between the SELECT and the UPDATE there is a gap. Every request that reads during that gap sees the same stock — 10 — and every one of them decides there's enough. Then they all write "stock = 9". The sleep makes the gap visible, but it exists without it: any real work between read and write (validating a coupon, calling payments) widens it.

The atomic UPDATE ... WHERE stock >= qty closes the gap. Postgres locks the row while it updates it; the next request waits, then re-checks stock >= qty against the new value. When stock hits 0, the WHERE matches nothing and the order is rejected.

What we measured

50 buyers, released at the same moment by a barrier, product with 10 in stock:

Read the full lesson free

This lesson is one of the first 3 in its course, free with an account — the full article, implementation guide and scripts. No card needed.

Already a member? Sign in