Second kill core design (inventory reduction)-anti-overselling and high concurrency, inventory --

Second kill core design (inventory reduction)-anti-overselling and high concurrency, inventory --

From: http://www.tuicool.com/articles/Bfa63e6

Static product details page, varnish acceleration, second-kill product library independent deployment of servers this is skipped. Only the optimization of inventory is discussed.
For mySQL configuration optimization, refer to my article "some experiences on mysql innodb engine performance optimization".
Focuses on the database layer.
Two tables:
First: duplicate table (buy_record). This user has not killed this item in seconds.
Field: id, uid, goods_id, addtime
Table 2: item table goods
Field: goods_id goods_num
Solution 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 (affected rows <= 0)
Throw an exception and roll back...
Select goods_num from goods where goods_id = $ good_id;
If (Inventory <= 0)
Throw an exception and roll back...
Update goods set goods_num = goods_num-1 where goods_id = $ goods_id;
If (affected rows <= 0)
This method is almost oversold in high concurrency. When the inventory is 1, multiple users select goods_num from goods where goods_id = $ good_id at the same time. At this time, the inventory is just greater than 0, and the update operation must be reduced to less than 0. at the same time, a similar problem may occur when I judge whether the second kill has been performed.
Solution 2:
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 (affected rows <= 0)
Throw an exception and roll back...
Select goods_num from goods where goods_id = $ good_id for update;
If (Inventory <= 0)
Throw an exception and roll back...
Update goods set goods_num = goods_num-1 where goods_id = $ goods_id;
If (affected rows <= 0)
Throw an exception and roll back...
This method effectively prevents overselling, but the exclusive lock is added each time the select Operation is blocked, and the concurrency performance is greatly reduced.
Solution 3: Add a unique index to (uid, goods_id !!
Start transaction;
Insert into buy_record...
If (unique index error ?)
Throw an exception. It takes several seconds to roll back...
Update goods set goods_num = goods_num-1 where goods_id = $ goods_id and goods_num> 0;
If (affected rows <= 0)
Throw an exception. The product is finished in seconds and rolled back...
This method perfectly solves the problem of low concurrency caused by overselling and select exclusive locks, and reduces four SQL statements to two. Greatly improves performance