Programming
java oracle queue batch-processing
Updated Wed, 03 Aug 2022 05:03:37 GMT

Select for update skip locked with row limit


I have a program written in Java that creates 5 threads which select data from Oracle. The select is like this:

select * from queue_requests where request_status = 0 and date_requested <= sysdate and rownum <= ? for update skip locked

I set the parameter to 1024. I often get the following result (using System.out.println):

THREAD 0 UPDATE 1024

THREAD 4 UPDATE 0

THREAD 1 UPDATE 0

THREAD 2 UPDATE 0

THREAD 3 UPDATE 0

So, only one thread finds rows to update. From what i found in different articles, i think oracle firstly applies the rownum clause and after that for update skip locked, so all the threads try to update the first 1024 rows. I could probably use something like:

select * from (select * from queue_requests where request_status = 0 and date_requested <= sysdate for update skip locked) where rownum <= ?

But this will lock all the rows and after that will return a part of them. I don't want to lock all the rows.

I found a solution which created stored procedures, but i can't alter anything in the database. I also found something about Advanced Queueing, but i'm not sure i need something so complex (i didn't find any good example either).

Is there any solution for this problem?




Solution

If you want each thread to only select about one fifth of the records, you might do something like:

select *
from   queue_requests
where  request_status = 0 and
       date_requested <= sysdate and
       mod(DBMS_RowID.RowID_Row_Number(queue_requests.rowid),5) = thread_number
       rownum <= ?
for update
skip locked;

... where thread_number is an integer between 0 and 4.

The success of this depends on the likely number of rows per block for the queue_requests table. If the rows are small then you'll be OK, but if they're large and you have many rows then you might do better with:

mod(DBMS_RowID.RowID_Block_Number(queue_requests.rowid),5)




Comments (1)

  • +0 – Could you explain why the number or rows per block matters? — Oct 19, 2018 at 21:02