Second kill core design (reduce inventory part)-anti-oversold and high concurrency

Product Details page of static, varnish acceleration, seconds to kill commodity Library Standalone deployment Server This is a bit over the top. Discuss the optimization of the inventory section only

MySQL configuration level 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: Weighing table (buy_record), the user has no seconds to kill the product

Fields: ID, uid, goods_id, Addtime

Second table: Commodity table Goods

Fields: goods_id goods_num

Programme 1:

Start transaction;

The select ID from Buy_record where uid= $uid and goods_id= $goods _id;

if (the result is not NULL)

Throw the exception and roll back.

Insert INTO Buy_record ...

if (affected number of rows <=0)

Throw the exception, roll back ...

Select Goods_num from goods where goods_id= $good _id;

if (inventory <=0)

Throw the exception, roll back ...

Update goods set goods_num=goods_num-1 where goods_id= $goods _id;

if (affected number of rows <=0)

This method is almost inevitable to lead to oversold under high concurrency.  When the inventory is 1, just a few users simultaneously select Goods_num from goods where goods_id= $good _id; Inventory is just over 0, the update operation must be reduced to less than 0. At the same time whether the second kill the weight of the same problem will be similar

Programme II:

Start transaction;

Select ID from Buy_record where uid= $uid and goods_id= $goods _id for update;

if (the result is not NULL)

Throw the exception and roll back.

Insert INTO Buy_record ...

if (affected number of rows <=0)

Throw the exception, roll back ...

Select Goods_num from goods where goods_id= $good _id for update;

if (inventory <=0)

Throw the exception, roll back ...

Update goods set goods_num=goods_num-1 where goods_id= $goods _id;

if (affected number of rows <=0)

Throw the exception, roll back ...

This method effectively prevents the oversold, but adds the exclusive lock each time select, each select operation will be blocked, the concurrency performance is greatly reduced.

Scheme III: Add a unique index to (uid,goods_id).

Start transaction;

Insert INTO Buy_record ...

If (the unique index is an error.) )

Throw exception, already seconds past, rollback ...

Update goods set goods_num=goods_num-1 where goods_id= $goods _id and goods_num>0;

if (affected number of rows <=0)

Throw exception, product seconds are over, roll back ...

This method perfectly solves the problem of low concurrency caused by oversold and select exclusive locks, and 4 SQL shrinks to 2 SQL statements. greatly enhance performance