V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
rqxiao
V2EX  ›  MySQL

请教一个 Mysql 并发查询的问题(与可重复读相关?)

  •  
  •   rqxiao · 154 天前 · 2038 次点击
    这是一个创建于 154 天前的主题,其中的信息可能已经有所发展或是发生改变。
    (此问题出现场景为 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;
    第 1 条附言  ·  153 天前
    补充下需求 审核流程有可能会有多阶段 一阶段审核子表记录全部成功 触发二阶段审核子表记录,直到最终阶段审核子表记录全部成功修改主表记录状态
    39 条回复    2024-08-03 20:14:41 +08:00
    RainCats
        1
    RainCats  
       154 天前   ❤️ 1
    浅薄看了点书,for update 用的是当前读,会刷新当前事务中读取到的数据版本。
    要不试试搞个分布式锁去确保同一时间只有一个可以操作到。
    pkoukk
        2
    pkoukk  
       154 天前
    从你的业务描述上看,我没看到锁主表的必要性...
    既然两个都是成功,目标都是改为审核通过,那为什么要锁呢
    skaly
        3
    skaly  
       154 天前   ❤️ 1
    尽量不要用存储过程/触发器什么的,出现问题调试起来非常麻烦


    鉴于你这个问题,可以考虑在主表里面 加两字段 count ,u_count 。
    每次更新 u_count 的时候,使用 u_count=u_count+1
    然后再判断 u_count 是否=count ,来决定是否 审核通过
    1018ji
        4
    1018ji  
       154 天前   ❤️ 1
    -- 查询字表明细记录(这部如果去除整个流程正常)-
    SELECT * FROM a_detail WHERE id = 1

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

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


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


    还是考虑 3 的做法,但是有个问题是如果代码不够牛逼,很容易错乱也就是说导致 u_count 少了。
    你这个事务太大了,这个事务就是个灾难
    1018ji
        5
    1018ji  
       154 天前
    https://www.jianshu.com/p/eb3f56565b42 补充个文章,自己测试对不对吧
    MoYi123
        6
    MoYi123  
       154 天前
    难道不是每次子审核通过就 直接跑这 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
        7
    rqxiao  
    OP
       153 天前
    @pkoukk 有可能会有驳回 审核不通过的记录,也有可能全是通过。全是通过要计算最终是否审核通过
    pkoukk
        8
    pkoukk  
       153 天前
    @rqxiao 驳回和不通过那不是子表的事情,总表不是通过子表记录计算出来的么?
    两个不同的线程对子表记录的计算结果应该一致啊,还能不一致?
    rqxiao
        9
    rqxiao  
    OP
       153 天前
    @pkoukk 最后两个审核通过的他们在事务里都没有提交的时候,不是 count 自己事务内 当前内已提交事务的数量吗,有可能都 2 个线程都是 count 出 9 个 如果总共审核通过的是 10 个的话
    Dream95
        10
    Dream95  
       153 天前   ❤️ 1
    事件驱动,子表审核通过触发审核事件,处理审核事件时再去检查全部任务是否通过审核
    bsg1992
        11
    bsg1992  
       153 天前
    这个不算问题吧, 审批触发事件后,查询一下审批记录,满足数量更新主表的状态值就好了啊
    LiaoMatt
        12
    LiaoMatt  
       153 天前
    我觉得通过 MQ 来实现会更好, 或者你把事务隔离级别设置为读已提交是不是就可以了
    rqxiao
        13
    rqxiao  
    OP
       153 天前
    @bsg1992 意思是 修改字表和修改主表在两个事物里吗
    wengyanbin
        14
    wengyanbin  
       153 天前
    @rqxiao 难道是在事务开始时候的 select * from a_detail where id =这一句影响了后面的 count ?就算去掉开始的语句我也觉得会有幻读的问题的,两个事务里面的 count 应该都是快照读才对。
    rqxiao
        15
    rqxiao  
    OP
       153 天前
    @wengyanbin 并发时,每个会话开启事务,获取到行锁之后,进行一次快照读。只要能获取到行锁,说明上个事务已经提交了。所以并发情况下,达到了串行执行业务。获取到行锁一定能查询当前准确的数据。为什么说会有幻读?
    目前来看 select * from a_detail where id =这一句是影响了后面的 count 。就算我替换成 select * from student a 。与本业务完全不相关的表查询,也会有问题。不是很理解为什么
    碍于本人现在的认知水平,理解有可能也不是对的。
    rqxiao
        16
    rqxiao  
    OP
       153 天前
    @rqxiao mysql 为 rr 级别
    long952
        17
    long952  
       153 天前
    把第一个查询语句放在 for update 后面试试,rr 级别第一次查询生成一个 ReadView ,解决幻读问题,以后每次读取还是这个 readview ,数据还是旧的
    vczyh
        18
    vczyh  
       153 天前
    @long952 应该是这样
    rqxiao
        19
    rqxiao  
    OP
       153 天前
    @long952 我以前看网上资料的,rr 级别第一次查询生成一个 ReadView 。都是用 select 一条记录作为演示。
    我一直以为是 readview 是跟记录绑定的。不同的记录会绑定不同的 readvie ,现在这个现象感觉和不是记录级别,感觉是整个表级别。
    wengyanbin
        20
    wengyanbin  
       153 天前
    @rqxiao 问题应该还是在 count 的地方,产生了一个快照读。但我个人分析还是觉得无论前面那一句查询加不加都会是快照读,也就是无论怎样都会有幻读的问题才对。不加 select * from a_detail where id =这一句事务反而能够正常跑是我不能理解。现在不知道从哪里分析问题了
    rqxiao
        21
    rqxiao  
    OP
       152 天前
    @wengyanbin 你指的幻读问题具体是指什么问题
    wengyanbin
        22
    wengyanbin  
       152 天前
    @rqxiao 在这里事务一更新了 detail 然后去 count ,这个时候 count(0)=1 ,所以事务一没有更新表 a ;事务二跟事务一是并发的,count(0)也是等于 1 ,也没有更新表 a 。原因就是因为 count(0)这个地方是 ReadView ,两个事务读出来 count(0)都不等于 0.
    rqxiao
        23
    rqxiao  
    OP
       152 天前
    @wengyanbin rr 级别在 在事务开启后第一次发生快照读的时候生成 readview 而非 事务开启时生成 readview 。所以后获取到行锁的事务在 update 自己的记录后,count ( 0 )就是等于 0 。
    如果按照你的说法,在 rr 级别,那第二个事务也永远不会 count=0 了?
    因为根据 mvcc ,他确实会读取到事务一提交完的数据,你可以自己测试下。count 的结果和事务开始的时间无关,和第一次进行快照读有关
    而且幻读的定义不是事务中,同时进行两次 查询发现 count 数量不一致,这个场景一个事务里没有 count 多次啊?

    如果理解有误请指出
    rqxiao
        24
    rqxiao  
    OP
       152 天前
    @rqxiao 只要能获取到行锁,说明上个事务已经提交了
    rqxiao
        25
    rqxiao  
    OP
       152 天前
    @wengyanbin 而且只要能获取到行锁,说明上个事务已经提交了
    wengyanbin
        26
    wengyanbin  
       152 天前
    @rqxiao 没注意到行锁是同一个对象,能获取到行锁确实证明上个已经提交。我说的幻读的那种场景是例如会议室预定,两个人同时预定同个时间段的会议室,通过 select count()来判断是否有预定,在 rr 级别下,会出现两个事务并发进行,都先判断无人预定,然后就会产生写冲突。
    wenxueywx
        27
    wenxueywx  
       152 天前
    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
        28
    keepme  
       152 天前
    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
        29
    keepme  
       152 天前
    @wenxueywx 正确的说法应该是事务的第一条 sql 语句执行时创建的
    rqxiao
        30
    rqxiao  
    OP
       151 天前
    @wenxueywx
    rr 等级的 readview 是事务开始时创建 .这句话是错的
    rqxiao
        31
    rqxiao  
    OP
       151 天前
    @wenxueywx A 、B 两个事务并行,A 事务已经提交的数据,B 事务是通过 readview 是读不到的。
    这句话也是错的 ,rr 级别根据 mvcc 生成 readview ,那按照你说的话,在 rr 级别只要 b 事务和 a 事务同时开启,b 事务就永远不可能读到 a 事务已提交的数据吗,但实际不是的
    rqxiao
        32
    rqxiao  
    OP
       151 天前
    @wenxueywx 原先就是设想 在 rr 级别为了 让 count 操作串行执而利用行锁,并且让 count 操作在获取行锁立马执行,确保能读到已经提交的数据。所以说本来的意思想 A 事务已经提交的数据,B 事务是通过 readview 能读到
    wenxueywx
        33
    wenxueywx  
       151 天前
    @rqxiao 1 、“rr 等级的 readview 是事务开始时创建”确实不对,准确地说,rr 等级的 readview 创建时机是事务中首次执行 sql 时,此事务中的快照读都是基于该 readview 。
    2 、A 、B 两个并行的事务,A 可以通过当前读获取 B 事务已经提交的数据,不能通过快照读读到。
    3 、 加锁和 count 读是两个操作,count 依然是读的快照,不会因为你加锁而读最新数据,你需要 count 进行当前读
    rqxiao
        34
    rqxiao  
    OP
       140 天前
    @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
        35
    rqxiao  
    OP
       140 天前
    @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
        36
    rqxiao  
    OP
       140 天前
    @wenxueywx 事务中 加锁之后 进行了快照读,在目前仅有的业务下,在什么情况会出现读不到最新数据?
    wenxueywx
        37
    wenxueywx  
       140 天前
    @rqxiao 确实会阻塞
    wenxueywx
        38
    wenxueywx  
       140 天前
    6 楼说的对,子表更新与统计子表更新成功的条数后更新主表 没必要在一个事务
    wengyanbin
        39
    wengyanbin  
       93 天前 via Android
    重新看过问题,我有一个想法。题主你原来没问题的写法,两个事务同时开启,在第一句 SQL 的时候发生了锁竞争,有一个事务阻塞没有产生快照,直到另一个事务完成才获得锁并产生快照。这时候的快照已经是最新的,所以最后按照你的设计执行成功。在 for update 加上的那一句查询,使得两个事务都同时获得各自的快照,后面的操作其实都是在快照上面执行的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3700 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 10:33 · PVG 18:33 · LAX 02:33 · JFK 05:33
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.