[Go] MySQL-based seconds kill core design (reduce inventory)-anti-oversold and high concurrency

Source: Internet
Author: User

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

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.