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 Python checks it, then an UPDATE takes the quantity off:

@app.post("/products/<int:product_id>/buy")
def buy_product(product_id):
    quantity = request.get_json()["quantity"]
    row = db.execute("SELECT stock FROM products WHERE id = ?", (product_id,)).fetchone()
    if row is None:
        return jsonify({"error": "Product not found"}), 404
    if row["stock"] < quantity:
        return jsonify({"error": "Not enough stock"}), 409
    db.execute("UPDATE products SET stock = stock - ? WHERE id = ?", (quantity, product_id))
    db.commit()
    return jsonify({"id": product_id, "bought": quantity})

With one customer at a time, it works. But a web server handles requests side by side. When two customers buy the last lamp at the same moment, 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 cursor.rowcount tells you how many rows changed. Here is the idea for a coupon that can be used a few times:

sql = "UPDATE coupons SET uses_left = uses_left - 1 WHERE code = ? AND uses_left > 0"
cursor = db.execute(sql, ("SPRING",))
db.commit()
print(cursor.rowcount)   # 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 buy_product 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 buy_product with one atomic UPDATE, and use cursor.rowcount to answer 409 or 404 when it changed nothing.

Hint
Add the stock check to the UPDATE's WHERE, and pass 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.