oracle - 使用 "for update"批量收集

Question

在 Oracle (11g) 中使用 BULK COLLECT 处理记录时，我遇到了一个有趣且意外的问题。

以下代码运行良好，可以毫无问题地处理所有数百万条记录:

-- Define cursor
cursor My_Data_Cur Is
Select col1
      ,col2
from My_Table_1;
…

-- Open the cursor
open My_Data_Cur;

-- Loop through all the records in the cursor
loop

  -- Read the first group of records
  fetch My_Data_Cur
  bulk collect into My_Data_Rec
  limit 100;

  -- Exit when there are no more records to process
  Exit when My_Data_Rec.count = 0;

  -- Loop through the records in the group
  for idx in 1 .. My_Data_Rec.count
  loop
    … do work here to populate a records to be inserted into My_Table_2 …
  end loop;

  -- Insert the records into the second table
  forall idx in 1 .. My_Data_Rec.count
  insert into My_Table_2…;

  -- Delete the records just processed from the source table
  forall idx in 1 .. My_Data_Rec.count
  delete from My_Table_1 …;

  commit;
end loop;

由于在处理完每组 100 条记录(限制为 100 条)后，我们将删除刚刚读取和处理的记录，我认为将“for update”语法添加到游标定义中是个好主意，这样在读取数据和删除记录之间，另一个进程无法更新任何记录。

所以，我更改的代码中唯一的事情是......

cursor My_Data_Cur
is
  select col1
        ,col2
from My_Table_1
for update;

当我在此更改后运行 PL/SQL 程序包时，该作业仅处理 100 条记录，然后终止。我通过从游标中删除“for update”来确认此更改是导致问题的原因，并且程序包再次处理了源表中的所有记录。

知道为什么添加“for update”子句会导致这种行为改变吗？关于如何解决这个问题的任何建议？我将尝试在流程开始时在表上启动独占事务，但这不是一个理想的解决方案，因为我真的不想锁定处理数据的整个表。

预先感谢您的帮助，

授予

Answer 1

问题是您正在尝试跨提交进行提取。

当你打开My_Data_Cur与 for update子句，Oracle 必须锁定 My_Data_1 中的每一行在它可以返回任何行之前。当你commit , Oracle 必须释放所有这些锁(Oracle 创建的锁不跨越事务)。由于游标不再具有您请求的锁，Oracle 必须关闭游标，因为它不再满足 for update条款。因此，第二次提取必须返回 0 行。

最合乎逻辑的方法几乎总是删除 commit并在一次交易中完成所有事情。如果您真的、真的、真的需要单独的事务，则需要为循环的每次迭代打开和关闭游标。最有可能的是，您想做一些事情来限制游标每次打开时只返回 100 行(即 rownum <= 100 子句)，这样您就不会产生访问每一行以放置锁和然后除了您处理和删除的 100 行以外的每一行，每次通过循环释放锁。