Snap-up and second-kill are common scenarios. Interviewers often ask questions during interviews, such as how to achieve the snap-up second-kill in Taobao.
Buying and killing in seconds is very simple, but there are some problems to be solved, mainly for two problems:
1. Pressure on databases caused by high concurrency
2. How to Solve the Correct Reduction of Inventory under the Competitive State ("Oversell")
The first problem is that PHP is very simple, using caching technology can alleviate database pressure, such as memcache, redis and other caching technologies.
The second question is more complex:
General Writing:
Query the inventory of the corresponding goods to see if it is greater than 0, and then perform operations such as generating orders, but if the inventory is greater than 0, there will be problems in high concurrency, resulting in negative inventory.
<?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;
//Generate unique order
function build_order_no(){
return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//Logging
function insertLog($event,$type=0){
global $conn;
$sql="insert into ih_log(event,type)
values('$event','$type')";
mysql_query($sql,$conn);
}
//Simulate Order Placement
//Is inventory greater than 0
$sql="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id'";
//Unlock When the data of goods_id='$goods_id'and sku_id='$sku_id' in ih_store data is locked (note 3), other transactions must wait for this transaction to commit before they can execute
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row['number']>0){//High concurrency leads to oversold
$order_sn=build_order_no();
//Generate Order
$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);
//Inventory reduction
$sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
$store_rs=mysql_query($sql,$conn);
if(mysql_affected_rows()){
insertLog('Successful inventory reduction');
}else{
insertLog('Inventory Reduction Failed');
}
}else{
insertLog('Insufficient inventory');
}What if this happens?Here are several optimization methods:
Optimize scenario 1: Set the inventory field number ed to unsigned, and when the inventory is 0, false will be returned because the field cannot be negative
//Inventory reduction
$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('Successful inventory reduction');6 }Copy Code
Optimize scenario 2: Use MySQL transactions to lock rows of operations
<?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;
//Generate unique order number
function build_order_no(){
return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//Logging
function insertLog($event,$type=0){
global $conn;
$sql="insert into ih_log(event,type)
values('$event','$type')";
mysql_query($sql,$conn);
}
//Simulate Order Placement
//Is inventory greater than 0
mysql_query("BEGIN"); //Start Transaction
$sql="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id' FOR UPDATE";//This record is locked and other transactions must wait for the transaction to commit before executing
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row['number']>0){
//Generate Order
$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);
//Inventory reduction
$sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
$store_rs=mysql_query($sql,$conn);
if(mysql_affected_rows()){
insertLog('Successful inventory reduction');
mysql_query("COMMIT");//Transaction Commit Unlock
}else{
insertLog('Inventory Reduction Failed');
}
}else{
insertLog('Insufficient inventory');
mysql_query("ROLLBACK");
}Optimize scenario 3: Use non-blocking file exclusive locks
<?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;
//Generate unique order number
function build_order_no(){
return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//Logging
function insertLog($event,$type=0){
global $conn;
$sql="insert into ih_log(event,type)
values('$event','$type')";
mysql_query($sql,$conn);
}
$fp = fopen("lock.txt", "w+");
if(!flock($fp,LOCK_EX | LOCK_NB)){
echo "The system is busy, please try again later";
return;
}
//Place an order
$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){//Is inventory greater than 0
//Simulate Order Placement
$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);
//Inventory reduction
$sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
$store_rs=mysql_query($sql,$conn);
if(mysql_affected_rows()){
insertLog('Successful inventory reduction');
flock($fp,LOCK_UN);//Release lock
}else{
insertLog('Inventory Reduction Failed');
}
}else{
insertLog('Insufficient inventory');
}
fclose($fp);Optimize scenario 4: Use the redis queue because the pop operation is atomic and executes sequentially even if many users arrive at the same time. It is recommended (mysql transactions degrade severely under high concurrency, and file locks work in the same way)
Queue commodity inventory first
<?php
$store=1000;
$redis=new Redis();
$result=$redis->connect('127.0.0.1',6379);
$res=$redis->llen('goods_store');
echo $res;
$count=$store-$res;
for($i=0;$i<$count;$i++){
$redis->lpush('goods_store',1);
}
echo $redis->llen('goods_store');Preemptive Purchase, Description 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;
//Generate unique order number
function build_order_no(){
return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
}
//Logging
function insertLog($event,$type=0){
global $conn;
$sql="insert into ih_log(event,type)
values('$event','$type')";
mysql_query($sql,$conn);
}
//Simulate Order Placement
//Determine redis queue inventory before placing an order
$redis=new Redis();
$result=$redis->connect('127.0.0.1',6379);
$count=$redis->lpop('goods_store');
if(!$count){
insertLog('error:no store redis');
return;
}
//Generate Order
$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);
//Inventory reduction
$sql="update ih_store set number=number-{$number} where sku_id='$sku_id'";
$store_rs=mysql_query($sql,$conn);
if(mysql_affected_rows()){
insertLog('Successful inventory reduction');
}else{
insertLog('Inventory Reduction Failed');
}The above is just a simple simulation of the high concurrency of the rush to buy, the real scene is much more complex than this, many attention points, such as the rush to buy pages become static, call the interface through ajax.
Another example would lead to one user grabbing more than one, thinking:
A queue and a queue of results and inventory are required.In case of high concurrency, the user is queued first, and a user is removed from the queue with a thread loop to determine if the user is already in the queue of results. If so, the user is already in the queue of results. Otherwise, the inventory is reduced by 1, and the user is written to the database to queue the results.
When I was working in the mall project between me, I killed this redis I used directly in seconds. During this period, I looked at the above several methods, although they are different, but the purpose is the same. You choose your own, just be happy.