Preventing Overselling in High-Concurrency PHP Environments: Practical Strategies and Code Examples

The article you’re referencing provides a detailed explanation of how to prevent overselling in high-concurrency scenarios, especially in e-commerce platforms dealing with flash sales or time-limited promotions. In such cases, many users may simultaneously attempt to purchase the same product, which leads to potential stock overselling if proper mechanisms are not in place.

Here’s a breakdown of the methods discussed in the article:

1. Database Pressure and Caching (Redis)

• Problem: Handling high concurrency can overload the database if every request directly interacts with it.
• Solution: Use caching systems like Redis to store product data temporarily and reduce direct database calls. This can significantly improve performance under high traffic loads.

2. Race Condition in Inventory Check

• Problem: In a high-concurrency environment, multiple requests can pass the inventory check (e.g., checking if inventory > 0) and lead to overselling.
• Solution: The article suggests several approaches to prevent this:

Regular Approach

• The standard approach involves checking the inventory, deducting the quantity from the stock, and updating the database.
• Issue: If two requests check the inventory at the same time, both may see the inventory as available and proceed with the purchase, leading to overselling.

Solution with FOR UPDATE (Row Locking)

• By adding FOR UPDATE to the SQL query (SELECT * FROM products WHERE id={$product_id} FOR UPDATE), you lock the row in the database during the transaction, preventing concurrent updates to the same product until the first transaction completes.
• This ensures that once one user buys a product, the stock is updated immediately, and other users will see the updated inventory status.

Using Transactions

• Wrap the inventory check and update in a transaction (BEGIN, COMMIT, ROLLBACK).
• Locking: This ensures that during the transaction, other operations that attempt to update the inventory will be blocked until the transaction is complete.

File-Based Locking (Blocking vs Non-Blocking)

• Blocking Lock: Use flock() to create a lock file (lock.txt) to manage concurrency. The first request locks the file and processes the purchase, while others have to wait until the lock is released.
• Non-Blocking Lock: Use flock() with LOCK_NB to attempt to acquire the lock without blocking. If the lock cannot be acquired (indicating another user is processing the purchase), the request will fail with a “Server Busy” message.

Code Examples

1. Basic Example Using Row Locking with FOR UPDATE:

$sql = "SELECT * FROM products WHERE id={$product_id} FOR UPDATE";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
if ($row['store'] > 0) {
    $sql = "UPDATE products SET store = store - {$buy_num} WHERE id={$product_id}";
    if (mysqli_query($con, $sql)) {
        echo "Updated successfully";
        // Create order and log
    } else {
        echo "Failed to update stock";
    }
} else {
    echo "Out of stock";
}

2. Example Using Transactions:

mysqli_query($con, "BEGIN"); // Start transaction
$sql = "SELECT * FROM products WHERE id={$product_id} FOR UPDATE";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
if ($row['store'] > 0) {
    $sql = "UPDATE products SET store = store - {$buy_num} WHERE id={$product_id}";
    if (mysqli_query($con, $sql)) {
        echo "Stock updated successfully";
        mysqli_query($con, "COMMIT"); // Commit transaction
    } else {
        mysqli_query($con, "ROLLBACK"); // Rollback transaction
        echo "Failed to update stock";
    }
} else {
    mysqli_query($con, "ROLLBACK");
    echo "Out of stock";
}

3. Example Using File Locking:

$fp = fopen('lock.txt', 'w');
if (flock($fp, LOCK_EX)) {   // Blocking lock
    $sql = "SELECT * FROM products WHERE id={$product_id}";
    $result = mysqli_query($con, $sql);
    $row = mysqli_fetch_assoc($result);
    if ($row['store'] > 0) {
        $sql = "UPDATE products SET store = store - {$buy_num} WHERE id={$product_id}";
        if (mysqli_query($con, $sql)) {
            echo "Stock updated successfully";
            // Create order and log
        } else {
            echo "Failed to update stock";
        }
    } else {
        echo "Out of stock";
    }
    flock($fp, LOCK_UN); // Release lock
}
fclose($fp);

Conclusion

By implementing row-level locking (FOR UPDATE), transactions, or file-based locks, you can ensure that stock quantities are accurately updated in a high-concurrency environment, preventing overselling. Additionally, using caching systems like Redis can help alleviate database pressure and improve performance during peak traffic times.

This approach provides a robust way to handle inventory management in e-commerce systems where users might compete for limited stock.