数据库乐观锁版本号

1. 目的
   同一个单据入库前经行版本号校验
2. 案例

-- auto-generated definition
create table fsales
(
    id         int auto_increment comment '主键'
        primary key,
    sales_date date           null,
    amount     decimal(10, 2) null,
    version    int            null comment '版本号'
);

package com.example.boottest.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDate;

/**
 * @TableName sales
 */
@TableName(value = "fsales")
@Data
public class Fsales implements Serializable {
    /**
     *
     */
    @TableField(value = "id")
    private Integer id;

    /**
     *
     */
    @TableField(value = "sales_date")
    private LocalDate salesDate;

    /**
     *
     */
    @TableField(value = "amount")
    private BigDecimal amount;

    /*  支持的数据类型只有:int,Integer,long,Long,Date,Timestamp,LocalDateTime
      整数类型下 newVersion = oldVersion + 1
      newVersion 会回写到 entity 中
      仅支持 updateById(id) 与 update(entity, wrapper) 方法
      在 update(entity, wrapper) 方法下, wrapper 不能复用!!!*/
    @TableField(value = "version")
    @Version
    private Integer version;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}


package com.example.boottest.controller;

import com.example.boottest.entity.Fsales;
import com.example.boottest.service.FsalesService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.time.LocalDate;

/**
 1. @author test
 2. @version 1.0
 3. @date 2023-12-22 14:31
 4. @Desc 每次新增1元钱
 */
@RestController
@RequestMapping("optimisticlock")
public class FsalesController {
    @Resource
    FsalesService fsalesService;

    @GetMapping("updateSalesUseCAS")
    public Boolean updateSalesUseCAS() {
        Fsales byId = fsalesService.getById(1);
        if (byId == null) {
            return null;
        }
        byId.setSalesDate(LocalDate.now().plusDays(1));
        byId.setAmount(byId.getAmount().add(BigDecimal.ONE));
//        UPDATE fsales SET sales_date='2023-12-23', amount='0.01', version=3 WHERE id=1 AND version=2
        Boolean flag = fsalesService.updateById(byId);
        return flag;
    }
}

3. 通过jmeter 1s 1000线程测试
   

4. 翻车
   

5. 结合官网问题排查增加乐观锁拦截器

package com.example.boottest.config;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author test
 * @version 1.0
 * @date 2023-12-22 15:13
 * @Desc
 */
@Configuration
public class MybatisplusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

6. 再次发送请求测试
   
   可以看到成功95次，95元钱