Detailed explanation of examples of rush buying and flash sale functions under high concurrency using PHP and redis

Rush sales and flash sales are very common scenes. Interviewers often ask questions during interviews, such as how you implement rush sales on Taobao and so on.

The implementation of snap-up and flash sales is very simple, but some problems need to be solved, mainly focusing on two problems:

1 The pressure caused by high concurrency on the database

2 How to solve the correct inventory reduction ("oversold" problem) under competition conditions

The first question, for PHP It's very simple. You can use caching technology to relieve database pressure, such as memcache, redis and other caching technologies.

The second question is more complicated:

Conventional writing:

Query the corresponding product Inventory, see if it is greater than 0, and then perform operations such as generating orders. However, when judging whether the inventory is greater than 0, there will be problems under high concurrency, resulting in a negative inventory number

<?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(&#39;ymd&#39;).substr(implode(NULL, array_map(&#39;ord&#39;, str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//记录日志
function insertLog($event,$type=0){
    global $conn;
    $sql="insert into ih_log(event,type) 
    values(&#39;$event&#39;,&#39;$type&#39;)"; 
    mysql_query($sql,$conn); 
}
 
//模拟下单操作
//库存是否大于0
$sql="select number from ih_store where goods_id=&#39;$goods_id&#39; and sku_id=&#39;$sku_id&#39;";
//解锁 此时ih_store数据中goods_id=&#39;$goods_id&#39; and sku_id=&#39;$sku_id&#39; 的数据被锁住(注3)，其它事务必须等待此次事务 提交后才能执行
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row[&#39;number&#39;]>0){//高并发下会导致超卖
    $order_sn=build_order_no();
    //生成订单 
    $sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) 
    values(&#39;$order_sn&#39;,&#39;$user_id&#39;,&#39;$goods_id&#39;,&#39;$sku_id&#39;,&#39;$price&#39;)"; 
    $order_rs=mysql_query($sql,$conn); 
     
    //库存减少
    $sql="update ih_store set number=number-{$number} where sku_id=&#39;$sku_id&#39;";
    $store_rs=mysql_query($sql,$conn); 
    if(mysql_affected_rows()){ 
        insertLog(&#39;库存减少成功&#39;);
    }else{ 
        insertLog(&#39;库存减少失败&#39;);
    } 
}else{
    insertLog(&#39;库存不够&#39;);
}

This occurs What to do in this situation? Let’s look at several optimization methods:

Optimization plan 1: Set the inventory field number field to unsigned. When the inventory is 0, because the field cannot be a negative number, Will return false

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

Optimization plan 2: Use MySQL transaction to lock the row of operation

<?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(&#39;ymd&#39;).substr(implode(NULL, array_map(&#39;ord&#39;, str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//记录日志
function insertLog($event,$type=0){
    global $conn;
    $sql="insert into ih_log(event,type) 
    values(&#39;$event&#39;,&#39;$type&#39;)"; 
    mysql_query($sql,$conn); 
}
 
//模拟下单操作
//库存是否大于0
mysql_query("BEGIN");   //开始事务
$sql="select number from ih_store where goods_id=&#39;$goods_id&#39; and sku_id=&#39;$sku_id&#39; FOR UPDATE";//此时这条记录被锁住,其它事务必须等待此次事务提交后才能执行
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row[&#39;number&#39;]>0){
    //生成订单 
    $order_sn=build_order_no(); 
    $sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) 
    values(&#39;$order_sn&#39;,&#39;$user_id&#39;,&#39;$goods_id&#39;,&#39;$sku_id&#39;,&#39;$price&#39;)"; 
    $order_rs=mysql_query($sql,$conn); 
     
    //库存减少
    $sql="update ih_store set number=number-{$number} where sku_id=&#39;$sku_id&#39;";
    $store_rs=mysql_query($sql,$conn); 
    if(mysql_affected_rows()){ 
        insertLog(&#39;库存减少成功&#39;);
        mysql_query("COMMIT");//事务提交即解锁
    }else{ 
        insertLog(&#39;库存减少失败&#39;);
    }
}else{
    insertLog(&#39;库存不够&#39;);
    mysql_query("ROLLBACK");
}

Optimization plan 3: Use non-blocking file exclusive lock

 <?php
$conn=mysql_connect("localhost","root","123456"); 
if(!$conn){ 
    echo "connect failed"; 
    exit; 
} 
mysql_select_db("big-bak",$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(&#39;ymd&#39;).substr(implode(NULL, array_map(&#39;ord&#39;, str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//记录日志
function insertLog($event,$type=0){
    global $conn;
    $sql="insert into ih_log(event,type) 
    values(&#39;$event&#39;,&#39;$type&#39;)"; 
    mysql_query($sql,$conn); 
}
 
$fp = fopen("lock.txt", "w+");
if(!flock($fp,LOCK_EX | LOCK_NB)){
    echo "系统繁忙，请稍后再试";
    return;
}
//下单
$sql="select number from ih_store where goods_id=&#39;$goods_id&#39; and sku_id=&#39;$sku_id&#39;";
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row[&#39;number&#39;]>0){//库存是否大于0
    //模拟下单操作 
    $order_sn=build_order_no(); 
    $sql="insert into ih_order(order_sn,user_id,goods_id,sku_id,price) 
    values(&#39;$order_sn&#39;,&#39;$user_id&#39;,&#39;$goods_id&#39;,&#39;$sku_id&#39;,&#39;$price&#39;)"; 
    $order_rs=mysql_query($sql,$conn); 
     
    //库存减少
    $sql="update ih_store set number=number-{$number} where sku_id=&#39;$sku_id&#39;";
    $store_rs=mysql_query($sql,$conn); 
    if(mysql_affected_rows()){ 
        insertLog(&#39;库存减少成功&#39;);
        flock($fp,LOCK_UN);//释放锁
    }else{ 
        insertLog(&#39;库存减少失败&#39;);
    } 
}else{
    insertLog(&#39;库存不够&#39;);
}
fclose($fp);

Optimization plan 4:Use redis queue, because the pop operation is atomic, even if many users arrive at the same time, they will be executed sequentially. It is recommended to use (mysql transaction in high concurrency The performance drops drastically, as does the file lock method)

First put the product inventory into a queue

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

Purchase and describe the logic

 <?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(&#39;ymd&#39;).substr(implode(NULL, array_map(&#39;ord&#39;, str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//记录日志
function insertLog($event,$type=0){
    global $conn;
    $sql="insert into ih_log(event,type) 
    values(&#39;$event&#39;,&#39;$type&#39;)"; 
    mysql_query($sql,$conn); 
}
 
//模拟下单操作
//下单前判断redis队列库存量
$redis=new Redis();
$result=$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('库存减少失败');
}

The above is just a simple simulation of rush buying under high concurrency. The real scenario is much more complicated than this. There are many things to pay attention to

For example, the rush buying page is made static and the interface is called through ajax

The above will cause one user to grab multiple items. The idea is:

Needs a queuing queue, a snap-up result queue and an inventory queue. In the case of high concurrency, first enter the user into the queuing queue, use a thread loop to remove a user from the queuing queue, and determine whether the user is already in the rush-buying result queue. If it is, it has been snapped up, otherwise it is not snapped up, the inventory is reduced by 1, write Database, put the user into the result queue.

When I was working on a shopping mall project, I used redis directly for flash sales. During this time, I looked at the above methods. Although they are different, they all achieve the same purpose. Everyone Make your own choice and be happy.

The above is the detailed content of Detailed explanation of examples of rush buying and flash sale functions under high concurrency using PHP and redis. For more information, please follow other related articles on the PHP Chinese website!