The AI Oversells in Java

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

@PostMapping("/products/{productId}/buy")
Object buyProduct(@PathVariable int productId, @RequestBody Map<String, Object> data) {
    int quantity = (int) data.get("quantity");
    List<Map<String, Object>> rows = db.queryForList("SELECT stock FROM products WHERE id = ?", productId);
    if (rows.isEmpty()) {
        return ResponseEntity.status(404).body(Map.of("error", "Product not found"));
    }
    int stock = (int) rows.get(0).get("stock");
    if (stock < quantity) {
        return ResponseEntity.status(409).body(Map.of("error", "Not enough stock"));
    }
    db.update("UPDATE products SET stock = stock - ? WHERE id = ?", quantity, productId);
    return Map.of("id", productId, "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 the number db.update returns tells you how many rows changed. Here is the idea for a coupon that can be used a few times:

String sql = "UPDATE coupons SET uses_left = uses_left - 1 WHERE code = ? AND uses_left > 0";
int changed = db.update(sql, "SPRING");
System.out.println(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 buyProduct 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 buyProduct with one atomic UPDATE, and use the number db.update returns 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: quantity, productId, quantity. Look the product up only after the UPDATE changed nothing: that lookup explains the failure, it never decides who gets the stock.