PHP combined with Redis to achieve high concurrent snapping, second kill function of the example _redis

Snapped up, second kill is now a very common application scenario, the main problem to be solved are two:

1 high concurrent pressure on the database

2 How to solve the correct inventory reduction ("oversold" problem) in the competitive state

For the first question, it is already easy to think of using caching to handle snapping, avoiding direct manipulation of databases, such as using Redis.

The focus is on the second question.

General wording:

Check out the inventory of the corresponding merchandise, see if it is greater than 0, and then perform orders and other operations, but in determining whether the inventory is greater than 0, if there are problems under high concurrency, resulting in a 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
(), 7, 13), 1)), 0, 8);
	}//Log function Insertlog ($event, $type =0) {global $conn; 
	$sql = "INSERT into Ih_log (Event,type) VALUES (' $event ', ' $type ')"; 
mysql_query ($sql, $conn); //Simulate whether the order operation//inventory is greater than 0 $sql = "Select number from Ih_store where goods_id= ' $goods _id ' and sku_id= ' $sku _id '";//unlock this time Ih_store data
Goods_id= ' $goods _id ' and sku_id= ' $sku _id ' data are locked (note 3), and other transactions must wait for the transaction to be submitted before they can be executed $rs =mysql_query ($sql, $conn);
$row =mysql_fetch_assoc ($RS);
	if ($row [' number ']>0] {//high concurrency will result in 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 (' Inventory reduction Success ');
	}else{Insertlog (' Inventory reduction failure '); }}else{Insertlog (' insufficient stock ');}?>

optimization Scenario 1: Set the Inventory field Number field to unsigned, and return False if the inventory is 0 o'clock 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 (' Inventory reduction succeeded ');
}

Optimization Scenario 2: use a MySQL transaction to lock the operation line

<?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 (
Uniqid (), 7, 13), 1)), 0, 8);
	}//Log function Insertlog ($event, $type =0) {global $conn; 
	$sql = "INSERT into Ih_log (Event,type) VALUES (' $event ', ' $type ')"; 
mysql_query ($sql, $conn);	//Simulate whether the order operation//inventory is 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,
Other transactions must wait for this transaction to be submitted before they can be executed $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 (' Inventory reduction Success ');
	mysql_query ("commit");//transaction commit is to unlock}else{insertlog (' Inventory reduction failure ');
	}}else{Insertlog (' insufficient stock ');
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 (
Uniqid (), 7, 13), 1)), 0, 8);
	}//Log 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 "system busy, please try again later";
Return
//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] {//inventory is greater than 0///analog to single operation $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 (' Inventory reduction Success ');
	Flock ($FP, Lock_un);/release lock}else{insertlog (' Inventory reduction failure ');
 }}else{Insertlog (' insufficient stock ');} fclose ($FP);

Optimization Scenario 4: Use the Redis queue, because the pop operation is atomic, even if a lot of users arrive at the same time, but also in sequence, recommended use (MySQL transactions in high concurrency performance degradation is very bad, the way the file lock is also)

First, inventory of goods like a queue

<?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 ');
? >

Snapping, describing 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 (
Uniqid (), 7, 13), 1)), 0, 8);
	}//Log function Insertlog ($event, $type =0) {global $conn; 
	$sql = "INSERT into Ih_log (Event,type) VALUES (' $event ', ' $type ')"; 
mysql_query ($sql, $conn);
//Simulate the Redis queue inventory $redis =new Redis () before single operation//next order;
$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 (' Inventory reduction succeeded ');}  else{Insertlog (' Inventory reduction failed ');}

Simulate 5000 high concurrency test

Webbench-c 5000-t http://192.168.1.198/big/index.php
Ab-r-N 6000-c 5000 http://192.168.1.198/big/index.php

The above is just a simple simulation of high concurrency under the snapping, the real scene is much more complex than this, a lot of attention to the place

Like snapping pages into static, calling interfaces via Ajax

Again as the above will lead to a user Rob multiple, thinking:

Requires a queued queue and snapping result queues and inventory queues. High concurrency, first the user into the queue, with a thread loop processing from the queue to remove a user, to determine whether the user has been snapping up the results queue, if in, it has snapped up, otherwise not snapped up, inventory minus 1, write the database , the user into the results queue.

Test data table

----Database: ' Big '----------------------------------------------------------------the structure of the table ' Ih_goods '--CREATE table
IF not EXISTS ' ih_goods ' (  ' goods_id ' int (a) unsigned NOT NULL auto_increment,   ' cat_id ' int (one) not NULL,   ' goods_name ' varchar (255) not NULL,   PRIMARY KEY (' goods_id ')) Engine=myisam  default Charset=utf8 AUTO


_increment=2;

The data in the----Dump table ' ih_goods '--INSERT into ' ih_goods ' (' goods_id ', ' cat_id ', ' Goods_name ') VALUES (1, 0, ' Millet mobile '); 
 --------------------------------------------------------------table's structure ' ih_log '--CREATE table IF not EXISTS ' Ih_log ' ( ' id ' int (one) not NULL auto_increment, ' event ' varchar (255) isn't 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

; ----the data in the table ' Ih_log '----------------------------------------------------------------the structure of the table ' Ih_order '--CREATE TABLE IF not EXISTS ' Ih_order ' (' id ' int () NOT null auto_increment, ' ORDER_SN ' char (+) NOT null, ' user_id ' int (one) not NULL, ' Status ' Int (one) not null default ' 0 ', ' goods_id ' int (one) not null default ' 0 ', ' sku_id ' int (one) not null default ' 0 ', ' Price ' float not null, ' addtime ' timestamp not null DEFAULT current_timestamp, PRIMARY KEY (' id ') engine=innodb

T Charset=utf8 comment= ' order form ' auto_increment=1; ----the data in the table ' Ih_order '----------------------------------------------------------------the structure of the table ' Ih_store '--creat E TABLE IF not EXISTS ' Ih_store ' (' id ' int (one) not null auto_increment, ' goods_id ' int (one) not null, ' sku_id ' int (10) unsigned NOT NULL default ' 0 ', ' number ' int (a) NOT null default ' 0 ', ' freez ' int (one) not null default ' 0 ' COMMENT ' virtual inventory

', PRIMARY KEY (' id ') engine=innodb DEFAULT charset=utf8 comment= ' inventory ' auto_increment=2;  ----Dump the data in the table ' Ih_store '--INSERT into ' ih_store ' (' id ', ' goods_id ', ' sku_id ', ' number ', ' Freez ') VALUES (1, 1, 11, 500, 0); 

The above is a small series for everyone to bring the PHP combined with Redis to achieve high concurrent buying, second kill function of the full content of the case, I hope that we support cloud-Habitat Community ~