JDBC 随机获取 5 行数据，在 update 之前怎么防止并发的其他线程获取到重复的数据
meinjoy · 2019-10-17 15:38:32 +08:00 · 4474 次点击这是一个创建于 1864 天前的主题，其中的信息可能已经有所发展或是发生改变。
随机获取到 5 个不同的数据 id
-
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;
-
然后更新状态 state=1
后来发现个问题，第一次查询获取到 5 个随机 id （ 20，343，123，309，234 ）,还没有 update 的时候，有其他线程获取的数据也有 20 这个 id
这种情况怎么避免？
 |
1
gz911122 2019-10-17 15:41:37 +08:00
select for update
然后 update 的时候检测版本号 |
 |
2
lihongjie0209 2019-10-17 15:41:57 +08:00
锁表？
|
 |
3
zhaorunze 2019-10-17 15:44:30 +08:00
select 和 update 在同一个事务隔离级别为可重复读的事务中就 ok 了
|
 |
4
ColoThor 2019-10-17 15:44:50 +08:00
简单点，获取出来，先删了，再插入？
|
|
5
zhaorunze 2019-10-17 15:46:21 +08:00
前题数据库要支持行级锁。。。InnoDB 是没问题的
|
 |
6
meinjoy OP |
|
8
meinjoy OP @zhaorunze 具体怎么使用？
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); stmt = conn.prepareStatement(sql); stmt.executeQuery(); 然后 update ? |
 |
9
opengps 2019-10-17 15:59:15 +08:00
缓存里加 redis 锁
|
 |
10
Raymon111111 2019-10-17 16:20:00 +08:00
防并发的话两个手段, 数据库自带版本号, 更新的时候只会有一个 update 成功; 把取出来的记录以 id 维度加分布式锁(redis), 拿锁失败表明已经有别人拿到了相同的记录.
还要考虑拿重复记录后失败怎么办的问题 |
 |
11
mawerss1 2019-10-17 16:34:15 +08:00
其他线程获取之后的操作也是 set state = 1 ? 直接 replace into
|
 |
12
lovelife1994 2019-10-17 16:49:51 +08:00 via iPhone
repeatable read 考虑会不会出现锁升级导致死锁 ，Select for update 不知道 x 锁会不会保留到事务结束。
|
 |
14
bobuick 2019-10-17 17:10:35 +08:00
1L 的意思里面, 版本号得自己加的. db 自己的 MVVC 的版本号不是面向应用开发者的.
这是一个 cas 的概念 要么 select for update, transaction 结束后锁释放, 要么 cas 方式也可以. 锁释放前, 你其他 select 会阻塞 |
 |
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 |
|
17
justRua 2019-10-17 18:56:23 +08:00
sql 改一下加个 for update，其他不变就行了，并发查询的时候会阻塞到上一个事务提交，不会查到重复的 ID，jdbc 要 setAutoCommit(false)不然会自动提交
|
|
19
meinjoy OP @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(); 这样就行了？ |
|
20
gz911122 2019-10-18 09:29:31 +08:00
|
 |
22
jsy123392550 2019-10-18 10:07:41 +08:00
感觉这语句乐观锁要比悲观锁开销大啊，还是别用版本号了吧
|
 |
23
Aresxue 2019-10-18 13:09:00 +08:00
用悲观锁吧，别自旋了，感觉冲突的几率还不小（假设取值是完全随机的,获取每个值对地概率都一样，那么概率是((n-5)/n)^5?）
|
Select Language