Two Buyers, One Lamp in C#

The shop has one lamp left, and two customers press Buy at the same moment. A route that first reads the stock and then writes stock - 1 can be fooled: a web server handles requests side by side, so both requests can read 1 before either one writes. Both customers get the lamp, and the stock ends at -1. This is a race condition: the result depends on which request gets there first.

One statement: let the database check and change the value in a single UPDATE, with the check in its WHERE. The database runs one write at a time, so nobody can slip in between the check and the change. ExecuteNonQuery() runs the UPDATE and gives back the number of rows it changed: 1 if it worked, 0 if not. Here is the idea for a coupon with a limited number of uses:

var cmd = RunQuery("UPDATE coupons SET uses_left = uses_left - 1 WHERE code = $code AND uses_left > 0");
cmd.Parameters.AddWithValue("$code", code);
if (cmd.ExecuteNonQuery() == 0) {
    return Results.Json(new { error = "Coupon used up" }, statusCode: 409);
}

409 Conflict says the request is fine, but the current state says no. An UPDATE also changes no row when the id doesn't exist, so when nothing changed, look the product up to choose between 409 and 404.

POST /products/1/buy   →  200  {"bought": 1, "id": 1}
GET /products/1        →  200  {"category": "kitchen", "id": 1, "name": "Mug", "price": 12, "stock": 9}
POST /products/3/buy   →  409  {"error": "Out of stock"}   (another customer bought the last lamp first)
GET /products/3        →  200  {"category": "home", "id": 3, "name": "Lamp", "price": 29, "stock": 0}
POST /products/2/buy   →  409  {"error": "Out of stock"}   (the notebook's stock is 0)
POST /products/99/buy  →  404  {"error": "Product not found"}
POST /products/8/buy   →  200  {"bought": 1, "id": 8}

Your task: add app.MapPost("/products/{id}/buy", …). Take one item off the stock with a single UPDATE … WHERE id = $id AND stock > 0 made with RunQuery, and answer new { id, bought = 1 }. If no row changed, answer 404 Product not found when there is no such product, and 409 Out of stock otherwise.

Assignment
Follow the Coding Tutorial to build a buy route that never sells more than the shop has.

Hint
Don't read the stock first. Keep the command from RunQuery in cmd and check cmd.ExecuteNonQuery() == 0: that call is what runs the UPDATE. Only then run SELECT id FROM products WHERE id = $id with ExecuteScalar(), which gives null when no row comes back, to tell a missing product from a sold-out one.