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);


        /**
     * 悲观锁
     * 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";
    }
   
    

}