Of Stack

php combined with redis to achieve high concurrency under the panic the second kill function of the example

  • 2020-05-15 02:30:52
  • OfStack

Panic buying and seckilling are common application scenarios nowadays, and there are two main problems to be solved:

High concurrency puts pressure on the database

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

For the first problem, it is already easy to think of caching to handle the rush, avoiding direct database manipulation, such as Redis.

The focus is on the second question

Normal writing:

Query the inventory of the corresponding goods to see if it is greater than 0, and then execute operations such as order generation. However, when judging whether the inventory is greater than 0, there will be problems under 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;

// Generated only 1 The order 
function build_order_no(){
  return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(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); 
}

// Simulated ordering operation 
// Whether the inventory is greater than 0
$sql="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id'";// unlock   At this time ih_store In the data goods_id='$goods_id' and sku_id='$sku_id'  The data is locked ( note 3) , other transactions must wait for this transaction   You can't execute until you commit 
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row['number']>0){// High concurrency can lead to oversold 
	$order_sn=build_order_no();
	// To generate orders  
	$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(' Inventory is not enough ');
}
?>

Optimization plan 1: set the number field of the inventory field to unsigned. When the inventory is 0, because the field cannot be negative, false will be returned 


// 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 ');
}

Optimization scenario 2: use the MySQL transaction to lock the rows of the 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;

// Generated only 1 The order number 
function build_order_no(){
  return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(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); 
}

// Simulated ordering operation 
// Whether the inventory is greater than 0
mysql_query("BEGIN");	// Start the transaction 
$sql="select number from ih_store where goods_id='$goods_id' and sku_id='$sku_id' FOR UPDATE";// At this point the record is locked , Other transactions must wait for this transaction to commit before executing 
$rs=mysql_query($sql,$conn);
$row=mysql_fetch_assoc($rs);
if($row['number']>0){
	// To generate orders  
	$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 is unlocking 
	}else{ 
		insertLog(' Inventory reduction failed ');
	}
}else{
	insertLog(' Inventory is not enough ');
	mysql_query("ROLLBACK");
}
?>

Optimization option 3: use a 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;

// Generated only 1 The order number 
function build_order_no(){
  return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(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 " The system is busy, please try again later ";
	return;
}
// Place the 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){// Whether the inventory is greater than 0
	// Simulated ordering 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(' Successful inventory reduction ');
		flock($fp,LOCK_UN);// Release the lock 
	}else{ 
		insertLog(' Inventory reduction failed ');
	} 
}else{
	insertLog(' Inventory is not enough ');
}
fclose($fp);

Optimization solution 4: redis queue is used, because the pop operation is atomic, even if there are many users arriving at the same time, they will be executed in sequence. It is recommended to use mysql transaction (the performance of mysql transaction degrades greatly under high concurrency, and the way of file locking is also the same).

First, the inventory of goods as 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');
?>

Panic buying, 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;

// Generated only 1 The order number 
function build_order_no(){
  return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(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); 
}

// Simulated ordering operation 
// Pre-order judgment redis Queue inventory 
$redis=new Redis();
$result=$redis->connect('127.0.0.1',6379);
$count=$redis->lpop('goods_store');
if(!$count){
	insertLog('error:no store redis');
	return;
}

// To generate orders  
$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 ');
}

Simulate 5000 high concurrency tests

webbench -c 5000 -t 60 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 rush, the real scene is much more complex than this, a lot of attention

If the page is static, call the interface via ajax

Then, as mentioned above, one user will rob more than one user.

Need 1 queue and buy result queue and stock queue. In the case of high concurrency, the user is first put into the queue, and then one user is taken out of the queue by a thread of loop processing to judge whether the user is already snapping up the result queue. If the user is in the queue, he/she has already snapped up the result queue. If not, he/she has already snapped up the result queue.

Test data sheet 


--
--  The database : `big`
--

-- --------------------------------------------------------

--
--  The structure of the table  `ih_goods`
--


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 ;


--
--  Transfer the data in the table  `ih_goods`
--


INSERT INTO `ih_goods` (`goods_id`, `cat_id`, `goods_name`) VALUES
(1, 0, ' Millet mobile phone ');

-- --------------------------------------------------------

--
--  The structure of the table  `ih_log`
--

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 ;

--
--  Transfer the data in the table  `ih_log`
--


-- --------------------------------------------------------

--
--  The structure of the table  `ih_order`
--

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=' The order sheet ' AUTO_INCREMENT=1 ;

--
--  Transfer the data in the table  `ih_order`
--


-- --------------------------------------------------------

--
--  The structure of the table  `ih_store`
--

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 ' Virtual inventory ',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' inventory ' AUTO_INCREMENT=2 ;

--
--  Transfer the data in the table  `ih_store`
--

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