PHP combined with Redis to achieve high concurrent snapping, second-kill function instances

Snapping, second-kill is now a very common application scenario, there are two main issues to solve:

1 high concurrency pressure on the database

2 How to deal with the correct reduction of inventory ("oversold" issue) under competitive status

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

The focus is on the second question.

General wording:

Check the inventory of the corresponding commodity, see if it is greater than 0, and then perform operations such as generating orders, but in determining whether the inventory is greater than 0, if there is a problem in high concurrency, resulting in a negative inventory volume

<?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; Generates a unique 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);} 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 at this time Ih_ The data in the store data goods_id= ' $goods _id ' and sku_id= ' $sku _id ' is locked (note 3), other transactions must wait for the transaction to be committed before the $rs=mysql_query ($sql, $conn) is executed; $row =    MYSQL_FETCH_ASSOC ($RS); if ($row [' number ']>0) {//High concurrency causes 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 succeeded ');    }else{Insertlog (' Inventory reduction failed '); }}else{Insertlog (' insufficient inventory ');}? >

Optimization Scenario 1: Set the Inventory field Number field to unsigned, when inventory is 0 o'clock, because the field cannot be negative, it will return false

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 MySQL transactions 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; Generates a 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);}    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 0mysql_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 the transaction to be committed 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 reductionLess $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 ');    mysql_query ("commit");//Transaction commit is unlocked}else{insertlog (' Inventory reduction failed ');    }}else{Insertlog (' insufficient inventory '); mysql_query ("ROLLBACK");}? >

Optimization Scenario 3: Using 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; Generates a 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);}    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//simulated under 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 succeeded ');    Flock ($FP, lock_un);//Release lock}else{insertlog (' Inventory reduction failed '); }}else{Insertlog (' insufficient inventory ');} Fclose ($FP);

Optimization Scenario 4: Use Redis queue, because the pop operation is atomic, even if there are many users to arrive at the same time, it is recommended to use (MySQL transaction under high concurrency performance degradation is very bad, file lock way also)

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

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; Generates a 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);}    Log function Insertlog ($event, $type =0) {global $conn;    $sql = "INSERT into Ih_log (Event,type) VALUES (' $event ', ' $type ')"; mysql_query ($sql, $conn);} Simulate a single operation//pre-order 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;} 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 ');}

Analog 5000 High concurrency test

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

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

If the snapping page is made static, the interface is called via Ajax

Again as above will cause a user to rob multiple, thinking:

A queued queue and snapping result queue and inventory queue are required. High concurrency, the user first into the queued queue, with a thread loop processing from the queued queue to remove a user, determine whether the user has snapped the results queue, if in, then has snapped, otherwise not snapped, inventory minus 1, write the database, the user into the result queue.

Test Data Sheet

----database: ' Big '----------------------------------------------------------------table structure ' ih_goods '--CREATE table IF not EXISTS ' ih_goods ' (' goods_id ' int () unsigned not null auto_increment, ' cat_id ' int (one) not null, ' Goods_name ' varcha  R (255) not NULL, PRIMARY KEY (' goods_id ')) Engine=myisam DEFAULT Charset=utf8 auto_increment=2; ----Dump the data in the table ' Ih_goods '--INSERT into ' ih_goods ' (' goods_id ', ' cat_id ', ' Goods_name ') VALUES (1, 0, ' Xiaomi phone '); Structure of the--------------------------------------------------------------table ' ih_log '--CREATE table IF not EXISTS ' Ih_log ' (' ID ' int ' is not NULL auto_increment, ' event ' varchar (255) is not NULL, ' type ' tinyint (4) is not null DEFAULT ' 0 ', ' addtime ' timest AMP not NULL default current_timestamp, PRIMARY KEY (' id ')) engine=myisam default Charset=utf8 auto_increment=1; ----The data in the Dump table ' Ih_log '----------------------------------------------------------------table structure ' ih_order '--CREATE table IF not EXISTS ' Ih_order ' (' id ' int (one) 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 is not null, ' addtime ' timestamp not NULL default current_t Imestamp, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8 comment= ' order table ' auto_increment=1; The structure of the data ' ih_order '----------------------------------------------------------------table in the----dump table ' Ih_store '--CREATE TABLE IF not EXISTS ' Ih_store ' (' id ' int (one) not null auto_increment, ' goods_id ' int (one) not null, ' sku_id ' int (Ten) Unsig Ned not null default ' 0 ', ' number ' int (ten) NOT null default ' 0 ', ' freez ' int (one) not null default ' 0 ' COMMENT ' virtual inventory ', PRIM ARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8 comment= ' inventory ' auto_increment=2;  The data in the----dump 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 snapping, seconds kill the function of all the content, I hope we support topic.alibabacloud.com~