Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Walbert.

Asked: August 31, 2018 - 7:19 pm UTC

Last updated: September 03, 2018 - 12:44 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

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.

and Connor said...

Take a look here for the "why" and how to workaround it

https://asktom.oracle.com/pls/apex/asktom.search?tag=select-for-update-skip-locked


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect

Walbert Neto, September 03, 2018 - 5:01 pm UTC

I really appreciate the solution.
It works like a charm.
Thanks so much for the help.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library