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

[meinjoy]

随机获取到 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

这种情况怎么避免？

[gz911122]

select for update

然后 update 的时候检测版本号

[lihongjie0209]

锁表？

[zhaorunze]

select 和 update 在同一个事务隔离级别为可重复读的事务中就 ok 了

[ColoThor]

简单点，获取出来，先删了，再插入？

[zhaorunze]

前题数据库要支持行级锁。。。InnoDB 是没问题的

[meinjoy]

@zhaorunze 是 InnoDB

@gz911122 select for update 尝试过，还是会获取到相同的 ID

[gz911122]

@meinjoy 然后 update 判断版本号啊...

[meinjoy]

@zhaorunze 具体怎么使用？
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
stmt = conn.prepareStatement(sql);
stmt.executeQuery();

然后 update ?

[opengps]

缓存里加 redis 锁

[Raymon111111]

防并发的话两个手段, 数据库自带版本号, 更新的时候只会有一个 update 成功; 把取出来的记录以 id 维度加分布式锁(redis), 拿锁失败表明已经有别人拿到了相同的记录.

还要考虑拿重复记录后失败怎么办的问题

[mawerss1]

其他线程获取之后的操作也是 set state = 1 ? 直接 replace into

[lovelife1994]

repeatable read 考虑会不会出现锁升级导致死锁 ，Select for update 不知道 x 锁会不会保留到事务结束。

[meinjoy]

@gz911122 怎么判断，数据库接触的少啊

[bobuick]

1L 的意思里面, 版本号得自己加的. db 自己的 MVVC 的版本号不是面向应用开发者的.
这是一个 cas 的概念

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

锁释放前, 你其他 select 会阻塞

[zhaorunze]

@meinjoy 是的，事务提交或者回滚之前 update 就可以了

[justRua]

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]

sql 改一下加个 for update，其他不变就行了，并发查询的时候会阻塞到上一个事务提交，不会查到重复的 ID，jdbc 要 setAutoCommit(false)不然会自动提交

[petelin]

@zhaorunze 可重复读不加版本明显不行啊

[meinjoy]

@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]

@meinjoy
#13
定义一个版本号字段 v ,select 的时候查出来 v,update 的时候判断 v=v, 符合则更新,update 的时候版本号+1

[zhaorunze]

@petelin 加版本是啥意思，还有这么 low 的操作吗，可能我的事务被 spring 托管了

[jsy123392550]

感觉这语句乐观锁要比悲观锁开销大啊，还是别用版本号了吧

[Aresxue]

用悲观锁吧，别自旋了，感觉冲突的几率还不小（假设取值是完全随机的,获取每个值对地概率都一样，那么概率是((n-5)/n)^5?）