Preventing Overselling in High‑Concurrency Flash Sale Systems with PHP, MySQL, and Redis

In e‑commerce flash‑sale (抢购/秒杀) scenarios a large number of users place orders within a short time, creating two main challenges: high concurrency pressure on the database and the risk of stock overselling.

High‑Concurrency Pressure on the Database

To alleviate the load, cache the product data and avoid direct database queries, for example by using Redis as a fast in‑memory store.

Preventing Stock Oversell

The naive approach of checking store > 0 before creating an order can lead to negative stock under concurrency. Several robust solutions are presented.

1. Unsigned Stock Field and Row Lock

Define the stock column as UNSIGNED to prevent negative values and use SELECT ... FOR UPDATE to lock the row during the transaction.

<code>&lt;?php
$db();
global $con;
$product_id = 1; // 商品ID
$buy_num = 1;   // 购买数量
$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 "更新成功";
        $oid = build_order_no();
        create_order($oid, $product_id, $buy_num);
        insertLog('库存减少成功，下单成功');
    } else {
        echo "更新失败";
        insertLog('库存减少失败');
    }
} else {
    echo "没有库存";
    insertLog('库存不够');
}
?>
</code>

2. MySQL Transaction with Explicit BEGIN/COMMIT/ROLLBACK

Wrap the whole order process in a transaction so that the row lock is released only after commit or rollback.

<code>&lt;?php
$db();
global $con;
mysqli_query($con, "BEGIN"); // 开始事务
// ... 同上查询、判断、更新逻辑 ...
if ($update_success) {
    mysqli_query($con, "COMMIT"); // 提交事务
} else {
    mysqli_query($con, "ROLLBACK"); // 回滚事务
}
?>
</code>

3. File Lock (flock) – Blocking and Non‑Blocking Modes

Use a file as a mutex. In blocking mode the second request waits; in non‑blocking mode it returns a busy response.

<code>&lt;?php
$db();
$fp = fopen('lock.txt', 'w');
if (flock($fp, LOCK_EX)) { // 阻塞锁
    // 业务逻辑同上
    flock($fp, LOCK_UN);
} else {
    echo "系统繁忙，请稍后再试";
    insertLog('系统繁忙，请稍后再试');
}
fclose($fp);
?>
</code>

4. Redis Queue for Stock Pre‑loading

Push a token for each item into a Redis list; LPOP atomically consumes a token, guaranteeing that no more orders are processed than available stock.

<code>&lt;?php
$db();
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$key = 'goods_store_1';
$count = $redis->lpop($key);
if (!$count) {
    insertLog('error:no store redis');
    return '秒杀结束，没有商品库存了';
}
// 继续更新 MySQL 库存并生成订单
?>
</code>

5. Redis Optimistic Lock (WATCH/MULTI/EXEC)

Watch a key representing sold quantity, increment it inside a transaction, and only succeed if the stock limit has not been reached.

<code>&lt;?php
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->watch('sales');
$sales = $redis->get('sales');
$stock = 20;
if ($sales >= $stock) {
    exit('秒杀结束');
}
$redis->multi();
$redis->incr('sales');
$res = $redis->exec(); // 成功返回 1
if ($res) {
    // 更新 MySQL 库存并创建订单
    // ... 同上逻辑 ...
    echo '秒杀完成';
} else {
    exit('抢购失败');
}
?>
</code>

All helper functions ( db() , build_order_no() , create_order() , insertLog() ) are defined once and reused across the examples.

By combining caching, row locking, transactions, file locking, and Redis‑based queues or optimistic locks, developers can reliably handle flash‑sale traffic without causing stock inconsistencies or excessive database load.