多线程应用中如何无锁访问需刷新数据表行并最小化冲突？

问题概述 #

你正在开发一个包含数据表的应用，需要基于refresh_me列刷新指定行，数据表结构如下：

CREATE TABLE IF NOT EXISTS `datatable` (
  `id` bigint(20) NOT NULL,
  `row_created` datetime(6) NOT NULL,
  `row_modified` datetime(6) DEFAULT NULL,
  `refresh_me` bit(1) DEFAULT NULL,
  `refresh_date` datetime(6) DEFAULT NULL,
  `next_refresh_date` datetime(6) DEFAULT NULL,
  `refresh_days` int(11) NOT NULL,
  `version` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

核心需求是：当refresh_me为true时刷新该行，在多线程环境下避免重复获取相同行，同时不使用select for update，仅依赖Spring JPA的乐观锁机制。目前采用"查询100行随机选10行"的方式，在线程数较少时有效，但面对100个线程时冲突概率仍然很高，需要更优方案。

优化方案 #

1. 基于行标识的分段查询 #

从根源上避免线程间的行重叠，给每个线程分配专属的行范围：

• 固定线程数场景：按id取模拆分，比如有100个线程，每个线程查询：

  SELECT * FROM datatable WHERE refresh_me = 1 AND id % 100 = [线程编号] ORDER BY row_modified ASC;
  

  线程编号从0到99，确保每个线程只处理自己分段内的行，完全没有冲突。
• 动态线程数场景：用keyset分页（基于上一次处理的最大id或row_modified）替代普通偏移分页，避免偏移过大的性能问题。比如每个线程每次查询：

  SELECT * FROM datatable WHERE refresh_me = 1 AND id > [上一次处理的最大id] ORDER BY id ASC LIMIT 10;
  

  这种方式可以让线程们按顺序"瓜分"待刷新行，不会重复获取。

2. 引入"处理中"原子标记 #

新增字段实现行的抢占式锁定，避免多线程竞争同一行：

• 给数据表新增两个字段：

  ALTER TABLE datatable ADD COLUMN processing bit(1) DEFAULT 0;
  ALTER TABLE datatable ADD COLUMN lock_expire_time datetime(6) DEFAULT NULL;
  

• 每个线程先执行原子更新，抢占待处理的行：

  UPDATE datatable 
  SET processing = 1, lock_expire_time = DATE_ADD(NOW(), INTERVAL 5 MINUTE) 
  WHERE refresh_me = 1 AND processing = 0 AND (lock_expire_time IS NULL OR lock_expire_time < NOW()) 
  LIMIT 10;
  

• 然后查询被标记的行（分布式多实例场景可再加lock_owner字段存储实例标识），处理完成后将processing设回0，同时更新refresh_me、refresh_date等字段。
• 结合Spring JPA，用@Modifying注解执行更新语句，再查询对应行处理即可。这种方式通过数据库原子操作确保同一行只会被一个线程抢占，过期时间还能避免线程挂起后行被永久锁定。

3. 升级随机选取策略+乐观锁重试 #

如果坚持使用随机选取逻辑，可以优化为：

• 每个线程查询更大批次的候选行（比如300行），随机选取30行；
• 处理前通过乐观锁校验版本号，用批量更新语句执行刷新：

  @Modifying
  @Query("UPDATE Datatable d SET d.refreshMe = false, d.refreshDate = CURRENT_TIMESTAMP, d.nextRefreshDate = CURRENT_TIMESTAMP + INTERVAL d.refreshDays DAY, d.version = d.version + 1 WHERE d.id IN :ids AND d.version = :versions")
  int batchUpdateRefreshedRows(@Param("ids") List<Long> ids, @Param("versions") List<Long> versions);
  

• 方法返回的更新行数即为成功处理的行数，未成功的（版本冲突）可以放入本地重试队列，稍后再从候选行中补充新行处理。

4. 备选：SKIP LOCKED轻量行锁（若可接受） #

虽然你不想用select for update，但MySQL 8.0+支持的SKIP LOCKED可以让线程直接获取未被锁定的行，无需等待：

SELECT * FROM datatable WHERE refresh_me = 1 ORDER BY row_modified ASC LIMIT 10 FOR UPDATE SKIP LOCKED;

这种方式冲突率极低，效率很高，若能接受这种轻量级行锁，是最优的性能方案，但如果必须严格只用乐观锁，可以忽略此方案。

方案选型建议 #

• 线程数固定或可控时，优先选分段查询，实现简单且完全无冲突；
• 分布式多实例或线程数动态变化时，优先选处理中原子标记，适配性更强；
• 若不想修改表结构，可升级随机选取+乐观锁重试，但冲突率仍高于前两种方案；
• 若数据库版本支持且可接受轻量锁，SKIP LOCKED是性能最优的选择。

内容的提问来源于stack exchange，提问作者Antoner.me