Java+MySQL环境下select-then-update操作的后端并发问题解决方案

你的场景中，多个线程同时执行查询-更新操作时，很容易出现多个线程拿到同一个序列号的并发问题，以下是几种实用的解决办法：

方案一：使用行锁（SELECT ... FOR UPDATE） #

在事务内通过SELECT ... FOR UPDATE锁定查询到的行，确保同一时间只有一个线程能操作该行：

1. 开启数据库事务
2. 执行查询语句并加锁：

   SELECT id, sn FROM serial_number WHERE available = 1 LIMIT 1 FOR UPDATE;
   

3. 基于查询到的id执行更新：

   UPDATE serial_number SET available = 0 WHERE id = ?;
   

4. 提交事务（若业务操作失败则回滚，释放锁）

Java中可以通过Spring的@Transactional注解管理事务，或者手动用JDBC控制事务边界。注意事务不要包含过长的业务逻辑，避免持有锁时间过久导致性能问题。

方案二：合并查询与更新为单条原子SQL #

利用数据库单条语句的原子性，直接用一条SQL完成“标记为不可用+获取序列号”的操作，从根源避免并发冲突：

适配MySQL 8.0及以上版本（支持RETURNING子句） #

UPDATE serial_number SET available = 0 WHERE available = 1 LIMIT 1 RETURNING id, sn;

执行这条SQL后，数据库会直接返回被更新的行的id和sn，无需额外查询。

适配低版本MySQL（无RETURNING） #

先执行更新，再通过关联逻辑获取对应的sn：

UPDATE serial_number SET available = 0 WHERE available = 1 LIMIT 1;
SELECT id, sn FROM serial_number WHERE available = 0 ORDER BY id DESC LIMIT 1;

这种方式依赖UPDATE的原子性，确保后续查询只会拿到刚被标记为不可用的序列号。

Java中执行这类SQL时，可通过JDBC的executeUpdate()获取受影响行数，若返回1则说明成功抢到序列号，再获取对应的sn即可。

方案三：乐观锁（适合低并发场景） #

给表添加一个version字段（int类型，默认0），通过版本号控制更新：

1. 先查询序列号及版本号：

   SELECT id, sn, version FROM serial_number WHERE available = 1 LIMIT 1;
   

2. 更新时校验版本号：

   UPDATE serial_number SET available = 0, version = version + 1 WHERE id = ? AND version = ? AND available = 1;
   

3. 若更新受影响行数为0，说明该行已被其他线程修改，需重试查询-更新流程。

这种方式不需要加锁，但高并发下可能会有较多重试，适合冲突频率低的场景。

内容的提问来源于stack exchange，提问作者Andrew Law