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

[rqxiao]

（此问题出现场景为 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;

补充下需求 审核流程有可能会有多阶段 一阶段审核子表记录全部成功 触发二阶段审核子表记录，直到最终阶段审核子表记录全部成功修改主表记录状态

[RainCats]

浅薄看了点书，for update 用的是当前读，会刷新当前事务中读取到的数据版本。
要不试试搞个分布式锁去确保同一时间只有一个可以操作到。

[pkoukk]

从你的业务描述上看，我没看到锁主表的必要性...
既然两个都是成功，目标都是改为审核通过，那为什么要锁呢

[skaly]

尽量不要用存储过程/触发器什么的，出现问题调试起来非常麻烦


鉴于你这个问题，可以考虑在主表里面 加两字段 count ，u_count 。
每次更新 u_count 的时候，使用 u_count=u_count+1
然后再判断 u_count 是否=count ，来决定是否 审核通过

[1018ji]

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

这个东西会影响后面的查询吧，我估计表现就是这样的

-- 查看子表是不是都已经审核通过了--
select count(0) from a_detail WHERE auid = 'a1' and approval_status!=2;


---------------------------------------------------------------------------------------


还是考虑 3 的做法，但是有个问题是如果代码不够牛逼，很容易错乱也就是说导致 u_count 少了。
你这个事务太大了，这个事务就是个灾难

[1018ji]

https://www.jianshu.com/p/eb3f56565b42 补充个文章，自己测试对不对吧

[MoYi123]

难道不是每次子审核通过就 直接跑这 2 句就行? 都不用在一个事务里.

UPDATE a_detail set approval_status = 2 WHERE id = 2 and approval_status = 1;
UPDATE a set approval_status=(select count(0)==0 from a_detail WHERE auid = 'a1' and approval_status!=2) where uid='a1'

[rqxiao]

@pkoukk 有可能会有驳回 审核不通过的记录，也有可能全是通过。全是通过要计算最终是否审核通过

[pkoukk]

@rqxiao 驳回和不通过那不是子表的事情，总表不是通过子表记录计算出来的么？
两个不同的线程对子表记录的计算结果应该一致啊，还能不一致？

[rqxiao]

@pkoukk 最后两个审核通过的他们在事务里都没有提交的时候，不是 count 自己事务内 当前内已提交事务的数量吗，有可能都 2 个线程都是 count 出 9 个 如果总共审核通过的是 10 个的话

[Dream95]

事件驱动，子表审核通过触发审核事件，处理审核事件时再去检查全部任务是否通过审核

[bsg1992]

这个不算问题吧， 审批触发事件后，查询一下审批记录，满足数量更新主表的状态值就好了啊

[LiaoMatt]

我觉得通过 MQ 来实现会更好, 或者你把事务隔离级别设置为读已提交是不是就可以了

[rqxiao]

@bsg1992 意思是 修改字表和修改主表在两个事物里吗

[wengyanbin]

@rqxiao 难道是在事务开始时候的 select * from a_detail where id =这一句影响了后面的 count ？就算去掉开始的语句我也觉得会有幻读的问题的，两个事务里面的 count 应该都是快照读才对。

[rqxiao]

@wengyanbin 并发时，每个会话开启事务，获取到行锁之后，进行一次快照读。只要能获取到行锁，说明上个事务已经提交了。所以并发情况下，达到了串行执行业务。获取到行锁一定能查询当前准确的数据。为什么说会有幻读？
目前来看 select * from a_detail where id =这一句是影响了后面的 count 。就算我替换成 select * from student a 。与本业务完全不相关的表查询，也会有问题。不是很理解为什么
碍于本人现在的认知水平，理解有可能也不是对的。

[rqxiao]

@rqxiao mysql 为 rr 级别

[long952]

把第一个查询语句放在 for update 后面试试，rr 级别第一次查询生成一个 ReadView ，解决幻读问题，以后每次读取还是这个 readview ，数据还是旧的

[vczyh]

@long952 应该是这样

[rqxiao]

@long952 我以前看网上资料的，rr 级别第一次查询生成一个 ReadView 。都是用 select 一条记录作为演示。
我一直以为是 readview 是跟记录绑定的。不同的记录会绑定不同的 readvie ，现在这个现象感觉和不是记录级别，感觉是整个表级别。

[wengyanbin]

@rqxiao 问题应该还是在 count 的地方，产生了一个快照读。但我个人分析还是觉得无论前面那一句查询加不加都会是快照读，也就是无论怎样都会有幻读的问题才对。不加 select * from a_detail where id =这一句事务反而能够正常跑是我不能理解。现在不知道从哪里分析问题了

