Auction system design (knowledge transfer of seckill system)

Time:2021-4-19

Since finishing the article of seckill system (PHP + golang commodity seckill) last time, knowledge migration is a new project, commodity auction.

Technology: PHP, mysql, redis, laravel
Business objects: goods, sessions and orders
Bidding process:

1、 Crud of goods, auction sessions and orders;
2、 The information of seconds, goods and inventory will be written into redis in advance;
3、 Configure redis persistence;
4、 Second kill order logic is realized;
5、 Redis optimization of second kill process;
6、 Use golang concurrent programming to simulate seckill.

1、 Crud of goods, auction sessions and orders;

Commodity list:

CREATE TABLE `goods` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `Num ` varchar (64) not null comment 'commodity number',
  `users_ ID ` int (12) unsigned not null comment 'owner',
  `create_users_id` int(12) unsigned NOT NULL COMMENT ' Product creator ',
  `Name ` varchar (255) not null comment 'trade name',
  `IMG ` int (11) not null comment 'cover map',
  `Price ` decimal (10,2) unsigned not null comment 'current price',
  `area_ ID ` int (11) not null comment 'region ID',
  `user_ Name ` varchar (100) default null comment 'consignee name',
  `user_ Phone ` varchar (11) default null comment 'receiver's contact number',
  `user_ Address ` varchar (255) default null comment 'consignee address',
  `express_id` int(11) DEFAULT NULL COMMENT ' Logistics ID ',
  `express_ No ` varchar (255) default null comment 'logistics order number',
  `is_auction` tinyint(1) NOT NULL DEFAULT '1' COMMENT ' Whether it can be auctioned or not, 1 = < can be auctioned, 2 = < can't be auctioned ',
  `Status ` tinyint (1) unsigned not null default '1' comment 'status 1 = > tradable 2 = > to be paid 3 = > transaction completed 4 = > to be delivered 5 = > in delivery 6 = > completed 7 = > to be received',
  `next_time` timestamp NULL DEFAULT NULL COMMENT ' The earliest time to display next time ',
  `trade_ Time ` timestamp null default null comment 'next tradable time',
  `created_ at` timestamp NOT NULL DEFAULT CURRENT_ Timestamp comment 'creation time',
  `updated_ At ` timestamp null default null comment 'update time',
  `deleted_ At ` timestamp null default null comment 'delete time',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Auction schedule:

CREATE TABLE `auctions` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `Area ` tinyint (4) not null comment 'auction area, 1 = > novice area, 2 = > auction area, 3 = > Star area',
  `Name ` varchar (64) default null comment 'field name',
  `Start ` time not null comment 'start time',
  `End ` time not null comment 'end time',
  `created_ at` timestamp NOT NULL DEFAULT CURRENT_ Timestamp comment 'creation time',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT ' Update time ',
  `deleted_ At ` timestamp null default null comment 'delete time',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_ INCREMENT=10 DEFAULT CHARSET=utf8 ROW_ Format = Dynamic comment ='auction table ';

Order form:

CREATE TABLE `orders` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `serial_num` varchar(32) DEFAULT NULL COMMENT ' Serial number, empty before trading ',
  `goods_id` int(12) unsigned NOT NULL COMMENT ' Commodity ID ',
  `sell_ users_ ID ` int (12) unsigned not null comment 'auction commodity owner ID',
  `buy_ users_ ID ` int (12) unsigned default null comment 'user ID of purchasing goods',
  `buy_ Price ` decision (10,2) not null comment 'purchase price cost price',
  `pay_ Time ` datetime default null comment 'payment time',
  `Status ` char (5) not null comment 'status 10000 = > to be paid 20000 = > payment overtime 30000 = > payment completed 30001 = > in delivery 50000 = > completed',
  `Remark ` varchar (255) default null comment ',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT ' Update time ',
  `deleted_ At ` timestamp null default null comment 'delete time',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

2、 Write redis ahead of time

1. The auction time is three fixed times per day, which is written in advance and the expiration time is set.

2. There are two versions of cache data structure design

