Handling High-Concurrency Flash Sale in PHP: Preventing Overselling with Database Locks, Transactions, File Locks, and Redis Queues

Flash‑sale (抢购) and秒杀 scenarios generate massive concurrent requests, creating two main issues: heavy database load and the risk of overselling when inventory is reduced incorrectly.

The first problem can be mitigated by caching (e.g., Redis) to avoid direct database hits, but the critical challenge is ensuring correct inventory deduction under high concurrency.

Conventional approach queries the stock, checks if it is greater than zero, then creates an order; however, race conditions can cause the stock to become negative.

<?php
$conn=mysql_connect("localhost","big","123456");
if(!$conn){
    echo "connect failed";
    exit;
}
mysql_select_db("big",$conn);
mysql_query("set names utf8");

$price=10;$user_id=1;$goods_id=1;$sku_id=11;$number=1;

function build_order_no(){
    return date('ymd').substr(implode(NULL,array_map('ord',str_split(substr(uniqid(),7,13),1))),0,8);
}
function insertLog($event,$type=0){
    global $conn;
    $sql="insert into ih_log(event,type) values('$event','$type')";
    mysql_query($sql,$conn);
}

$sql="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id'";
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row['number']>0){
    $order_sn=build_order_no();
    $sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) values('$order_sn','$user_id','$goods_id','$sku_id','$price')";
    $order_rs=mysql_query($sql,$conn);
    $sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
    $store_rs=mysql_query($sql,$conn);
    if(mysql_affected_rows()){
        insertLog('库存减少成功');
    }else{
        insertLog('库存减少失败');
    }
}else{
    insertLog('库存不够');
}
?>

Optimization 1 : Define the inventory column as UNSIGNED and only decrement when the current stock is greater than zero, preventing negative values.

//库存减少
$sql="update ih_store set number=number-{$number} where sku_id='$sku_id' and number>0";
$store_rs=mysql_query($sql,$conn);
if(mysql_affected_rows()){
    insertLog('库存减少成功');
}

Optimization 2 : Use MySQL transactions with FOR UPDATE to lock the row during the check‑and‑update sequence.

<?php
$conn=mysql_connect("localhost","big","123456");
// ... (connection code omitted for brevity)
mysql_query("BEGIN");
$sql="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id' FOR UPDATE";
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row['number']>0){
    $order_sn=build_order_no();
    $sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) values('$order_sn','$user_id','$goods_id','$sku_id','$price')";
    $order_rs=mysql_query($sql,$conn);
    $sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
    $store_rs=mysql_query($sql,$conn);
    if(mysql_affected_rows()){
        insertLog('库存减少成功');
        mysql_query("COMMIT");
    }else{
        insertLog('库存减少失败');
    }
}else{
    insertLog('库存不够');
    mysql_query("ROLLBACK");
}
?>

Optimization 3 : Apply a non‑blocking file exclusive lock ( flock) to serialize order creation.

<?php
$fp = fopen("lock.txt", "w+");
if(!flock($fp, LOCK_EX | LOCK_NB)){
    echo "系统繁忙，请稍后再试";
    return;
}
// order logic here
flock($fp, LOCK_UN);
fclose($fp);
?>

Optimization 4 : Use a Redis list as a queue; each lpop operation is atomic, guaranteeing that only one request can obtain a stock unit.

<?php
$store=1000;
$redis=new Redis();
$redis->connect('127.0.0.1',6379);
$res=$redis->llen('goods_store');
$count=$store-$res;
for($i=0;$i<$count;$i++){
    $redis->lpush('goods_store',1);
}
?>

The actual flash‑sale flow then checks the Redis queue before creating the order, ensuring that no more orders are generated than available stock.

<?php
$redis=new Redis();
$redis->connect('127.0.0.1',6379);
$count=$redis->lpop('goods_store');
if(!$count){
    insertLog('error:no store redis');
    return;
}
$order_sn=build_order_no();
$sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) values('$order_sn','$user_id','$goods_id','$sku_id','$price')";
$order_rs=mysql_query($sql,$conn);
$sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
$store_rs=mysql_query($sql,$conn);
if(mysql_affected_rows()){
    insertLog('库存减少成功');
}else{
    insertLog('库存减少失败');
}
?>

For testing, tools like webbench or ab can simulate 5,000 concurrent requests, and the article also provides the necessary MySQL table definitions for ih_goods, ih_log, ih_order, and ih_store.

-- Database: `big`
CREATE TABLE IF NOT EXISTS `ih_goods` (
  `goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` int(11) NOT NULL,
  `goods_name` varchar(255) NOT NULL,
  PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;

CREATE TABLE IF NOT EXISTS `ih_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event` varchar(255) NOT NULL,
  `type` tinyint(4) NOT NULL DEFAULT '0',
  `addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `ih_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_sn` char(32) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `goods_id` int(11) NOT NULL DEFAULT '0',
  `sku_id` int(11) NOT NULL DEFAULT '0',
  `price` float NOT NULL,
  `addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表' AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `ih_store` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) NOT NULL,
  `sku_id` int(10) unsigned NOT NULL DEFAULT '0',
  `number` int(10) NOT NULL DEFAULT '0',
  `freez` int(11) NOT NULL DEFAULT '0' COMMENT '虚拟库存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='库存' AUTO_INCREMENT=2;

INSERT INTO `ih_store` (`id`,`goods_id`,`sku_id`,`number`,`freez`) VALUES (1,1,11,500,0);

While the article provides a simple simulation, real‑world flash‑sale systems require additional considerations such as static front‑end pages, AJAX APIs, queuing mechanisms, and result tracking to avoid duplicate purchases.