oracle - SQL For Update Skip Locked Query and Java Multi Threading - How to fix this -

select          id         table_name tkn1,         (select              id,              rownum rnum          table_name                        procs_dt null          order prty desc, cret_dt) result           tkn1.id= result.id         , result.rnum <= 10 update of tkn1.id skip locked 

here problem. 2 threads accessing query @ same time

thread 1 - executes select , locks 10 rows ordered descending priority , created date. next update procs_dt todays date separate query..

thread 2 - before update of procs_dt or commit happens thread 1 , thread executes query. requirement next 10 unlocked rows must handed on thread 2. happens same set of locked rows comes out of inner query since procs_dt still null , yet updated thread 1 , since skip locked given in outer query, 10 rows skipped , no records returned thread 2 process

this defeats multi threading requirement.

how fix query? tried adding skip locked inner query. oracle 11g doesn allow it.

experts please help. using oracle 11g

i'd go : cursor select rows in order update, , use limit clause first ten available.

create table gm_temp select rownum id, table_name obj_name, date '2011-01-01' + rownum create_date  all_tables rownum < 500;  create type tab_number table of number;  declare   cursor c_table      select id gm_temp order create_date desc update of id skip locked;   t_table_src tab_number := tab_number(); begin   open c_table;   fetch c_table bulk collect t_table_src limit 10;   close c_table;   dbms_output.put_line(':'||t_table_src.count||':'||t_table_src(1)); end; 

actually, i'd firstly see whether processing outstanding rows set better multi-threading.

then if did decide needed form of multi-threading, i'd @ pipelined functions parallel enabled (assuming on enterprise edition).