SELECT FOR UPDATE锁与游标和批处理DML的交互 [英] Interaction of SELECT FOR UPDATE lock with cursor and batch DML

问题描述

当前我的代码框架如下:

Currently my code skeleton looks like:

varchar rowidvariable[batchlimitcount][19];

stmt = "Select rowid from table_name where xx"
delstmt = "delete from table_name where rowid=:rowidvariable"
prepare delstatement using delstmt;

prepare cursor from stmt;
declare cursor from preparecursor;
open cursor;

while(1)
{
    fetch cursor into rowidvariable;

    somecondition {break};        

    exec sql for fetchedCount 
        execute delstatement using :rowidvariable;

    commit;

} 

有人向我指出，使用SELECT FOR UPADATE锁定表将是确保100％的行被锁定并且ROWID在任何情况下(无论机会多么小)都不会改变的方法

It was pointed out to me that locking the table using SELECT FOR UPADATE would be the way to go to ensure 100% that the rows are locked and ROWID in any instance (however small the chance) doesn't get changed.

但是commit;释放了锁，并且由于有成千上万的记录，所以我确实要分批删除它，这非常重要，因此在推进所提出的解决方案方面似乎存在挑战.

However as commit; releases the lock and its extremely important that I do delete in batches as there are millions of records, there seems to be challenge in moving forward with the proposed solution.

请告知是否还有更好的选择.预先感谢.

Please advise if there are any better alternatives. Thanks in advance.

参考我之前的问题:链接

请注意，整个过程是在 oracle-pro-c 中进行的.

Note the whole process is happening in oracle-pro-c.

推荐答案

听起来像这样的问题是，您必须删除数百万行，因此要分批执行

sounds like the issue is that you have to delete millions of rows and so you want to do it in baches

如果是这样，这可能对您有用-它会循环遍历并删除行并提交，这样您就不会用完撤消操作，而不必担心锁定行

if so, this might work for you -- it will loop through and delete rows and commit so that you don't run out of undo and you don't have to worry about locking rows

begin
   loop
      delete from xx where yyy=zzz and rownum < 1000;
      exit when sql%rowcount = 0;
      commit;
   end loop;
   commit;
end;
/

这篇关于SELECT FOR UPDATE锁与游标和批处理DML的交互的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！