V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
牛客网
meinjoy
V2EX  ›  Java

JDBC 随机获取 5 行数据,在 update 之前怎么防止并发的其他线程获取到重复的数据

  •  
  •   meinjoy · 2019-10-17 15:38:32 +08:00 · 3075 次点击
    这是一个创建于 373 天前的主题,其中的信息可能已经有所发展或是发生改变。

    随机获取到 5 个不同的数据 id

    1. SELECT id,state FROM (select * from test_list where state=0) t WHERE id >= ((SELECT MAX(id) FROM test_list)-(SELECT MIN(id) FROM test_list)) * RAND() + (SELECT MIN(id) FROM test_list) LIMIT 5;

    2. 然后更新状态 state=1

    后来发现个问题,第一次查询获取到 5 个随机 id ( 20,343,123,309,234 ),还没有 update 的时候,有其他线程获取的数据也有 20 这个 id

    这种情况怎么避免?

    23 条回复    2019-10-18 13:09:00 +08:00
    gz911122
        1
    gz911122   2019-10-17 15:41:37 +08:00
    select for update

    然后 update 的时候检测版本号
    lihongjie0209
        2
    lihongjie0209   2019-10-17 15:41:57 +08:00
    锁表?
    zhaorunze
        3
    zhaorunze   2019-10-17 15:44:30 +08:00
    select 和 update 在同一个事务隔离级别为可重复读的事务中就 ok 了
    ColoThor
        4
    ColoThor   2019-10-17 15:44:50 +08:00
    简单点,获取出来,先删了,再插入?
    zhaorunze
        5
    zhaorunze   2019-10-17 15:46:21 +08:00
    前题数据库要支持行级锁。。。InnoDB 是没问题的
    meinjoy
        6
    meinjoy   2019-10-17 15:54:05 +08:00
    @zhaorunze 是 InnoDB

    @gz911122 select for update 尝试过,还是会获取到相同的 ID
    gz911122
        7
    gz911122   2019-10-17 15:56:13 +08:00
    @meinjoy 然后 update 判断版本号啊...
    meinjoy
        8
    meinjoy   2019-10-17 15:58:08 +08:00
    @zhaorunze 具体怎么使用?
    conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    stmt = conn.prepareStatement(sql);
    stmt.executeQuery();

    然后 update ?
    opengps
        9
    opengps   2019-10-17 15:59:15 +08:00
    缓存里加 redis 锁
    Raymon111111
        10
    Raymon111111   2019-10-17 16:20:00 +08:00
    防并发的话两个手段, 数据库自带版本号, 更新的时候只会有一个 update 成功; 把取出来的记录以 id 维度加分布式锁(redis), 拿锁失败表明已经有别人拿到了相同的记录.

    还要考虑拿重复记录后失败怎么办的问题
    mawerss1
        11
    mawerss1   2019-10-17 16:34:15 +08:00
    其他线程获取之后的操作也是 set state = 1 ? 直接 replace into
    lovelife1994
        12
    lovelife1994   2019-10-17 16:49:51 +08:00 via iPhone
    repeatable read 考虑会不会出现锁升级导致死锁 ,Select for update 不知道 x 锁会不会保留到事务结束。
    meinjoy
        13
    meinjoy   2019-10-17 17:03:07 +08:00
    @gz911122 怎么判断,数据库接触的少啊
    bobuick
        14
    bobuick   2019-10-17 17:10:35 +08:00
    1L 的意思里面, 版本号得自己加的. db 自己的 MVVC 的版本号不是面向应用开发者的.
    这是一个 cas 的概念

    要么 select for update, transaction 结束后锁释放, 要么 cas 方式也可以.

    锁释放前, 你其他 select 会阻塞
    zhaorunze
        15
    zhaorunze   2019-10-17 17:43:46 +08:00
    @meinjoy 是的,事务提交或者回滚之前 update 就可以了
    justRua
        16
    justRua   2019-10-17 18:46:48 +08:00   ❤️ 1
    sql 要改一下,有个子查询导致 for update 的行锁没加上
    SELECT id,state FROM test_list
    WHERE id >= ((SELECT MAX(id) FROM test_list )-(SELECT MIN(id) FROM test_list )) * RAND() + (SELECT MIN(id) FROM test_list )
    AND state = 0
    LIMIT 5 for UPDATE
    justRua
        17
    justRua   2019-10-17 18:56:23 +08:00
    sql 改一下加个 for update,其他不变就行了,并发查询的时候会阻塞到上一个事务提交,不会查到重复的 ID,jdbc 要 setAutoCommit(false)不然会自动提交
    petelin
        18
    petelin   2019-10-17 19:39:12 +08:00 via iPhone
    @zhaorunze 可重复读不加版本明显不行啊
    meinjoy
        19
    meinjoy   2019-10-17 20:04:51 +08:00
    @justRua

    String sql = SELECT id,state FROM test_list
    WHERE id >= ((SELECT MAX(id) FROM test_list )-(SELECT MIN(id) FROM test_list )) * RAND() + (SELECT MIN(id) FROM test_list )
    AND state = 0
    LIMIT 1 for UPDATE;

    conn.setAutoCommit(false);
    stmt = conn.prepareStatement(sql);
    stmt.executeQuery();
    stmt = conn.prepareStatement(updateId);
    stmt.executeUpdate();
    conn.commit();

    这样就行了?
    gz911122
        20
    gz911122   2019-10-18 09:29:31 +08:00
    @meinjoy
    #13
    定义一个版本号字段 v ,select 的时候查出来 v,update 的时候判断 v=v, 符合则更新,update 的时候版本号+1
    zhaorunze
        21
    zhaorunze   2019-10-18 09:49:10 +08:00
    @petelin 加版本是啥意思,还有这么 low 的操作吗,可能我的事务被 spring 托管了
    jsy123392550
        22
    jsy123392550   2019-10-18 10:07:41 +08:00
    感觉这语句乐观锁要比悲观锁开销大啊,还是别用版本号了吧
    Aresxue
        23
    Aresxue   2019-10-18 13:09:00 +08:00
    用悲观锁吧,别自旋了,感觉冲突的几率还不小(假设取值是完全随机的,获取每个值对地概率都一样,那么概率是((n-5)/n)^5?)
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2445 人在线   最高记录 5168   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 15:36 · PVG 23:36 · LAX 08:36 · JFK 11:36
    ♥ Do have faith in what you're doing.