Hy guys,
I searched all the forum but I didn't find any clue about it.
I have a stage in table that multiple threads consume it.
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.