preface

When it comes to solving high concurrency problems through multi-threading, thread safety is often the first concern, followed by performance. There are many technical solutions to the inventory oversold problem, such as pessimistic locking, distributed locking, optimistic locking, queue serialization, Redis atomic operation, etc. This article uses MySQL optimistic locking to demonstrate the basic implementation.

Pre-development preparation

1. Environment parameters

  • Development tool: IDEA
  • Basic tools: Maven+JDK8
  • Technology: SpringBoot+Mybatis
  • Database: MySQL5.7
  • SpringBoot version: 2.2.5.release

2. Create a database

With basic Scheme already in place, the demo is best done with the simplest data structures.

DROP TABLE IF EXISTS `goods`; CREATE TABLE 'goods' (' id' int(11) NOT NULL AUTO_INCREMENT COMMENT' increment ', 'name' varchar(30) DEFAULT NULL COMMENT '主 体 ',' stock 'int(11) DEFAULT '0' COMMENT' 主 体 ', 'version' int(11) DEFAULT '0' COMMENT ', PRIMARY KEY (' id ')) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '表'; INSERT INTO `goods` VALUES (1, 'iphone', 10, 0); INSERT INTO `goods` VALUES (2, 'huawei', 10, 0); DROP TABLE IF EXISTS `order`; CREATE TABLE 'order' (' id' int(11) AUTO_INCREMENT, 'uid' int(11) COMMENT 'id',' gid 'int(11) COMMENT' id', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '表';Copy the code

If you don’t have an environment, you can quickly build the MySQL environment by using the MySQL master-slave replication in Docker combat. Create database test and import related SQL to initialize Table.

3. Configure dependencies in poM files

Below is the POM.xml dependency configuration.

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
Copy the code

4. Configuration application. Yml

As MyBatis in the demo is based on interface mapping, it is easy to configure. In application. Yml, you only need to configure mysql

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3307/test? allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
    username: root
    password: root
Copy the code

5. Create related beans

package com.idcmind.ants.entity;

public class Goods {

    private int id;
    private String name;
    private int stock;
    private intversion; . Omit getters, setters, and toString methods}Copy the code
public class Order {

    private int id;
    private int uid;
    private intgid; . Omit getters, setters, and toString methods}Copy the code

Optimistic lock solution for overselling inventory

1. Dao layer development

GoodsDao.java

@Mapper
public interface GoodsDao {

    /** **@paramId Id of the product *@return* /
    @Select("SELECT * FROM goods WHERE id = #{id}")
    Goods getStock(@Param("id") int id);

    /** * Optimistic lock scheme to reduce inventory *@paramId Id of the product *@paramVersion Version *@return* /
    @Update("UPDATE goods SET stock = stock - 1, version = version + 1 WHERE id = #{id} AND stock > 0 AND version = #{version}")
    int decreaseStockForVersion(@Param("id") int id, @Param("version") int version);
}
Copy the code

OrderDao.java

It is important to note here that since order is the keyword in SQL, the table name needs to be quoted back.

@Mapper
public interface OrderDao {
    
    /** * insert order * note: the order table is a keyword that requires' order '*@param order
     */
    @Insert("INSERT INTO `order` (uid, gid) VALUES (#{uid}, #{gid})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertOrder(Order order);
}
Copy the code

2. Service layer development

GoodsService.java

@Service
public class GoodsService {

    @Autowired
    private GoodsDao goodsDao;
    @Autowired
    private OrderDao orderDao;

    /** * deduct inventory *@paramGid Commodity ID *@paramUid User ID *@return SUCCESS 1 FAILURE 0
     */
    @Transactional
    public int sellGoods(int gid, int uid) {

        // Get inventory
        Goods goods = goodsDao.getStock(gid);
        if (goods.getStock() > 0) {
            // Optimistic lock update inventory
            int update = goodsDao.decreaseStockForVersion(gid, goods.getVersion());
            // Update failed, indicating that other threads have modified the data, this time inventory reduction failed, you can retry a certain number of times or return
            if (update == 0) {
                return 0;
            }
            // Inventory is deducted successfully, and order is generated
            Order order = new Order();
            order.setUid(uid);
            order.setGid(gid);
            int result = orderDao.insertOrder(order);
            return result;
        }
        // Return on failure
        return 0; }}Copy the code

Concurrent test

Here we write a unit test for concurrent testing.

@SpringBootTest
class GoodsServiceTest {

    @Autowired
    GoodsService goodsService;

    @Test
    void seckill(a) throws InterruptedException {

        // The inventory is initialized to 10, where 100 concurrency is simulated through CountDownLatch and thread pool
        int threadTotal = 100;

        ExecutorService executorService = Executors.newCachedThreadPool();

        final CountDownLatch countDownLatch = new CountDownLatch(threadTotal);
        for (int i = 0; i < threadTotal ; i++) {
            int uid = i;
            executorService.execute(() -> {
                try {
                    goodsService.sellGoods(1, uid);
                } catch(Exception e) { e.printStackTrace(); } countDownLatch.countDown(); }); } countDownLatch.await(); executorService.shutdown(); }}Copy the code

Check the database to verify that it is oversold

The results of the chart above are in line with our expectations. Concurrent testing can also be done via Postman or Jmeter. Since it is not the focus here, I will not do the demonstration, interested partners can leave a message, I will sort out the relevant tutorial.

subsequent

This article has solved the problem of oversold inventory through database optimistic locking, but it is not the optimal solution in terms of efficiency. The demonstration of other solutions will be improved in the future. If there are mistakes and omissions in the article, but also hope you don’t hesitate to give advice.

Public number [When I meet you]