V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
Features
V2EX  ›  问与答

为什么这段 SQL 代码在 Mysql 失效了, Mysql 不是阻塞的吗?

  •  1
     
  •   Features · 2022-03-31 11:04:04 +08:00 · 4164 次点击
    这是一个创建于 996 天前的主题,其中的信息可能已经有所发展或是发生改变。
    判断表中是否有记录:
    SELECT * FROM `order` WHERE `member_id` = 39 AND `sn` = 20220331783;
    无则插入
    INSERT INTO `order` SET `sn` = 20220331783 , `member_id` = 39;
    有则更新
    UPDATE `order` SET `sum` = `sum`+1;

    实际的结果是数据库中有多条相同的 member_id 和 sn
    为什么会这样呢?
    Mysql 不是阻塞的吗?
    46 条回复    2022-04-04 01:42:37 +08:00
    zjj19950716
        1
    zjj19950716  
       2022-03-31 11:17:26 +08:00   ❤️ 5
    看到沙发还在,我准备抢一楼,有人也准备抢一楼,那么谁是一楼呢
    xzh20121116g
        2
    xzh20121116g  
       2022-03-31 11:19:37 +08:00   ❤️ 1
    90%的概率:代码没有考虑并发,跟 mysql 没关系
    sun1991
        3
    sun1991  
       2022-03-31 11:21:21 +08:00
    MySQL 不是阻塞的. (陈述句)
    任何上规模数据库都不可能简简单单就阻塞.
    livenux
        4
    livenux  
       2022-03-31 11:21:44 +08:00
    这个用 upsert 不就好了?
    gtchan13579
        5
    gtchan13579  
       2022-03-31 11:26:59 +08:00
    直接用 replace into
    Feiex
        6
    Feiex  
       2022-03-31 11:27:47 +08:00
    开启事务了吗,什么级别?
    Features
        7
    Features  
    OP
       2022-03-31 11:27:59 +08:00
    @xzh20121116g
    @sun1991
    测试环境是单库,发出多个请求就会导致数据重复
    假设有 1,2,3 个请求
    请求 1 返回成功结果,这时候服务端应该把数据插入表中了
    请求 2 和请求 3 应该能 SELECT 到正确的数据啊?
    Features
        8
    Features  
    OP
       2022-03-31 11:31:33 +08:00
    @Feiex 没设置事务
    Jooooooooo
        9
    Jooooooooo  
       2022-03-31 11:31:46 +08:00
    @Features 请求 2 等待请求 1 成功再做吗?
    turan12
        10
    turan12  
       2022-03-31 11:32:01 +08:00
    使用 select for update 不知能否解决问题?
    Features
        11
    Features  
    OP
       2022-03-31 11:34:36 +08:00
    @Jooooooooo 好像确实不是的
    Ritter
        12
    Ritter  
       2022-03-31 11:34:42 +08:00
    两个请求同时进来 同时查到没有记录呗
    Feiex
        13
    Feiex  
       2022-03-31 11:36:34 +08:00
    @Features mysql 执行语句默认不是阻塞的,不然数据库做不了并发;
    #7 典型的事务隔离问题;

    语句开启需要事务,rc 级别即可:
    begin transaction;
    //for update 注意是关键!!!
    SELECT * FROM `order` WHERE `member_id` = 39 AND `sn` = 20220331783 for update;
    INSERT INTO `order` SET `sn` = 20220331783 , `member_id` = 39;
    UPDATE `order` SET `sum` = `sum`+1;
    commit;
    Features
        14
    Features  
    OP
       2022-03-31 11:36:53 +08:00
    @Ritter 是的,因为 Mysql 是远程的,把这个问题暴露出来了,以前没注意到。。。
    Features
        15
    Features  
    OP
       2022-03-31 11:37:29 +08:00
    @Feiex 感谢
    Features
        16
    Features  
    OP
       2022-03-31 11:39:28 +08:00
    @turan12 应该不行,因为多个请求并发,可能前一个请求的数据还 没到达服务器 /存储成功 /未更新
    应该要从其他地方解决问题
    wowo243
        17
    wowo243  
       2022-03-31 11:45:31 +08:00
    接口幂等问题?加个锁或者标记位,比如在 redis 中存一个 key 为 member_id+sn 的数据,接口先访问 redis 看 key 是否存在,不存在则设置 key 并执行逻辑,执行完逻辑后删除 key ;存在则等待标记位清除,再设置 key 并执行逻辑。
    micean
        18
    micean  
       2022-03-31 11:51:15 +08:00   ❤️ 3
    sn 和 member_id 建唯一索引

    然后 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c = c+1
    Pythoner666666
        19
    Pythoner666666  
       2022-03-31 11:52:38 +08:00
    考虑下是不是异步 + 并发的问题
    JeromeCui
        20
    JeromeCui  
       2022-03-31 11:52:47 +08:00
    @micean 这才是正确的方式
    Features
        21
    Features  
    OP
       2022-03-31 11:54:14 +08:00
    最终解决办法:
    在缓存中先预存 key = sn+member_id 的组合,
    同时查询数据库 sn+member_id 记录条数,
    如果缓存 key 的值不为空,但记录为 0 ,则返回提示: 您的手速太快了

    想到更优雅的,但没时间做的解决办法:
    把请求整合到一个队列中,稳定进出,应该也能解决
    Features
        22
    Features  
    OP
       2022-03-31 11:54:44 +08:00
    @wowo243 是的,就是这个问题
    Features
        23
    Features  
    OP
       2022-03-31 12:33:16 +08:00
    后续:
    并发太高时,甚至连缓存都来不及生成
    根据 @micean 的方法建立 unique key
    在业务中捕获 1062 Duplicate entry 错误
    调整业务代码,最终成功
    Danswerme
        24
    Danswerme  
       2022-03-31 13:30:09 +08:00
    学习到了,收藏下。
    koloonps
        25
    koloonps  
       2022-03-31 13:31:35 +08:00
    @Features 什么叫并发太高时,甚至连缓存都来不及生成?
    CEBBCAT
        26
    CEBBCAT  
       2022-03-31 14:18:06 +08:00   ❤️ 2
    你这不是不会用数据库,是根本还没学过。找一本适合初学者的书吧

    关于并发,要理解程序的并发模型才能比较好地写出健壮的代码,不然就会闹出先 SELECT 再 INSERT 的笑话

    看到你是前端转后端?加油!
    CEBBCAT
        27
    CEBBCAT  
       2022-03-31 14:19:27 +08:00
    看到楼上问的“什么叫并发太高”,忍不住再发一条,你还是再系统地学学 Redis 在并发方面的用法吧,别是又先 GET 再 SET ,正确的用法是 SETNX
    lawler
        28
    lawler  
       2022-03-31 14:32:00 +08:00   ❤️ 4
    看到 16 楼才出现正确答案,着实让我震惊。
    yor1g
        29
    yor1g  
       2022-03-31 14:38:05 +08:00
    要加数据库唯一索引
    查-> 没有 -> 插入
    查-> 有 -> 更新
    查-> 没有 -> 插入 -> 唯一提示失败 -> 再查 -> 更新
    或者用 redis 锁
    lawler
        30
    lawler  
       2022-03-31 15:48:25 +08:00   ❤️ 1
    @lawler #28 订正,是 18 楼。😀
    h82258652
        32
    h82258652  
       2022-03-31 16:38:07 +08:00
    如果业务上还有软删除这种的话,那就没法加唯一了
    还是搞个锁吧
    Features
        33
    Features  
    OP
       2022-03-31 17:01:20 +08:00
    @CEBBCAT 我是在学习后端,摸鱼时间偷偷学习下
    Features
        34
    Features  
    OP
       2022-03-31 17:06:08 +08:00
    @CEBBCAT 请教下,用 rocketmq 能否完美解决这种问题?
    回想安卓中涉及到这种问题,也是使用队列处理的
    br00k
        35
    br00k  
       2022-03-31 17:22:03 +08:00
    这种避免的最佳方式就是增加唯一索引,从源头杜绝产生异常数据的可能。
    wxdiy
        36
    wxdiy  
       2022-03-31 17:37:55 +08:00
    @br00k 这儿才正确哇,用锁又引入了新的问题了
    gamexg
        37
    gamexg  
       2022-03-31 17:46:08 +08:00   ❤️ 1
    这种需求我会加唯一索引。


    @h82258652 #32 我这边是把软删除的时间字段也加到唯一索引里面,达到唯一的目的。
    LinsVert
        38
    LinsVert  
       2022-03-31 17:56:46 +08:00
    有时候可能不是所谓的“并发“,可能是前端没有做按钮拦截,可能出现双击操作,从而请求会几乎同一时间到达。
    Features
        39
    Features  
    OP
       2022-03-31 17:58:03 +08:00
    后续的后续:
    因为 sn 是可预期的,所以做一个 task ,提前把用户的数据加热到数据表中
    此业务环境下,不再对数据进行 SELECT 和 INSERT 操作,只要 UPDATE 就可以了
    性能瞬间提升了很多
    Features
        40
    Features  
    OP
       2022-03-31 17:58:57 +08:00
    @LinsVert 这就是模拟并发啊,这种不可能要求前端做的
    我自己就是前端
    zw1one
        41
    zw1one  
       2022-04-01 09:26:57 +08:00
    SELECT * FROM `order` WHERE `member_id` = 39 AND `sn` = 20220331783;
    ->
    SELECT * FROM `order` WHERE `member_id` = 39 AND `sn` = 20220331783 for update;
    wangxin13g
        42
    wangxin13g  
       2022-04-01 10:18:42 +08:00
    唯一索引+事务
    你这几条 sql 不是原子性的出现几条数据一样不是很正常么
    jowan
        43
    jowan  
       2022-04-01 10:31:23 +08:00
    这就是常见的并发问题 如果不考虑性能的话用 18#的方案可以解决 也就是无则插入有则更新
    问这个问题说明你平时没有做过高并发且需要保证原子操作的功能
    但绝大多数需要考虑性能问题的情况下 可以用分布式锁 比如 redlock
    还有不管你用什么方式解决了重复插入的问题 但依然没解决重复更新的问题
    比如这个场景可能是前端没有做节流限制或者客户端网络太差导致重复提交
    LinsVert
        44
    LinsVert  
       2022-04-01 17:04:07 +08:00
    @Features 并发处理肯定是后端处理,我只是举例实际应用场景中能触发的一种可能。
    lyy16384
        45
    lyy16384  
       2022-04-01 18:29:01 +08:00
    @micean #18 ON DUPLICATE KEY UPDATE 有个问题就是即使 update 也会消耗一个自增主键,在某些 update 特别多的情况下最好还是先 select 判断一下
    CEBBCAT
        46
    CEBBCAT  
       2022-04-04 01:42:37 +08:00
    @Features RocketMQ 是消息队列,你可以用它解决,这取决于你的业务设计。当你决定要使用 MQ 来 handle 并发的时候,新的问题随之产生:用户操作是否有延时?消息消费是否原子?如果某条消息消费到一半,机器断电了怎么办?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2562 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 11:28 · PVG 19:28 · LAX 03:28 · JFK 06:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.