How does the flash sale system avoid oversold inventory?

public String buy(Long goodsId, Integer goodsNum) {
    //Query product inventory
    Goods goods = goodsMapper.selectById(goodsId);
    //If the current inventory is 0, it will prompt that the product has been sold out.
    if (goods.getGoodsInventory() <= 0) {
        return "The product has been sold out!";
    }
    //If the current purchase quantity is greater than the inventory, it will prompt that the inventory is insufficient.
    if (goodsNum > goods.getGoodsInventory()) {
        return "Insufficient stock!";
    }
    //update inventory
    goods.setGoodsInventory(goods.getGoodsInventory() - goodsNum);
    goodsMapper.updateById(goods);
    return "Purchase successful!";
}

Let’s take a look at this string of code. The logic is represented by a flow chart as follows:

Problem Analysis

Under normal circumstances, if the requests come one after another, there will be no problem with this string of code, as shown below:

Different requests are made at different times, and the product inventory obtained each time is updated, and the logic is ok.

So why does the oversold problem occur? First, we add a scenario to this string of code: product flash sale (it is difficult to reproduce the overselling problem in non-flash sale scenarios). The characteristics of the flash kill scene are as follows:

  • High concurrency processing: In a flash sale scenario, a large number of shoppers may flood into the system at the same time, so it needs to have high concurrency processing capabilities to ensure that the system can withstand high concurrent access and provide fast response.
  • Quick response: In the flash sale scenario, due to time constraints and fierce competition, the system needs to be able to respond to shoppers’ requests quickly, otherwise the purchase may fail and affect the shopper’s shopping experience.
  • Distributed system: In the flash sale scenario, a single server cannot handle the request peak. The distributed system can improve the fault tolerance and pressure resistance of the system, which is very suitable for the flash sale scenario.

In this scenario, the requests cannot come one after another, but thousands of requests come in at the same time. Then there will be multiple requests querying the inventory at the same time, as shown below:

If the product inventory table is queried at the same time, the product inventory obtained must be the same, and the judgment logic is also the same.

For example, the current inventory of the product is 10 pieces. Request 1 buys 6 pieces, and request 2 buys 5 pieces. Since the inventory found in both requests is 10, it can definitely be sold. But the real situation is 5 + 6=11>10, which is obviously a problem! It is only right that one of these two requests must fail!

Solution

Judging from the above example, the problem seems to be that the inventory we get is the same every time, which leads to the oversold inventory problem. Isn’t this problem solved as long as we ensure that the inventory we get is the latest every time? Yet!

Before talking about the plan, let me first post my test table structure:

