The AI Oversells in C#

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

app.MapPost("/products/{id}/buy", (int id, JsonElement data) => {
    var quantity = data.GetProperty("quantity").GetInt32();
    var cmd = db.CreateCommand();
    cmd.CommandText = "SELECT stock FROM products WHERE id = $id";
    cmd.Parameters.AddWithValue("$id", id);
    var stock = cmd.ExecuteScalar();
    if (stock == null) {
        return Results.Json(new { error = "Product not found" }, statusCode: 404);
    }
    if ((long)stock < quantity) {
        return Results.Json(new { error = "Not enough stock" }, statusCode: 409);
    }
    cmd.CommandText = "UPDATE products SET stock = stock - $quantity WHERE id = $id";
    cmd.Parameters.AddWithValue("$quantity", quantity);
    cmd.ExecuteNonQuery();
    return Results.Json(new { id = 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 ExecuteNonQuery() tells you how many rows changed. Here is the idea for a coupon that can be used a few times:

var cmd = db.CreateCommand();
cmd.CommandText = "UPDATE coupons SET uses_left = uses_left - 1 WHERE code = $code AND uses_left > 0";
cmd.Parameters.AddWithValue("$code", "SPRING");
var changed = cmd.ExecuteNonQuery();
Console.WriteLine(changed);   // 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 MapPost function for /products/{id}/buy 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's function with one atomic UPDATE, and use the number ExecuteNonQuery() returns to answer 409 or 404 when it changed nothing.

Hint
Add the stock check to the UPDATE's WHERE; $quantity appears twice but gets one value. Look the product up only after the UPDATE changed nothing: that lookup explains the failure, it never decides who gets the stock.