The static of the Product Details page, varnish acceleration, seconds to kill the commodity library standalone deployment Server This is a little over. Only discuss the optimization of the inventory section
MySQL configuration level of optimization can refer to my article "about MySQL InnoDB engine performance optimization of a little experience"
The focus is on the database level.
2 Sheets:
First: the scale (Buy_record), the user has no seconds to kill the product
Fields: ID, uid, goods_id, Addtime
Table Two: Commodity table goods
Field: goods_id goods_num
Scenario 1:
Start transaction;
Select ID from Buy_record where uid= $uid and goods_id= $goods _id;
if (the result is not empty)
Throw an exception and roll back.
Insert INTO Buy_record ...
if (number of rows affected <=0)
Throw an exception, roll back ...
Select Goods_num from goods where goods_id= $good _id;
if (stock <=0)
Throw an exception, roll back ...
Update goods set goods_num=goods_num-1 where goods_id= $goods _id;
if (number of rows affected <=0)
This method almost inevitably leads to oversold under high concurrency. When inventory is 1, just a few users at the same time select Goods_num from goods where goods_id= $good _id, when the inventory is just over 0, when the update operation must be reduced to less than 0. A similar problem occurs when the above-mentioned penalty is executed in seconds.
Scenario Two:
Start transaction;
Select ID from Buy_record where uid= $uid and goods_id= $goods _id for update;
if (the result is not empty)
Throw an exception and roll back.
Insert INTO Buy_record ...
if (number of rows affected <=0)
Throw an exception, roll back ...
Select Goods_num from goods where goods_id= is $good _id for update;
if (stock <=0)
Throw an exception, roll back ...
Update goods set goods_num=goods_num-1 where goods_id= $goods _id;
if (number of rows affected <=0)
Throw an exception, roll back ...
This method effectively prevents the oversold, but in each select time with an exclusive lock, each select operation will be blocked, concurrency performance greatly reduced.
Scenario Three: to (UID,GOODS_ID) plus unique index!!
Start transaction;
Insert INTO Buy_record ...
if (unique index error?) )
Throw an exception, already seconds, roll back ...
Update goods set goods_num=goods_num-1 where goods_id= $goods _id and goods_num>0;
if (number of rows affected <=0)
Throw the exception, the commodity seconds are finished, rollback ...
This method perfectly solves the problem of low concurrency caused by the oversold and select row locks, and 4 SQL is reduced to 2 SQL statements. Vastly improved performance
From:http://www.tuicool.com/articles/bfa63e6
[Go] MySQL-based seconds kill core design (reduce inventory)-anti-oversold and high concurrency