请教一个 Mysql 并发查询的问题（与可重复读相关？）

（此问题出现场景为 mysql5.7.31 隔离级别为可重复读）
在做审核业务流程中自定义了两张表，a 和 a_detail 。主要需求是子表所有记录都审核通过了，那么就去修改主表记录为审核通过。但有可能会遇到最后两条记录同时审核成功，他们去查询当前子表审核记录的时候有可能对方还没提交，所以最终会修改主表失败。为了解决这个问题，就直接想到了添加索引后，利用 mysql 行锁加在主表的记录上，获取锁之后再去 count 。但是在操作过程中在获取行锁之前添加了一句查询子表的 sql 导致结果不正确。请教下这是为什么？或者说请教下此业务有其他合适的解决方案吗?

CREATE TABLE `a` (
`uid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '审核记录主表 guid',
`approval_status` int(11) NULL DEFAULT 1 COMMENT '审核状态(1:审核中,2:通过)',
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_guid`(`uid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '审核记录主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('a1', 1, 1);
INSERT INTO `a` VALUES ('a2', 1, 2);

CREATE TABLE `a_detail` (
`approval_status` int(11) NULL DEFAULT 1 COMMENT '审核状态(1:审核中;2:通过)',
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
`auid` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_aid`(`auid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 74 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '审核记录明细表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of a_detail
-- ----------------------------
INSERT INTO `a_detail` VALUES (1, 1, 'a1');
INSERT INTO `a_detail` VALUES (1, 2, 'a1');
INSERT INTO `a_detail` VALUES (1, 3, 'a2');
INSERT INTO `a_detail` VALUES (1, 4, 'a2');

会话一和会话二同时执行
SELECT @@GLOBAL.TX_ISOLATION;

begin;

-- 查询字表明细记录(这部如果去除整个流程正常)-
SELECT * FROM a_detail WHERE id = 1

-- 根据索引利用行锁锁定主表的记录--
SELECT * FROM `a` where uid='a1' for update;

-- 跟新字表状态为 2 审核通过--
UPDATE a_detail set approval_status = 2 WHERE id = 1 and approval_status = 1;

-- 查看子表是不是都已经审核通过了--
select count(0) from a_detail WHERE auid = 'a1' and approval_status!=2;
-- 接下来如果 count(0) 数量是 0 代表所有子记录都通过，则去修改主表状态 update a set approval_status=2 where uid='a1' --

commit;

SELECT @@GLOBAL.TX_ISOLATION;

begin;

-- 查询字表明细记录(这部如果去除整个流程正常)-
SELECT * FROM a_detail WHERE id = 2

-- 根据索引利用行锁锁定主表的记录--
SELECT * FROM `a` where uid='a1' for update;

-- 跟新字表状态为 2 审核通过--
UPDATE a_detail set approval_status = 2 WHERE id = 2 and approval_status = 1;

-- 查看子表是不是都已经审核通过了--
select count(0) from a_detail WHERE auid = 'a1' and approval_status!=2;
-- 接下来如果 count(0) 数量是 0 代表所有子记录都通过，则去修改主表状态 update a set approval_status=2 where uid='a1' --

commit;

rqxiao

158 天前

@wengyanbin rr 级别在在事务开启后第一次发生快照读的时候生成 readview 而非事务开启时生成 readview 。所以后获取到行锁的事务在 update 自己的记录后，count （ 0 ）就是等于 0 。
如果按照你的说法，在 rr 级别，那第二个事务也永远不会 count=0 了？
因为根据 mvcc ，他确实会读取到事务一提交完的数据，你可以自己测试下。count 的结果和事务开始的时间无关，和第一次进行快照读有关
而且幻读的定义不是事务中，同时进行两次查询发现 count 数量不一致，这个场景一个事务里没有 count 多次啊？

如果理解有误请指出

rqxiao

145 天前

@wenxueywx
根据本人目前掌握理解的水平，如果主表不加锁， a 事务和 b 事务都分别各自按照下面 sql 执行执行

====sessionA
begin;
UPDATE a_detail set approval_status = 2 WHERE id = 1 and approval_status = 1;
select count(0) from a_detail WHERE auid = 'a1' and approval_status!=2 lock in share mode ；
commit;
=====sessionB
begin;
UPDATE a_detail set approval_status = 2 WHERE id = 2 and approval_status = 1;
select count(0) from a_detail WHERE auid = 'a1' and approval_status!=2 lock in share mode ；
commit;
各自执行 update where id=都会加上各自 id 的行锁，
但各自执行到 select count()都要去根据 auid 这个非唯一索引进行等值查询，都会触发各自的间隙锁，但是又与对方事务的行锁冲突，造成死锁