[rqxiao]

@wengyanbin 你指的幻读问题具体是指什么问题

[wengyanbin]

@rqxiao 在这里事务一更新了 detail 然后去 count ，这个时候 count(0)=1 ，所以事务一没有更新表 a ；事务二跟事务一是并发的，count(0)也是等于 1 ，也没有更新表 a 。原因就是因为 count(0)这个地方是 ReadView ，两个事务读出来 count(0)都不等于 0.

[rqxiao]

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

如果理解有误请指出

[rqxiao]

@rqxiao 只要能获取到行锁，说明上个事务已经提交了

[rqxiao]

@wengyanbin 而且只要能获取到行锁，说明上个事务已经提交了

[wengyanbin]

@rqxiao 没注意到行锁是同一个对象，能获取到行锁确实证明上个已经提交。我说的幻读的那种场景是例如会议室预定，两个人同时预定同个时间段的会议室，通过 select count()来判断是否有预定，在 rr 级别下，会出现两个事务并发进行，都先判断无人预定，然后就会产生写冲突。

[wenxueywx]

rr 等级的 readview 是事务开始时创建，事务在整个生命周期内使用相同的 readview 。我理解题主的意思是 A 、B 两个事务并行，A 事务已经提交的数据，B 事务是通过 readview 是读不到的。查询子表状态时采用当前读可以解决：select count(0) from a_detail WHERE auid = 'a1' and approval_status!=2 lock in share mode ；
其次，主表没有加锁的必要

[keepme]

1. 那是因为读视图是在 SELECT * FROM a_detail WHERE id = 2 和 SELECT * FROM a_detail WHERE id = 1 的时候创建的，后续的更新操作不会更新读视图，所以查不到对方事务数据的更改
2. 如果没有上面两个 sql 语句，读视图会在 SELECT * FROM `a` where uid='a1' for update;这条 sql 的时候创建读视图，这样只有一个事务执行完毕，另一个事务才会创建读视图，所以不存在 count 不到的问题。

[keepme]

@wenxueywx 正确的说法应该是事务的第一条 sql 语句执行时创建的

[rqxiao]

@wenxueywx
rr 等级的 readview 是事务开始时创建 .这句话是错的

[rqxiao]

@wenxueywx A 、B 两个事务并行，A 事务已经提交的数据，B 事务是通过 readview 是读不到的。
这句话也是错的 ，rr 级别根据 mvcc 生成 readview ，那按照你说的话，在 rr 级别只要 b 事务和 a 事务同时开启，b 事务就永远不可能读到 a 事务已提交的数据吗，但实际不是的

[rqxiao]

@wenxueywx 原先就是设想 在 rr 级别为了 让 count 操作串行执而利用行锁，并且让 count 操作在获取行锁立马执行，确保能读到已经提交的数据。所以说本来的意思想 A 事务已经提交的数据，B 事务是通过 readview 能读到

[wenxueywx]

@rqxiao 1 、“rr 等级的 readview 是事务开始时创建”确实不对，准确地说，rr 等级的 readview 创建时机是事务中首次执行 sql 时，此事务中的快照读都是基于该 readview 。
2 、A 、B 两个并行的事务，A 可以通过当前读获取 B 事务已经提交的数据，不能通过快照读读到。
3 、 加锁和 count 读是两个操作，count 依然是读的快照，不会因为你加锁而读最新数据，你需要 count 进行当前读

[rqxiao]

@wenxueywx 你的意思是在每个事务里 ，先 update 各自 id 的状态，后执行 select count(*) from a_detail where approval_status!=3 and auid='a1' lock in share mode;
吗 ，实测下来 select count(*) lock in share mode;会阻塞，死锁。 是我理解执行有问题吗

[rqxiao]

@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 这个非唯一索引进行等值查询，都会触发各自的间隙锁，但是又与对方事务的行锁冲突，造成死锁

[rqxiao]

@wenxueywx 事务中 加锁之后 进行了快照读，在目前仅有的业务下，在什么情况会出现读不到最新数据？

[wenxueywx]

@rqxiao 确实会阻塞

[wenxueywx]

6 楼说的对，子表更新与统计子表更新成功的条数后更新主表 没必要在一个事务

[wengyanbin]

重新看过问题，我有一个想法。题主你原来没问题的写法，两个事务同时开启，在第一句 SQL 的时候发生了锁竞争，有一个事务阻塞没有产生快照，直到另一个事务完成才获得锁并产生快照。这时候的快照已经是最新的，所以最后按照你的设计执行成功。在 for update 加上的那一句查询，使得两个事务都同时获得各自的快照，后面的操作其实都是在快照上面执行的。