Oracle百万级tele_detais表Java并发处理与更新方案咨询

背景信息 #

现有一张tele_detais表，约1000万条记录，表结构如下：

TN: varchar2 (唯一10位数字PK),
xyz :varchar2,
abc :varchar2 , 
mailids :varchar2, 
status :number , 
emailstatus :number  

要求10小时内全量处理所有记录，流程为：获取单条/批量记录→执行业务逻辑（更新数据并获取TN）→向mailids中的邮箱发送邮件→更新emailstatus。采用多线程处理时遇到的问题及解决方案如下：

问题1：无WHERE子句全量处理场景下，如何确保多线程获取不同记录？ #

两种可靠实现方案：

• 主键分片法：利用TN是10位唯一数字主键的特性，按TN数值取模分配任务。比如启动N个线程，每个线程处理MOD(TO_NUMBER(TN), N) = 线程编号的记录，每个线程的任务范围固定，天然不会重复获取同一条记录。
• 动态认领法：新增一个临时列（如processing_thread），线程每次批量认领未被标记的记录，同时用原子操作标记为当前线程ID，确保每条记录只会被一个线程锁定并处理。

问题2：需添加何种锁机制避免不同线程获取同一条记录？ #

针对Oracle数据库，推荐两种锁机制：

• 行级锁+SELECT ... FOR UPDATE SKIP LOCKED：这是高并发批量处理的最优方案。查询时直接锁定未被其他线程锁定的记录，自动跳过已锁定的记录，既避免了线程间的等待阻塞，又能确保同一条记录不会被多个线程获取。
• 乐观锁：如果业务允许少量冲突重试，可以利用emailstatus字段做乐观锁。比如未处理的记录emailstatus为0，线程查询时只取emailstatus=0的记录，更新emailstatus时带上WHERE emailstatus=0的条件，如果更新行数为0，说明这条记录已经被其他线程处理，直接跳过即可。但这种方式在高并发下冲突概率较高，适合线程数较少的场景。

问题3：应编写怎样的带限制条件的查询语句？ #

结合上述解决方案，给出对应SQL语句：

方案1：主键分片查询（预分配任务） #

假设启动4个线程，线程0执行的查询语句：

SELECT TN, xyz, abc, mailids, status, emailstatus
FROM tele_detais
WHERE MOD(TO_NUMBER(TN), 4) = 0;

其他线程依次将取模结果改为1、2、3即可，每个线程处理固定分片的记录。

方案2：并发认领查询（SKIP LOCKED） #

每次批量获取100条未处理的记录并锁定：

SELECT TN, xyz, abc, mailids, status, emailstatus
FROM tele_detais
WHERE emailstatus = 0  -- 假设0代表未处理
AND ROWNUM <= 100
FOR UPDATE SKIP LOCKED;

处理完成后更新emailstatus：

UPDATE tele_detais
SET emailstatus = 1  -- 1代表已处理
WHERE TN = :tn;

方案3：临时列标记查询（原子认领） #

先新增临时列：

ALTER TABLE tele_detais ADD processing_thread VARCHAR2(20);

线程执行原子更新并获取记录：

UPDATE tele_detais
SET processing_thread = 'THREAD_001'  -- 当前线程ID
WHERE processing_thread IS NULL
AND emailstatus = 0
AND ROWNUM <= 100
RETURNING TN, xyz, abc, mailids, status, emailstatus INTO :record_list;

这种方式在同一个SQL中完成标记和数据获取，完全避免了并发冲突。

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