使用“用于更新"批量收集 [英] bulk collect using "for update"
问题描述
在使用BULK COLLECT在Oracle(11g)中处理记录时，我遇到了一个有趣且意外的问题.
I run into an interesting and unexpected issue when processing records in Oracle (11g) using BULK COLLECT.
以下代码运行良好，可以处理所有上百万条记录，而没有出现问题:
The following code was running great, processing through all million plus records with out an issue:
-- 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"语法添加到游标定义中是一个好主意，以便在读取数据到删除记录之间，另一个进程无法更新任何记录.
Since at the end of processing each group of 100 records (limit 100) we are deleting the records just read and processed, I though it would be a good idea to add the "for update" syntax to the cursor definition so that another process couldn’t update any of the records between the time the data was read and the time the record is deleted.
所以，我更改的代码中唯一的事情是……
So, the only thing in the code I changed was…
cursor My_Data_Cur
is
select col1
,col2
from My_Table_1
for update;
当我在此更改后运行PL/SQL程序包时，该作业仅处理100条记录，然后终止.我确认此更改是通过从游标中删除"for update"引起此问题的，然后程序包再次处理了源表中的所有记录.
When I ran the PL/SQL package after this change, the job only processes 100 records and then terminates. I confirmed this change was causing the issue by removing the "for update" from the cursor and once again the package processed all of the records from the source table.
有什么想法为什么要添加"for update"子句会导致这种行为改变?关于如何解决这个问题有什么建议吗?我将尝试在流程开始时在表上启动排他事务，但这不是一个主意解决方案，因为我真的不想锁定用于处理数据的整个表.
Any ideas why adding the "for update" clause would cause this change in behavior? Any suggestions on how to get around this issue? I’m going to try starting an exclusive transaction on the table at the beginning of the process, but this isn’t an idea solution because I really don’t want to lock the entire table which processing the data.
预先感谢您的帮助，
赠予
推荐答案
问题是您试图在一次提交中进行提取.
The problem is that you're trying to do a fetch across a commit.
使用for update子句打开My_Data_Cur时，Oracle必须锁定My_Data_1表中的每一行，然后才能返回任何行.当您commit时，Oracle必须释放所有这些锁(Oracle创建的锁不覆盖事务).由于游标不再具有您请求的锁，因此Oracle必须关闭游标，因为它不再满足for update子句.因此，第二次读取必须返回0行.
When you open My_Data_Cur with the for update clause, Oracle has to lock every row in the My_Data_1 table before it can return any rows. When you commit, Oracle has to release all those locks (the locks Oracle creates do not span transactions). Since the cursor no longer has the locks that you requested, Oracle has to close the cursor since it can no longer satisfy the for update clause. The second fetch, therefore, must return 0 rows.
最合乎逻辑的方法几乎总是要删除commit并在单个事务中完成全部操作.如果确实确实需要单独的事务，则需要为循环的每次迭代打开和关闭游标.最有可能的是，您希望做一些限制游标每次打开仅返回100行的操作(即rownum <= 100子句)，这样就不会招致访问每一行以放置锁和然后除了您处理并删除的100条记录以外的每一行，以便每次通过循环释放锁.
The most logical approach would almost always be to remove the commit and do the entire thing in a single transaction. If you really, really, really need separate transactions, you would need to open and close the cursor for every iteration of the loop. Most likely, you'd want to do something to restrict the cursor to only return 100 rows every time it is opened (i.e. a rownum <= 100 clause) so that you wouldn't incur the expense of visiting every row to place the lock and then every row other than the 100 that you processed and deleted to release the lock every time through the loop.
这篇关于使用“用于更新"批量收集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
