Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

FOR UPDATE SKIP LOCKED with ROWNUM on ORACLE 12c

I searched all the forum but I didn't find any clue about it. I have a staging table that multiple threads consume. To avoid deadlock, I'm using something like this:

SELECT ID_MESSAGE
FROM TB_STAGE_IN S 
WHERE S.CD_STATUS = 0 
AND S.ID_JOB_SCHEDULE IS NULL 
AND ROWNUM <= 10000 
FOR UPDATE SKIP LOCKED; 

It works fine, but the threads don't reach the max of 10,000 rows. It's like:

  • Thread 1: 5000
  • Thread 2: 3000
  • Thread 2: 2000

I know that happens because the rownumber for them is the same, but the table has thousands and thousands of rows. What I really need is the thread gets 10,000 rows unlocked on every step.

I tried using FETCH FIRST 10000 ROWS ONLY, but I receive the message below: ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

Could you all please help me?

Thanks for the kind.

like image 631
Walbert Neto Avatar asked Sep 05 '25 03:09

Walbert Neto


1 Answers

Ask Tom has a suggestion that goes like this

open C;  -- cursor C is select ... for update skip locked;

loop
  fetch C bulk collect into :my_array limit 100;
  append :my_array to :to_be_processed_array;
  exit when c%notfound or :to_be_processed_array.count >= 10000;
end loop;

-- process any rows in :to_be_processed_array

close C;
like image 112
Dominique Fortin Avatar answered Sep 07 '25 17:09

Dominique Fortin