MySQL乐观锁并发控制实现案例

MySQL乐观锁并发控制实现案例

表设计

CREATE TABLE `t_point` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `point_name` varchar(255) NOT NULL,
  `point_type` varchar(255) NOT NULL,
  `version` int(11) NOT NULL DEFAULT '1',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4

mybatis各层设计

实体层

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Point implements Serializable {
    private static final long serialVersionUID = -6823782099362425089L;

    private Integer id;

    private String pointName;

    private String pointType;

    private Integer version;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

---

Mapper层

PointMapper.java代码

@Mapper
public interface PointMapper {


    @Select(value = "select * from t_point where id =#{id}")
    @Results(value = {
            @Result(column = "create_time",property = "createTime",javaType = LocalDateTime.class,jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_time",property = "updateTime",javaType = LocalDateTime.class,jdbcType = JdbcType.TIMESTAMP),

    })
    Point findById(@Param("id") Integer id);






    @Update(value = "update t_point set point_name=#{pointName},point_type =#{pointType},version=version+1 where id =#{id}")
    int update(Point point);


    /**
     * 乐观锁
     * 1.每次更新 version=version+1
     * 2.需要携带version字段进行更新 ,若version 不一致，则不会更新成功
     * 3.使用乐观锁更新前的select查询是不能带锁的
     *
     */
    @Update(value = "update t_point set point_name=#{pointName},point_type =#{pointType},version=version+1 where id =#{id} and version =#{version}")
    int updateWithVersion(Point point);


    @Select(value = "select * from t_point where point_name =#{name}")
    Point findByPointName(@Param("name") String name);


    @Insert(value = "insert into t_point (point_name ,point_type) values (#{pointName},#{pointType})")
    int save(Point point);



    int updateBatch(List<Point> points);




}

PointMapper.xml 代码

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.orm.dao.PointMapper">


<!--
foreach组装多条update语句，来形成分号;间隔的一条语句；
执行时需要jdbcUrl加上&allowMultiQueries=true
-->
    <update id="updateBatch" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update t_point
            <set>
                point_name = #{item.pointName},
                point_type = #{item.pointType},
                version=version+1
            </set>
            where id = #{item.id}
        </foreach>
    </update>
    
</mapper>

---

web接口及业务层

业务层

@Slf4j
@Service
public class PointService {
    @Resource
    private PointMapper pointMapper;
    
    @Transactional
    public void updateWithTimeOptimistic(Point point,int time) throws Exception {
        Point byPointName = pointMapper.findByPointName(point.getPointName());
        if (byPointName==null){
            return;
        }
        if (StrUtil.isNotBlank(point.getPointType())){
            byPointName.setPointType(point.getPointType());
        }
        if(StrUtil.isNotBlank(point.getPointName())){
            byPointName.setPointName(point.getPointName());
        }
        Thread.sleep(time*1000L);
        int ret = pointMapper.updateWithVersion(byPointName);
        if (ret<1){
            log.info("乐观更新-版本不一致-导致更新失败");
            throw new Exception("乐观更新-版本不一致-导致更新失败!!!");
        }

    }

    @Transactional
    public void updateOptimistic(Point point) throws Exception {
        Point byPointName = pointMapper.findByPointName(point.getPointName());
        if (byPointName==null){
            return;
        }
        if (StrUtil.isNotBlank(point.getPointType())){
            byPointName.setPointType(point.getPointType());
        }
        if(StrUtil.isNotBlank(point.getPointName())){
            byPointName.setPointName(point.getPointName());
        }
        int ret = pointMapper.updateWithVersion(byPointName);
        if (ret<1){
            log.info("乐观锁导致更新失败");
            throw new Exception("乐观锁导致更新失败!!!");
        }

    }
    
}

web接口层

@RestController
public class OptimisticTestController {

    @Autowired
    private PointService pointService;
    
    
     //乐观锁 在数据库的访问中使用，表现为：每次更新都会先查询version,
    // 执行update时会判定version是否相等，只有version相等才会更新成功；
    //
    //
    //
    //先访问
    //http://localhost:8615/opt/update/20
    //接着立马访问
    //http://localhost:8615/opt/update
    //
    //会发现,第二个请求会立刻完成。
    // 而第一个请求因为拿到的version已经是老的值所以最后更新失败，
    // （这里更新时返回值是0，即更新不成功，需要自己写抛出异常，
    // mybatis需要程序员自己判断并抛出异常，而JPA不需要，jpa的乐观锁可以抛异常）
    @RequestMapping("/opt/update/{time}")
    public String update1(@RequestBody Point point, @PathVariable("time") int time) throws Exception {
        pointService.updateWithTimeOptimistic(point, time);
        return "0000";
    }

    @RequestMapping("/opt/update")
    public String update2(@RequestBody Point point) throws Exception{
        pointService.updateOptimistic(point);
        return "0000";
    }
    

}