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

MySQL 实现这样的设计

  •  
  •   einvince · 2019-08-06 00:30:43 +08:00 · 2375 次点击
    这是一个创建于 1697 天前的主题,其中的信息可能已经有所发展或是发生改变。
    给定一批 id,需要在一张叫表 dupicate_check 表里检查 id 是否存在,不存在就插入,并且要知道新 id 的数量,现在如下步骤

    1. 查询 select id from dupicate_check id in ()
    2. 返回结果和给定的 id 作差集,得到新 id 的数量
    3. 把新 id 插入

    现在的问题是,如果并发跑会有问题,两个线程同时得到一个不存在的 id 会同时插入,而且得到新 id 的总数量会偏大,要解锁这个得加读锁,性能又差,有没有更好的设计
    14 条回复    2019-08-08 04:11:10 +08:00
    lzdhlsc
        1
    lzdhlsc  
       2019-08-06 02:34:49 +08:00
    应该在表的 schema 上限定 id 的唯一性, 从而让 mysql 判断是否唯一,而不是你的 code 去判断。这样你就可以只插入不查询,并且 swallow 你得到了 duplicated key error。
    msg7086
        2
    msg7086  
       2019-08-06 04:11:09 +08:00
    总是要在某个地方去锁的。
    就算是像楼上那样用唯一索引,本质上也要加锁来保证两个线程不会打架。
    如果你实在觉得 MySQL 上加锁太慢(应该不至于吧?),可以把重复检测放到 MySQL 之外来做。
    jjshare
        3
    jjshare  
       2019-08-06 05:16:30 +08:00
    @msg7086 唯一索引和加锁没有关系

    INSERT IGNORE 就搞定了~
    msg7086
        4
    msg7086  
       2019-08-06 07:29:34 +08:00
    @jjshare INSERT IGNORE 不要锁的吗?数据库的原子性就是靠锁来保证的呀。
    虽然我没读过 MySQL 源码,但是我猜这货是用了锁的。
    flowfire
        5
    flowfire  
       2019-08-06 08:33:53 +08:00 via iPhone
    duplicate 是不是拼错了……
    codingadog
        6
    codingadog  
       2019-08-06 08:58:36 +08:00 via Android
    集群用 Redis,单实例 JVM 缓存,加锁。
    线程 insert 操作前获取锁。
    数据库 ID 做唯一主键。
    直接 insert ignore 所有值,返回值即成功插入的数量。
    insert 完成后线程释放锁。
    jjshare
        7
    jjshare  
       2019-08-06 19:34:29 +08:00
    @msg7086 这个不需要读 mysql 源码,索引是数据结构层面的东西,原子性、锁机制是逻辑层面的东西,唯一索引本身就保证了原子性不需要额外的逻辑
    msg7086
        8
    msg7086  
       2019-08-07 05:27:59 +08:00
    @jjshare 不需要读 MySQL 源码是怎么知道 MySQL 没有用锁来实现唯一索引的呢。
    msg7086
        9
    msg7086  
       2019-08-07 05:30:40 +08:00
    顺便我查到的资料是 MySQL 不仅在唯一索引上用了锁,而且多线程同时对唯一索引做 INSERT IGNORE 甚至会让锁出现 DEADLOCK 的情况。如果你还是认为 MySQL 没有用锁实现唯一索引,那我也不知道怎么说下去了。反正这是我这边查到的信息。
    jjshare
        10
    jjshare  
       2019-08-08 02:14:01 +08:00
    @msg7086 数据结构可以解决的事情,为什么非要用锁这种逻辑操作上的东西来解决呢?
    你说的 read lock 只要读写操作同时存在的时候,都可能会出现,这个不稀奇啊。
    你查到的信息可以贴上来看一眼嘛,并且 mysql 的不同的存储引擎对这个的处理还不一样,换我写 mysql 的 INSERT IGNORE 的实现逻辑的话,我铁定不会用加个锁的,再说了,单独的插入操作加锁也没有意义。
    msg7086
        11
    msg7086  
       2019-08-08 02:22:39 +08:00
    @jjshare https://thushw.blogspot.com/2010/11/mysql-deadlocks-with-concurrent-inserts.html
    我看的是这篇。
    不知道是不是我理解错了。
    jjshare
        12
    jjshare  
       2019-08-08 02:40:45 +08:00
    @msg7086 哇,老哥也是个夜猫子啊,我先看看上面写的哈
    jjshare
        13
    jjshare  
       2019-08-08 02:55:36 +08:00
    @msg7086
    他那和题主的有差别

    TABLE vegetable (
    id bigint(10) NOT NULL auto_increment,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_name (name)
    ) ENGINE=InnoDB


    他表结构里面涉及两个 unique key,concurrent inserts 场景下,一个 commit 里面执行的操作比较多,锁的类型会升级
    你可以看这篇文章: http://peterping.top/2016/12/02/%E4%B8%80%E4%B8%AA%E5%85%B3%E4%BA%8Einsert-ignore%E6%AD%BB%E9%94%81%E9%97%AE%E9%A2%98%E7%9A%84%E8%AE%B0%E5%BD%95/

    题主提到的仅一个 id,这种的 insert 不会用到锁机制~除非他表中有其他 unique key ~
    msg7086
        14
    msg7086  
       2019-08-08 04:11:10 +08:00
    @jjshare 不是,我在北美,上班时间啦。
    你这篇文章我也大致看过。不过我刚刚想了一下,其实锁这个问题应该不需要上升到这么复杂的场景。我的回复有点被你带偏了。

    我之前想表达的是,不管在哪个层面,多线程要保证原子性,必须在某个地方加锁,从最上面应用层,一直到最下面 CPU 指令( LOCK CMPXCHG 又或者是用 XCHG 实现自旋锁),总有一个(或者多个)地方需要锁上,以保证两个线程的两个 CPU 指令序列不会被互相打断。多线程操作单个数据结构本身就是 thread unsafe 的。thread unsafe 场景下就需要用锁。这个和数据库层面上的逻辑锁是两回事。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5310 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 05:59 · PVG 13:59 · LAX 22:59 · JFK 01:59
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.