CREATE TABLE `t_goods` (
  `id` bigint NOT NULL COMMENT 'Physical primary key',
  `goods_name` varchar(64) DEFAULT NULL COMMENT 'goods name',
  `goods_pic` varchar(255) DEFAULT NULL COMMENT 'product picture',
  `goods_desc` varchar(255) DEFAULT NULL COMMENT 'product description information',
  `goods_inventory` int DEFAULT NULL COMMENT 'goods inventory',
  `goods_price` decimal(10,2) DEFAULT NULL COMMENT 'goods price',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_time` datetime DEFAULT NULL COMMENT 'update time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Method 1, redis distributed lock

Redisson introduction

Official introduction: Redisson is a Java in-memory data grid (In-Memory Data Grid) based on Redis. It encapsulates the Redis client API and provides commonly used data structures and services such as distributed locks, distributed collections, distributed objects, and distributed maps. Redisson supports Java 6 and above and Redis 2.6 and above, and uses codecs and serializers to support any object type. Redisson also provides some advanced features, such as asynchronous API and reactive streaming API. It can be used to achieve high availability, high performance, and high scalability data processing in distributed systems.

Redisson use

Introduction
<!--Use redisson as a distributed lock-->
<dependency>
  <groupId>org.redisson</groupId>
  <artifactId>redisson</artifactId>
  <version>3.16.8</version>
</dependency>
Injection object

RedissonConfig.java

import org.redisson.Redisson;
import org.redisson.api.RedissonClient;
import org.redisson.config.Config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class RedissonConfig {
    /**
     * All uses of Redisson are through the RedissonClient object
     *
     * @return
     */
    @Bean(destroyMethod = "shutdown")
    public RedissonClient redissonClient() {
        //Create configuration and specify redis address and node information
        Config config = new Config();
        config.useSingleServer().setAddress("redis://127.0.0.1:6379").setPassword("123456");

        //Create a RedissonClient instance based on config
        RedissonClient redissonClient = Redisson.create(config);
        return redissonClient;

    }
}

Code optimization

public String buyRedisLock(Long goodsId, Integer goodsNum) {
    RLock lock = redissonClient.getLock("goods_buy");
    try {
        //Add distributed lock
        lock.lock();
        //Query product inventory
        Goods goods = goodsMapper.selectById(goodsId);
        //If the current inventory is 0, it will prompt that the product has been sold out.
        if (goods.getGoodsInventory() <= 0) {
            return "The product has been sold out!";
        }
        //If the current purchase quantity is greater than the inventory, it will prompt that the inventory is insufficient.
        if (goodsNum > goods.getGoodsInventory()) {
            return "Insufficient stock!";
        }
        //update inventory
        goods.setGoodsInventory(goods.getGoodsInventory() - goodsNum);
        goodsMapper.updateById(goods);
        return "Purchase successful!";
    } catch (Exception e) {
        log.error("Second kill failed");
    } finally {
        lock.unlock();
    }
    return "Purchase failed";
}

After adding the Redisson distributed lock, the request changes from asynchronous to synchronous, allowing purchase operations to be performed one by one, solving the problem of oversold inventory, but it will lengthen the user’s waiting time and affect the user experience.

Method 2, MySQL row lock

Introduction to row locks

MySQL’s row lock is a lock for row-level data. It can lock a certain row of data in a table to ensure that other transactions cannot modify the row data during the lock period, thus ensuring the consistency and integrity of the data. Features are as follows:

  • MySQL row locks can only be used in the InnoDB storage engine.
  • Row lock requires an index to be implemented, otherwise the entire table will be automatically locked.
  • Row locks can be used explicitly by using the “SELECT … FOR UPDATE” and “SELECT … LOCK IN SHARE MODE” statements.

In short, row locks can effectively ensure data consistency and integrity, but too many row locks can also cause performance problems. Therefore, you need to consider carefully when using row locks to avoid performance bottlenecks.

So back to the issue of oversold inventory, we can add a row lock when initially querying product inventory. The implementation is very simple, that is,

//Check product inventory
Goods goods = goodsMapper.selectById(goodsId);

Raw query SQL
SELECT *
  FROM t_goods
  WHERE id = #{goodsId}

rewritten as
 SELECT *
  FROM t_goods
  WHERE id = #{goodsId} for update

Then the queried product inventory information will be locked. When other requests want to read this row of data, they need to wait for the current request to end. In this way, each inventory query will be the latest. However, like the Redisson distributed lock, it will lengthen the waiting time for users and affect the user experience.

Method 3, optimistic locking

The optimistic locking mechanism is similar to the cas mechanism in Java. It does not lock when querying data. Only when updating the data, it compares whether the data has changed. If there is no change, the update operation is performed. If it has changed, retry.

Add a version field to the product table and initialize the data to 0

`version` int(11) DEFAULT NULL COMMENT 'version'

Modify the update SQL as follows

update t_goods
set goods_inventory = goods_inventory - #{goodsNum},
     version = version + 1
where id = #{goodsId}
and version = #{version}

The Java code is modified as follows

public String buyVersion(Long goodsId, Integer goodsNum) {
    //Query product inventory (this statement uses row lock)
    Goods goods = goodsMapper.selectById(goodsId);
    //If the current inventory is 0, it will prompt that the product has been sold out.
    if (goods.getGoodsInventory() <= 0) {
        return "The product has been sold out!";
    }
    if (goodsMapper.updateInventoryAndVersion(goodsId, goodsNum, goods.getVersion()) > 0) {
        return "Purchase successful!";
    }
    return "Insufficient stock!";
}

By adding version number control, version numbers can be compared under the where condition when deducting inventory. Which record is being queried, then which record is required to be updated. The version number cannot be changed during the process of querying and updating, otherwise the update will fail.

Method 4, where conditions and unsigned non-negative field restrictions

The previous Redisson distributed locks and row locks solve the oversold problem by getting the latest inventory every time. Let’s change the idea: ensure that when deducting inventory, the inventory must be greater than the purchase amount. Can this be solved as well? What’s the problem? The answer is yes. Back to the code above:

//Update inventory
goods.setGoodsInventory(goods.getGoodsInventory() - goodsNum);
goodsMapper.updateById(goods);

We write the inventory deduction in the code, but this is definitely not possible, because in a distributed system, the inventory we obtain may be the same, and the inventory deduction logic should be put into SQL, that is:

 update t_goods
 set goods_inventory = goods_inventory - #{goodsNum}
 where id = #{goodsId}

The above SQL ensures that the inventory obtained every time is the inventory in the database, but we also need to add a judgment: ensure that the inventory is greater than the purchase amount, that is:

update t_goods
set goods_inventory = goods_inventory - #{goodsNum}
where id = #{goodsId}
AND (goods_inventory - #{goodsNum}) >= 0

Then the above Java code also needs to be modified:

public String buySqlUpdate(Long goodsId, Integer goodsNum) {
    //Query product inventory (this statement uses row lock)
    Goods goods = goodsMapper.queryById(goodsId);
    //If the current inventory is 0, it will prompt that the product has been sold out.
    if (goods.getGoodsInventory() <= 0) {
        return "The product has been sold out!";
    }
    //Here you need to determine whether the update operation is successful.
    if (goodsMapper.updateInventory(goodsId, goodsNum) > 0) {
        return "Purchase successful!";
    }
    return "Insufficient stock!";
}

Another method is the same as the where condition, which is the unsigned non-negative field restriction. Set the inventory field to the unsigned non-negative field type, so that the deduction will not be negative during deduction.

Summary

solution

advantage

shortcoming

redis distributed lock

Redis distributed lock can solve the lock problem in distributed scenarios, ensure the order and security of access to the same resource by multiple nodes, and have high performance.

Single point of failure problem, if the Redis node goes down, the lock will become invalid.

MySQL row lock

It can ensure the isolation of transactions and avoid data conflicts in concurrency situations.

The performance is low, which has a great impact on the performance of the database, and there are also deadlock problems.

optimistic locking

Compared with pessimistic locks, optimistic locks do not block threads and have higher performance.

Additional version control fields are required, and concurrency conflicts are prone to occur in high concurrency situations.

where conditions and unsigned non-negative field restrictions

You can use where conditions and unsigned non-negative field restrictions to ensure that the inventory will not be oversold, which is simple and easy to implement.

There may be certain security risks, and if certain operations are not properly restricted, it may still lead to inventory oversold issues. At the same time, if some scenarios require multiple update operations on the inventory, the restriction may cause the operation to fail and the data needs to be queried again, which will have an impact on performance.

There are many solutions. Judging from the usage and actual business, there is no optimal solution, only better solutions. Several solutions can even be combined to solve the problem.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate the databaseDatabase operation 138171 people are learning the system