E-Commerce Inventory (Overselling)

How a naive database update can sell the same concert ticket to two different people.

The idea

Imagine you have exactly 1 limited-edition sneaker left in stock. Two customers, Alice and Bob, click "Buy" at the exact same millisecond. If your backend reads the inventory (stock = 1), checks if it's > 0 (it is), and then updates it (stock = stock - 1), both Alice's thread and Bob's thread will read 1 before either can write 0. Both purchases succeed. You just sold 2 sneakers, but you only have 1 in the warehouse. This is a classic Race Condition causing Overselling.

Step 1: Normal Purchase. Alice buys the sneaker. Stock goes from 1 to 0. All is well.

How it works (Atomic Updates)

You can never "Read, Check in Code, then Write". The gap between the read and the write is where the race condition lives. You must push the logic down into the database itself, making the check and the update a single, indivisible (Atomic) operation.

// BAD: Read-Modify-Write (Race Condition!)
const item = await db.query('SELECT stock FROM items WHERE id=1');
if (item.stock >= 1) { // Both Alice and Bob pass this check at the same time
    await db.query('UPDATE items SET stock = stock - 1 WHERE id=1');
    return "Purchase Successful!";
}

// GOOD: Atomic Update with Database Constraints
// The WHERE clause ensures the DB only updates if stock > 0.
const result = await db.query(`
    UPDATE items 
    SET stock = stock - 1 
    WHERE id=1 AND stock >= 1
`);

// If result.rowCount is 0, someone else bought it first!
if (result.rowCount === 1) return "Purchase Successful!";
else return "Out of stock!";

Cost

Using atomic SQL updates solves overselling, but creates a bottleneck on highly anticipated product drops (like concert tickets). If 10,000 people try to run an UPDATE on the exact same row (the concert ticket table), the relational database has to lock that row, forcing all 10,000 requests into a slow, single-file line (Row Contention). The database might crash.

Watch out for