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

亦凉 2024-03-17 15:08 25阅读 0赞

## 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">
    
    
    
        <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";
        }
        
    
    }