The AI Oversells

An AI assistant wrote the shop's buy route: a customer sends a quantity, and the product's stock goes down. The AI did it in two steps. A SELECT reads the stock and JavaScript checks it, then an UPDATE takes the quantity off:

app.post("/products/:productId/buy", (req, res) => {
  const productId = Number(req.params.productId);
  const quantity = req.body.quantity;
  const row = db.prepare("SELECT stock FROM products WHERE id = ?").get(productId);
  if (row === undefined) {
    return res.status(404).json({ error: "Product not found" });
  }
  if (row.stock < quantity) {
    return res.status(409).json({ error: "Not enough stock" });
  }
  db.prepare("UPDATE products SET stock = stock - ? WHERE id = ?").run(quantity, productId);
  res.json({ id: productId, bought: quantity });
});

With one customer at a time, it works. But a busy shop runs several copies of the app side by side, all on the same database. When two customers buy the last lamp at the same moment, one in each copy, both SELECTs can read 1 before either UPDATE runs. Both checks pass, both customers get the lamp, and the stock ends at -1.

The fix: don't read first. Put the check inside the write, in the UPDATE's WHERE. The database runs one write at a time, so the row changes only if the condition is still true at that very moment: one atomic step. Then result.changes tells you how many rows changed. Here is the idea for a coupon that can be used a few times:

const sql = "UPDATE coupons SET uses_left = uses_left - 1 WHERE code = ? AND uses_left > 0";
const result = db.prepare(sql).run("SPRING");
console.log(result.changes);   // 1: the coupon was used, 0: none left

Your rewrite must give these answers. The third request plays the race: another customer buys the last lamp just before your UPDATE runs.

POST /products/1/buy   {"quantity": 3}  →  200  {"id": 1, "bought": 3}
GET  /products/1                        →  200  {"id": 1, "name": "Mug", …, "stock": 7}
POST /products/3/buy   {"quantity": 1}  →  409  {"error": "Not enough stock"}
GET  /products/3                        →  200  {"id": 3, "name": "Lamp", …, "stock": 0}
POST /products/5/buy   {"quantity": 7}  →  409  {"error": "Not enough stock"}
POST /products/5/buy   {"quantity": 6}  →  200  {"id": 5, "bought": 6}
POST /products/99/buy  {"quantity": 1}  →  404  {"error": "Product not found"}

Your task: rewrite the buy route's function so the stock check and the change are one UPDATE. Answer 409 when it changed no row, and 404 when the product doesn't exist.

Assignment
Follow the Coding Tutorial to rewrite the buy route with one atomic UPDATE, and use result.changes to answer 409 or 404 when it changed nothing.

Hint
Add the stock check to the UPDATE's WHERE, and pass run one value per ?, in order. Look the product up only after the UPDATE changed nothing: that lookup explains the failure, it never decides who gets the stock.