(6) Actual combat of inventory oversold cases – using mysql distributed locks to solve the “oversold” problem

Foreword

This section is the final article on using distributed locks to solve the “oversold” problem of concurrent access. In the previous chapters, we introduced the use of mysql’s row locks, optimistic locks, and pessimistic locks to solve the oversold problem caused by concurrent access. There are The problem is that row locks, optimistic locks, and pessimistic locks are not very flexible and need to be coupled with specific businesses, which is not conducive to use. In this section, we use the unique index feature of mysql to prevent data from being inserted repeatedly to achieve the basic principle of exclusiveness. Requirements to implement universal distributed lock function.

Text

  • Create table app_lock to implement distributed locks

– app_lock table

CREATE TABLE `app_lock` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `lock_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'lock name',
  `class_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'class name',
  `method_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'method name',
  `server_ip` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Server IP',
  `thread_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'thread name',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `expire_time` datetime DEFAULT NULL COMMENT 'expire time',
  `remark` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'Remark',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_lock_name` (`lock_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Application Lock';

PS: lock_name is the lock name. Create a unique index to ensure the uniqueness of the lock. The server_ip host information and thread_name thread number can extend the distributed lock to a reentrant lock. The expire_time expiration time can be passed through a scheduled task or message intermediate key. Automatically release the lock.

  • Table app_lock entity class creation 
package com.ht.atp.plat.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;


@Data
@TableName("app_lock")
@ApiModel(value = "AppLock object", description = "Application Lock")
public class AppLock implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("ID")
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @ApiModelProperty("lock name")
    @TableField("lock_name")
    private String lockName;

    @ApiModelProperty("class name")
    @TableField("class_name")
    private String className;

    @ApiModelProperty("method name")
    @TableField("method_name")
    private String methodName;

    @ApiModelProperty("Server IP")
    @TableField("server_ip")
    private String serverIp;

    @ApiModelProperty("thread name")
    @TableField("thread_name")
    private String threadName;

    @ApiModelProperty("Creation time")
    @TableField("create_time")
    private LocalDateTime createTime;

    @ApiModelProperty("Expiration time")
    @TableField("expire_time")
    private LocalDateTime expireTime;

    @ApiModelProperty("Remarks")
    @TableField("remark")
    private String remark;


}
  • Create locking and unlocking methods for app locks

– implementation code

package com.ht.atp.plat.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ht.atp.plat.entity.AppLock;
import com.ht.atp.plat.mapper.AppLockMapper;
import com.ht.atp.plat.service.AppLockService;
import org.springframework.stereotype.Service;


@Service
public class AppLockServiceImpl extends ServiceImpl<AppLockMapper, AppLock> implements AppLockService {

    @Override
    public void lock(AppLock appLock) {
        try {
            this.baseMapper.insert(appLock);
        } catch (Exception ex) {
            // Failed to acquire the lock, try again
            try {
                Thread.sleep(50);
                this.lock(appLock);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

    }

    @Override
    public void unlock(AppLock appLock) {
        this.baseMapper.deleteById(appLock.getId());
    }
}

– Lock by spin

– Unlock and delete the lock directly

  • Business code to implement inventory deduction

– implementation code

 @Autowired
    private AppLockService appLockService;

    @Override
    public void checkAndReduceStock() {
        AppLock appLock = new AppLock();
        appLock.setLockName("lock");
        appLock.setClassName(this.getClass().getName());
        appLock.setMethodName("checkAndReduceStock");
        appLock.setServerIp("127.0.0.1");
        appLock.setThreadName(Thread.currentThread().getName());
        appLock.setCreateTime(LocalDateTime.now());
        appLock.setExpireTime(LocalDateTime.now().plusSeconds(30));
        appLock.setRemark("This is a test lock!");
        try {
            // lock
            appLockService.lock(appLock);

            // Query inventory
            WmsStock wmsStock = baseMapper.selectById(1L);
            // Verify that the inventory is greater than 0 and then deduct the inventory
            if (wmsStock != null & amp; & amp; wmsStock.getStockQuantity() > 0) {
                wmsStock.setStockQuantity(wmsStock.getStockQuantity() - 1);
                baseMapper.updateById(wmsStock);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Unlock
            appLockService.unlock(appLock);
        }


    }

  • Start the 7000, 7001, 7002 servers and set the inventory to 10000

  • Start the jmeter stress test and inventory deduction interface

  • Final stress test results

– Inventory deduction is: 0

– Stress test results: The average response time is 3461ms, and the throughput per second is 27. Compared with redis and zookeeper locks, the performance is quite different.

  • Some summary about mysql distributed lock

– There is a single point of failure in the mysql database. Once the database goes down, the application lock will be unavailable. High availability can be ensured by building a master and backup database.

– Automatic lock release is not implemented: you can scan application lock data through scheduled tasks, and if the expiration time is less than the current time, delete the lock; or delete the lock through message middleware

– The lock is non-reentrant: You can use host information, port number, thread number and other information to determine whether it is the same thread. If it is the same thread, the lock is reentrant.

– Mysql database distributed lock performance is average, not as good as redis and zookeeper distributed locks, and easy to use

Conclusion

The content about using mysql distributed locks to solve the “oversold” problem ends here, and will be discussed later. . . . . .