a. The first version of the data structure design in the commodity list query, can not exclude their own commodity information and paging.

  • User sets that can be killed in different regions

      key: prefix + area_id + start + end + auctions_id, 
      value:uid 
    
  • Product information Zset in different regions

      key: prefix + area_id + start + end + auctions_id, 
      score:goods_id, 
      member:goods_detail
    
  • Inventory literal quantity

    key: random  
    value:1 
    
  • Do you want to buy space

    key: prefix + area_id + start + end  
    value:1 
    

In order to eliminate its own commodity function and pagination, some implementation schemes are considered
(1) Completely give up obtaining auction product information from the cache, which increases the pressure on the database, and at the same time can not use the auction random code.
(2) For each user to store a set of excluding their own product information, which will store duplicate data and increase the content space.
(3) Redis has a scan command to get data iteratively, and it can use glob pattern matching, but the number of data can’t be determined and can’t be paged.
All the above points (1) and (3) are excluded. Starting from (2), we redesign the data structure of the second version to store the commodity data and the collection of commodity IDs that users can query separately to reduce the duplication, but there will be too many keys, which needs to be optimized.

b. Data structure design of the second version

  • Zset of the commodity ID that the user can query (judge whether the user has competitive commodities)
 key: prefix + area_id +users_id + auction_id+ start + end, 
 score:goods_id, 
 member:goods_id 
  • Commodity information string (pagination is supported)
 key: prefix + area_id + auction_id + goods_id + start + end, 
 value:goods
  • Inventory literal quantity
 key: random  
 value:1 
  • Do you want to buy space
  key: prefix + area_id + start + end  
  value:1 

Auction system design (knowledge transfer of seckill system)

3、 Configure redis persistence

Both persistence modes are enabled: RDB (snapshot mode) + AOF (log mode)
Configuration file: save / append_ only
Difference: the storage interval of RDB is larger than that of AOF

4、 Second kill order logic

1. Query the number of sessions and the current seckill product
When querying the cache data in redis, when the amount of concurrency is large, it may appear as follows:
Cache penetration: the key value does not exist, repeated requests crush the database = > bloom filter or set the cache to null.
Cache breakdown: the key value exists but fails, and the database needs to be requested again, causing concurrency problem = > setnx lock
Cache avalanche: if the cache restarts or fails in a centralized way, all requests are sent to DB = > expiration time setting

2. Formal auction is a separate second kill order function.

3. Specific order logic:
Login verification = > seckill process verification = > place an order asynchronously through the queue and return the order number ordersn at the same time
The check points are as follows:

Second kill time: whether it is within the second kill time;
Do users have competitive products in this area
Random code: whether the goods can be killed in seconds;
Have you bought it or not: set key = session ID through setnx of redis_ Commodity ID_ The user ID is used to judge whether it has been purchased.
Second kill inventory quantity: before obtaining the corresponding inventory information, set setnx as the key to realize the concurrent lock. Set the timeout time to release the lock if the second kill succeeds or fails.

5、 Redis optimization of second kill process

Due to the design of cache data structure, a large number of keys may be stored in redis. If you query through the keys command, it will be o (n) complexity, and the query will be stuck and slow. Redis provides scan iteration to replace keys, but according to this project, it is not necessary to use it.
There are two aspects of optimization
1. When the auction information is written to redis in advance, due to the large number of keys, the pipeline pipeline of redis can be used to improve the writing efficiency
2. As far as possible, the number of sessions and the start and end time are returned to the front end, which are sent to the back end when querying or bidding. The time complexity of the back end splicing key value to obtain data is O (1).

6、 Using golang concurrent programming to simulate seckill

Please refer to another article:PHP + golang commodity second kill function
================================================================

Golang concurrent scheduling project code cloud:

https://gitee.com/jasonlxs/se…

Recommended Today

Analysis of super comprehensive MySQL statement locking (Part 1)

A series of articles: Analysis of super comprehensive MySQL statement locking (Part 1) Analysis of super comprehensive MySQL statement locking (Part 2) Analysis of super comprehensive MySQL statement locking (Part 2) Preparation in advance Build a system to store heroes of the Three KingdomsheroTable: CREATE TABLE hero ( number INT, name VARCHAR(100), country varchar(100), PRIMARY […]