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

小灰灰 2024-03-17 15:08 108阅读 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);
    
    
            /**
         * 悲观锁
         * select语句后加 for update
         * 
         *  //select ... for update可以在读取数据的同时将行锁住，阻止其他事务对这些行进行修改。
         */
        @Select(value = "select * from t_point where point_name =#{name} for update")
        Point selectByPointNameForUpdate(@Param("name") String name);
    
    
    
    
    
        @Update(value = "update t_point set point_name=#{pointName},point_type =#{pointType},version=version+1 where id =#{id}")
        int update(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);
    
    
    }

### web接口及业务层 ###

#### 业务层 ####

@Slf4j
    @Service
    public class PointService {
        
        @Resource
        private PointMapper pointMapper;
        
        @Transactional
        public void updateWithTimePessimistic(Point point, int time) throws InterruptedException {
        
            Point point1 = pointMapper.selectByPointNameForUpdate(point.getPointName());
            if (point1 == null) {
        
                return;
            }
            if (StrUtil.isNotBlank(point.getPointName())) {
        
                point1.setPointName(point.getPointName());
            }
            if (StrUtil.isNotBlank(point.getPointType())) {
        
                point1.setPointType(point.getPointType());
            }
    
            Thread.sleep(time * 1000L);
            pointMapper.update(point1);
    
    
        }
    
        @Transactional
        public void updatePessimistic(Point point)  {
        
            Point point1 = pointMapper.selectByPointNameForUpdate(point.getPointName());
            if (point1 == null) {
        
                return;
            }
            if (StrUtil.isNotBlank(point.getPointName())) {
        
                point1.setPointName(point.getPointName());
            }
            if (StrUtil.isNotBlank(point.getPointType())) {
        
                point1.setPointType(point.getPointType());
            }
    
            pointMapper.update(point1);
    
        }
        
    }

#### web接口层 ####

@RestController
    public class OptimisticTestController {
        
    
        @Autowired
        private PointService pointService;
        
        //悲观锁 在数据库的访问中使用，表现为：前一次请求没执行完，后面一个请求就一直在等待
        
        //悲观锁在并发环境下可能导致性能下降，因为它会阻止其他事务对资源的访问。
       
        //
        //先访问
        //http://localhost:8615/update/20
        //接着立马访问
        //http://localhost:8615/update
        //
        //会发现,第二个请求会等待第一个请求执行完毕才会执行
    
    
        @RequestMapping("/update/{time}")
        public String update1(@RequestBody Point point, @PathVariable("time") int time) throws InterruptedException {
        
            pointService.updateWithTimePessimistic(point, time);
            return "0000";
        }
    
        @RequestMapping("/update")
        public String update2(@RequestBody Point point){
        
            pointService.updatePessimistic(point);
            return "0000";
        }
       
        
    
    }