Hi,
I have a scenario where I have to get first 10 rows from filtered and ordered result set for update.
As we are on multi user environment we are trying to use SELECT FOR UPDATE SKIP LOCKED.

Let me put it in a simple example

Scenario-1

SESSION-1

select emp_id from emp
where rownum>=1
and  rownum <=10
for update skip locked 
order by emp_id;

SESSION-2

select emp_id from emp
where  rownum>=11 
and  rownum <=20
for update skip locked 
order by emp_id;

In above example I'm getting result from Session-1 but not from session-2(assume emp table has 1 million records).

---------------------------------------------------------------------------------
Scenario-2

SESSION-1

select emp_id from emp
where rownum>=1
and  rownum <=10
for update skip locked 
order by emp_id;

SESSION-2

select emp_id from emp
where  rownum>=1
and  rownum <=20
for update skip locked 
order by emp_id;

In above example I'm getting result from Session-1 and session-2 is also returning result from rownum 11 to 20.


I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

Am I missing something or is this a bug?

Can you please help me to get top n records from each session with no common records between the sessions?

where  rownum>=11

select count(*) from scott.emp
where  rownum>=11
and    rownum <=20;

COUNT(*)   
         0 

where  rownum>=11