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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.