Preface

In commodity second killing activities, such as commodity inventory is only 100, but in the rush buying activities, there may be 200 people rushing to buy at the same time, so there is concurrence, 100 items of goods under order to complete the inventory of 0, and may continue to order successfully, there is oversold.

To solve this problem, today I'm going to focus on the redis queue. Redis has a list type, which is actually a two-way linked list. Through lpush, the pop operation adds and deletes elements from the head or tail of the list. This allows lists to be used either as stacks or as queues. First in, first out, one end in and one end out, this is the queue. After the first one is finished in the queue, the second one will go, so redis queue can solve the problem of overselling concurrency perfectly.

There are also some ways to solve the problem of second kill oversell, such as: 1. using mysql transaction plus exclusive lock to solve; 2. using file lock to achieve. 3. Use redis setnx to implement lock mechanism. View click: Four Solutions to Avoid Overselling

Realization principle

Loop the commodity inventory lpush into num, and then take out one item at a time through rpop when placing an order. When num is 0, stop placing an order.

Step 1 Create tables

There are three tables: order form, commodity form and log form.

1. order form

CREATE TABLE `ims_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_sn` char(32) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `goods_id` int(11) NOT NULL DEFAULT '0',
  `sku_id` int(11) NOT NULL DEFAULT '0',
  `number` int(11) NOT NULL,
  `price` int(10) NOT NULL COMMENT 'Price: points per unit',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5820 DEFAULT CHARSET=utf8 COMMENT='Order form'

2. commodity list

CREATE TABLE `ims_hotmallstore_goods` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT 'Trade name',
  `type_id` int(11) NOT NULL COMMENT 'Commodity classification',
  `img` text NOT NULL COMMENT 'Commodity pictures',
  `money` decimal(10,2) NOT NULL COMMENT 'price',
  `money2` decimal(10,2) NOT NULL COMMENT 'Original price',
  `is_show` int(11) NOT NULL DEFAULT '1' COMMENT '1.On the shelf 2.Lower shelf',
  `uniacid` int(11) NOT NULL COMMENT 'Small program id',
  `inventory` int(11) NOT NULL COMMENT 'Stock',
  `details` text NOT NULL COMMENT 'details',
  `store_id` int(11) NOT NULL COMMENT 'business id',
  `sales` int(11) NOT NULL COMMENT 'Sales volume',
  `logo` varchar(100) NOT NULL,
  `num` int(11) NOT NULL,
  `is_gg` int(11) NOT NULL DEFAULT '2' COMMENT 'Whether to Open Specification',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

3. log list

CREATE TABLE `ims_order_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` int(11) NOT NULL DEFAULT '0',
  `msg` text CHARACTER SET utf8,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=4562 DEFAULT CHARSET=gb2312 COMMENT='Order Log Table'

Step 2 Writing Code

class Test {

    private static $instance = null;

    // Instantiating Redis with a Single Column Pattern
    public static function Redis()
    {
        if (self::$instance == null) {
            $redis=new \Redis();
            $redis->connect('127.0.0.1',6379);
            self::$instance = $redis;
        }
        return self::$instance;
    }

    // Circulating commodity inventory to num of lpush
    public function doPageSaveNum()
    {
        $redis=self::Redis();
        $goods_id=1;
        $sql="select id, num, money from ims_hotmallstore_goods where id=".$goods_id;
        $goods=pdo_fetch($sql);
        if(!empty($goods)){
         for($i=1; $i<=$goods['num']; $i++){
             $redis->lpush('num',$i);
         }
         die('Success!');
        }else{
         $this->echoMsg(0,'Commodities do not exist.');
        }
    }

    // Snap up order
    public function doPageGoodsStore()
    {       
            $goods_id=1;
            $sql="select id, num, money from ims_hotmallstore_goods where id=".$goods_id;
            $goods=pdo_fetch($sql);
            $redis=self::Redis();
            $count=$redis->rpop('num');//Remove 1 from num at a time
            if($count==0){
                $this->echoMsg(0,'no num redis');
            }
            $this->doPageGoodsOrder($goods,1);
            
    }

    // Save log
    public function echoMsg($status,$msg,$_data="")
    { 
      
        $data=json_encode(array('status'=>$status,'msg'=>$msg,'data'=>$_data),JSON_UNESCAPED_UNICODE);
        $order_log['status']=$status;
        $order_log['msg']=$msg;
        $order_log['create_time']=time();
        pdo_insert('order_log',$order_log);
       die($data);
    }
    public function orderNo()
    {
        return date('ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);  
    }
    
    // Order Update Inventory
    public function doPageGoodsOrder($goods,$goods_number)
    {   
        $orderNo=$this->orderNo();
        $number=$goods['num']-$goods_number;
        if($number<0){
            $this->echoMsg(0,'There is no stock left.');
        }
        $user_id=rand(1,500);
        $order['user_id']=$user_id;
        $order['goods_id']=$goods['id'];
        $order['number']=$goods_number;
        $order['price']=$goods['money'];
        $order['status']=1;
        $order['sku_id']=2;
        $order['order_sn']=$orderNo;
        $order['create_time']=date('Y-m-d H:i:s');
        pdo_insert('order',$order);
        $sql="update ims_hotmallstore_goods set num=num-".$goods_number." where num>0 and id=".$goods['id'];
        $res=pdo_query($sql);
        if(!empty($res)){
            $this->echoMsg(1,'Successful inventory deduction'.$number);
        }
        $redis=self::Redis();
        $redis->lpush('num',$goods_number);
        $this->echoMsg(0,'Failure of inventory deduction'.$number);

    }
 }

// Call -- Loop the inventory into lpush's num
if($_GET['i']==1){
   $model = new Test;
   $model->doPageSaveNum();
}

// Call -- High concurrent snap-up order
if($_GET['i']==2){
   $model = new Test;
   $model->doPageGoodsStore();
}

Step 3 Concurrent Testing

1. Manual execution: http://127.0.0.1/wqchunjingsvn/web/index.php?i=1. Store the inventory cycle in num of lpush.

2. Here I use Apache's ab test, installation method, this article is the last supplement. Open the terminal and execute: ab-n 1000-c 200 http://127.0.0.1/wqchunjingsvn/web/index.php?I=2
(-n sends 1000 requests, -c simulates 200 concurrent requests, and the number of requests must be greater than or equal to the number of concurrent requests. Quite 1,000 people visited at the same time, followed by the test url.

3. Observe whether the execution is successful or not. The results of the execution are shown in the following figure to illustrate the success of the execution.

Step 4 View the Data Table

1. Look at the order form, the total order quantity is 100, as shown below, no problem.

2. Check the inventory of goods, has changed from 100 to 0, no problem.

3. Look at the log table, there are 137 records in total, of which only 100 are status 1. That's OK.

Summary and analysis

1. The scheme is feasible, the stock is 0, and there is no oversold.

2. When testing high concurrency with Apache's ab, we need to pay attention to the fact that the Url address can't be spliced with the parameters with a & sign, otherwise the execution will fail.

Related information

Using redis to solve the problem of second killing oversold in php
How to Solve the Oversell Problem of High Concurrent Secondary Kill
Mac installs Apache http server (using Apache ab test, installation method)
Detailed Pressure Testing Using ab
mysql concurrent updates