【Java Web】行锁for update

【Java Web】行锁for update

begin;
select * from system_role where id_ = '1626887644786495491' for update;
update system_role set version_ = 0 where id_ = '1626887644786495491';
commit;

InnoDB默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

spring boot测试

mapper

    <select id="selectTest" resultType="ycx.system.vo.RoleVo">
        select * from system_role where id_ = '1626887644786495491' for update
    </select>

RoleVo selectTest();

service

    RoleVo selectTest1(String from);
    RoleVo selectTest2(String from);

    @Transactional(rollbackFor = Exception.class)
    @Override
    public RoleVo selectTest1(String from) {
        RoleVo r = baseMapper.selectTest();
        System.out.println(from + " 检索完成: " + DatePattern.NORM_DATETIME_FORMAT.format(new Date()));
        try {
            TimeUnit.SECONDS.sleep(30);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println(from);
        return r;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public RoleVo selectTest2(String from) {
        RoleVo r = baseMapper.selectTest();
        System.out.println(from + " 检索完成: " + DatePattern.NORM_DATETIME_FORMAT.format(new Date()));
        try {
            TimeUnit.SECONDS.sleep(30);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println(from);
        return r;
    }

controller

    @RequestMapping("/selectTest1")
    public Result<RoleVo> selectTest1(RoleVo roleVo) {
        return Result.ok(roleService.selectTest1("111"));
    }
    @RequestMapping("/selectTest2")
    public Result<RoleVo> selectTest2(RoleVo roleVo) {
        return Result.ok(roleService.selectTest1("222"));
    }

 测试结果：

先执行 selectTest2 接着执行 selectTest1 日志：

select * from system_role where id_ = '1626887644786495491' for update
222 检索完成: 2023-08-09 21:55:55
222
select * from system_role where id_ = '1626887644786495491' for update
111 检索完成: 2023-08-09 21:56:25
111

结论：再2没有执行完成前1一致在等待获取锁，一定要传主键，避免锁表