SELECT FOR UPDATE锁与游标及批量DML的交互问题

嘿，针对你用Oracle Pro*C做百万级批量删除遇到的锁释放问题，我给你整理几个实用的优化方案，既能保证数据一致性，又能兼顾批量效率：

这是Oracle 12c以后引入的特性，完美解决并发场景下的锁竞争问题。它会自动跳过已经被其他会话锁定的行，只返回当前会话能锁定的行，而且每次批量处理完commit释放锁后，下次fetch会继续取未被处理的行，完全不用怕重复或者漏删。

适配你的Pro*C代码框架，修改后大概是这样：

varchar rowidvariable[batchlimitcount][19];
int batchsize = 1000; // 自定义批量大小，根据性能调整
stmt = "SELECT rowid FROM table_name WHERE xx FOR UPDATE SKIP LOCKED FETCH FIRST :batchsize ROWS ONLY";
delstmt = "DELETE FROM table_name WHERE rowid=:rowidvariable";

PREPARE delstatement FROM :delstmt;
PREPARE cursor_stmt FROM :stmt;
DECLARE cur CURSOR FOR cursor_stmt;

while(1) {
    OPEN cur USING :batchsize;
    FETCH cur INTO :rowidvariable;
    fetchedCount = sqlca.sqlerrd[2]; // 获取实际fetch到的行数
    
    if (fetchedCount == 0) {
        break; // 没有更多行需要处理
    }
    
    EXEC SQL FOR :fetchedCount EXUTE delstatement USING :rowidvariable;
    COMMIT;
    CLOSE cur;
}

这个方案的好处是：

• 完全避免锁等待，其他会话可以同时处理不同批次的行
• 不用怕commit释放锁后行被修改，因为我们锁定的时候已经确保行是符合条件的
• 批量处理的效率很高，适合百万级数据

如果你的Oracle版本低于12c，没法用SKIP LOCKED，那可以用范围分片的思路：用表的主键或者唯一索引列（比如ID）来拆分数据，每次删除一个范围内的行，不用依赖ROWID，也能避免全表锁。

举个例子，假设表有主键id，代码可以这么写：

long min_id, max_id, current_id;
int batch_range = 10000; // 每次删1万条的范围

// 先获取要删除数据的ID范围
EXEC SQL SELECT MIN(id), MAX(id) INTO :min_id, :max_id FROM table_name WHERE xx;

current_id = min_id;
while(current_id <= max_id) {
    EXEC SQL DELETE FROM table_name 
             WHERE xx AND id BETWEEN :current_id AND :current_id + :batch_range - 1;
    COMMIT;
    
    // 更新当前范围，同时处理可能的ID不连续情况
    EXEC SQL SELECT MIN(id) INTO :current_id FROM table_name 
             WHERE xx AND id > :current_id + :batch_range - 1;
    if (sqlca.sqlcode == 1403) { // 没有更多行
        break;
    }
}

这个方案的优势是：

• 兼容所有Oracle版本，不需要依赖新特性
• 每次只锁定一个范围的行，锁粒度小，对并发影响小
• 不用维护ROWID数组，代码逻辑更简洁

如果你不想大改原有框架，也可以在删除前加一层验证，确保要删除的行依然符合条件，避免commit释放锁后行被修改的问题：

把删除语句改成：

DELETE FROM table_name WHERE rowid=:rowidvariable AND xx;

这样即使在fetch和delete之间，行被修改导致不符合xx条件了，这条删除也不会生效，保证数据正确性。

不过这个方案还是存在一定的锁竞争风险，如果有其他会话在你fetch后修改了行，你可能会删不到，但至少不会删错数据。

另外补充一下：你之前定义的varchar rowidvariable[batchlimitcount][19]是没问题的，Oracle的扩展ROWID是18个字符，留一个位置给字符串终止符刚好。

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