Look, I've seen this blow up in production more times than I care to admit. You're building an e-commerce site, you've got a "limited edition" product—say, a high-end mechanical keyboard with only 10 units in stock—and you write a piece of code that feels perfectly logical. You check if the stock is greater than zero, and if it is, you decrement it and create the order. It works every time you test it manually. But the moment you launch and a hundred people hit that "Buy" button at the exact same millisecond, you suddenly find yourself with -14 keyboards in stock and a lot of very angry customers.

The "Check-then-Act" Fallacy

The most common mistake I see is assuming that because PHP code executes sequentially, the database state remains frozen while that code is running. This is the "check-then-act" pattern, and in a multi-user environment, it's a recipe for disaster. Here is what the "wrong" code usually looks like:

// WRONG: This is vulnerable to race conditions
$stock = $db->query("SELECT stock FROM products WHERE id = 101")->fetchColumn();

if ($stock > 0) {
    // There is a tiny gap of time here. 
    // Another request can sneak in right NOW.
    $db->query("UPDATE products SET stock = stock - 1 WHERE id = 101");
    createOrder($userId, 101);
}

Here is why this fails: Imagine Alice and Bob both click "Buy" at the same time. Alice's request hits the server and reads the stock as 1. While Alice's PHP script is moving from the if statement to the UPDATE statement, Bob's request hits the server. Bob also reads the stock as 1 because Alice hasn't updated it yet. Both scripts pass the if ($stock > 0) check. Both scripts run the update. You've just sold two keyboards when you only had one.

Using SELECT FOR UPDATE to Claim Your Row

To fix this, we need to tell the database: "I am reading this row, and I intend to change it. Nobody else touch it until I'm done." This is called pessimistic locking. In MySQL, we do this using the FOR UPDATE clause inside a transaction.

When you use FOR UPDATE, any other request that tries to read that same row using FOR UPDATE (or tries to UPDATE it) will literally pause and wait until your transaction is committed or rolled back. It turns a chaotic free-for-all into an orderly queue.

// RIGHT: Using pessimistic locking
$db->beginTransaction();

try {
    // The 'FOR UPDATE' locks this row immediately
    $stmt = $db->prepare("SELECT stock FROM products WHERE id = ? FOR UPDATE");
    $stmt->execute([101]);
    $stock = $stmt->fetchColumn();

    if ($stock > 0) {
        $db->prepare("UPDATE products SET stock = stock - 1 WHERE id = ?")->execute([101]);
        createOrder($userId, 101);
        $db->commit();
        echo "Order successful!";
    } else {
        $db->rollBack();
        echo "Sorry, we just sold out!";
    }
} catch (Exception $e) {
    $db->rollBack();
    throw $e;
}

The Atomic Update Shortcut

Now, if you don't need to do complex logic in PHP between the check and the update, there is a much cleaner way. You can move the condition directly into the UPDATE statement. This is an "atomic" operation—the database handles the check and the decrement in a single, indivisible step.

I prefer this method whenever possible because it's faster and doesn't require keeping a transaction open while your PHP script thinks. It looks like this:

// BEST: Atomic update
$stmt = $db->prepare("UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0");
$stmt->execute([101]);

if ($stmt->rowCount() > 0) {
    // The update only happened if stock was > 0
    createOrder($userId, 101);
    echo "Order successful!";
} else {
    echo "Sorry, we just sold out!";
}

In this version, the database engine ensures that the stock > 0 condition is true at the exact moment the decrement happens. If the stock is 0, the WHERE clause fails, 0 rows are updated, and rowCount() tells you exactly that. No race conditions, no locks to manage, just clean, safe data.

📋 Practical Task

Fixing the "Flash Sale" Race Condition

You have been handed a legacy script for a "Flash Sale" feature. The current code is causing the company to oversell items during high-traffic events. The code currently uses the dangerous "check-then-act" pattern described in the lesson.

Your Goal: Rewrite the order processing logic to prevent overselling. You may choose either the SELECT FOR UPDATE approach or the Atomic Update approach, but you must ensure that it is impossible for the stock to drop below zero, regardless of how many concurrent requests hit the server.

Starting Code:

// current_sale.php
$productId = $_POST['product_id'];
$userId = $_SESSION['user_id'];

// DANGEROUS CODE
$res = $pdo->query("SELECT quantity FROM flash_sale_items WHERE id = $productId");
$item = $res->fetch();

if ($item['quantity'] > 0) {
    $pdo->query("UPDATE flash_sale_items SET quantity = quantity - 1 WHERE id = $productId");
    $pdo->query("INSERT INTO orders (user_id, product_id) VALUES ($userId, $productId)");
    echo "Success!";
} else {
    echo "Sold out!";
}

Requirements:

• Implement a solution that prevents race conditions.
• Ensure the database remains consistent.
• If using transactions, ensure you include a commit and rollBack.