lock and get next n records from table in oracle

I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS

PHONE_NUMBER 
PHONE STATUS
1     U
2     O
3     U

U stands for used , O is open/UnUSED .

in my input I will get n as input number , which signifies the number of phones I want to reserve(update status to U from O )

I want a query that will lock(something like SELECT FOR UPDATE) the any n available row (rows that are not locked yet and having status as O )

Constraints: Table has millions of Used and unsed phones

Updates for comments:

problem is i Want n (input) rows to be locked with a particular status . i.e i have to work with rownum

I will be getting multiple concurrent calls in my service with sqli_n as input number of records to select : so

SELECT * FROM PHONE_NUMBER WHERE STATUS='O' and rownum<=:sqli_n FOR UPDATE ;

this query wont work for me it will select only first n rows and if the first n rows are locked in 1 call the next/concurrent call will be stuck /wait until the first call updates the status to Used .

for update no wait will fail the above query

for update skip lock will also not work with row num

Answer

To resolve this situation you need to use a for update cursor, because a for update clause is evaluated after the where:

| PLAN_TABLE_OUTPUT                                                                                    |
| :--------------------------------------------------------------------------------------------------- |
| EXPLAINED SQL STATEMENT:                                                                             |
| ------------------------                                                                             |
| select /*+gather_plan_statistics*/ * from t where   decode(status, 'O',                              |
| id) is not null   and rownum < 10 order by decode(status, 'O', id) asc                               |
| for update skip locked                                                                               |
|                                                                                                      |
| Plan hash value: 2984481354                                                                          |
|                                                                                                      |
| ---------------------------------------------------------------------------------------------------- |
| | Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | |
| ---------------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT              |          |      1 |        |      9 |00:00:00.01 |      21 | |
| |   1 |  FOR UPDATE                   |          |      1 |        |      9 |00:00:00.01 |      21 | |
| |*  2 |   COUNT STOPKEY               |          |      1 |        |      9 |00:00:00.01 |      10 | |
| |   3 |    TABLE ACCESS BY INDEX ROWID| T        |      1 |     49 |      9 |00:00:00.01 |      10 | |
| |*  4 |     INDEX FULL SCAN           | IX_SFU_T |      1 |     10 |      9 |00:00:00.01 |       1 | |
| ---------------------------------------------------------------------------------------------------- |
|                                                                                                      |
| Predicate Information (identified by operation id):                                                  |
| ---------------------------------------------------                                                  |
|                                                                                                      |
|    2 - filter(ROWNUM<10)                                                                             |
|    4 - filter("T"."SYS_NC00005$" IS NOT NULL)                                                        |
|                                                                                                      |

But for explicit cursor with for update skip locked you lock only fetched rows and no rownum evaluation exists in the query: you limit the number of locked rows with limit clause of fetch.

create procedure l(s int)
as
  pragma autonomous_transaction;
  type t_tab is table of t%rowtype
    index by pls_integer;
  t_data t_tab;
  i pls_integer;
  
  cursor c is
  select *
  from t
  where
    decode(status, 'O', id) is not null
  order by decode(status, 'O', id) asc
  for update
  skip locked;
begin
  
  open c;
  fetch c
    bulk collect into t_data
    limit 5
  ;
  /*Do stuff*/
  commit;
  close c;
end;
/

Additionally, to improve the query performance you can use index on expression that evaluates to null on the cases out of interest (where status != ‘O’ in your case). In the upper query I’ve used decodewith the id column as output to assign rows in some predefined order (to use ordering on the same expression that was indexed to avoid table sorting), but if you do not need any order, index expression can be decode(status, 'O', status).

Full setup is below. db<>fiddle here

create table t
as
select level as id,
  dbms_random.string('x', 10)
   as val,
  dbms_random.random() as q,
  decode(mod(level, 20), 0, 'O', 'U')
    as status
from dual
connect by level < 1000

/*We will use this expression
for filtering and sorting*/
create index ix_sfu_t
on t(decode(status, 'O', id))

begin
dbms_stats.gather_table_stats(
  sys_context('USERENV', 'CURRENT_SCHEMA'),
  'T',
  cascade => true
);
end;
/

select /*+gather_plan_statistics*/ *
from t
where
  decode(status, 'O', id) is not null
  and rownum < 5
order by decode(status, 'O', id) asc
for update
skip locked

ID | VAL        |           Q | STATUS
-: | :--------- | ----------: | :-----
20 | 8XS78B92M2 |  1263442844 | O     
40 | XLLUN9DLV4 |  -435709224 | O     
60 | 2DZ4EUH11J | -1328077826 | O     
80 | PJKQMOBISR |  -644473876 | O     

select *
from table(
  dbms_xplan.display_cursor(
  format => 'BASIC +PREDICATE +ALLSTATS LAST'
  ))

| PLAN_TABLE_OUTPUT                                                                                    |
| :--------------------------------------------------------------------------------------------------- |
| EXPLAINED SQL STATEMENT:                                                                             |
| ------------------------                                                                             |
| select /*+gather_plan_statistics*/ * from t where   decode(status, 'O',                              |
| id) is not null   and rownum < 5 order by decode(status, 'O', id) asc                                |
| for update skip locked                                                                               |
|                                                                                                      |
| Plan hash value: 2984481354                                                                          |
|                                                                                                      |
| ---------------------------------------------------------------------------------------------------- |
| | Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | |
| ---------------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT              |          |      1 |        |      4 |00:00:00.01 |       8 | |
| |   1 |  FOR UPDATE                   |          |      1 |        |      4 |00:00:00.01 |       8 | |
| |*  2 |   COUNT STOPKEY               |          |      1 |        |      4 |00:00:00.01 |       2 | |
| |   3 |    TABLE ACCESS BY INDEX ROWID| T        |      1 |      4 |      4 |00:00:00.01 |       2 | |
| |*  4 |     INDEX FULL SCAN           | IX_SFU_T |      1 |        |      4 |00:00:00.01 |       1 | |
| ---------------------------------------------------------------------------------------------------- |
|                                                                                                      |
| Predicate Information (identified by operation id):                                                  |
| ---------------------------------------------------                                                  |
|                                                                                                      |
|    2 - filter(ROWNUM<5)                                                                              |
|    4 - filter("T"."SYS_NC00005$" IS NOT NULL)                                                        |
|                                                                                                      |

/*To show which rows were locked*/
create table t_log(
  s_id int,
  id int
)

create procedure l(s int)
as
  pragma autonomous_transaction;
  type t_tab is table of t%rowtype
    index by pls_integer;
  t_data t_tab;
  i pls_integer;
  
  /*Lock will be applied
  to result set only*/
  cursor c is
  select *
  from t
  where
    decode(status, 'O', id) is not null
  order by decode(status, 'O', id) asc
  for update
  skip locked;

begin
  
  open c;
  fetch c
    bulk collect into t_data
    /*Will fetch 5 not locked rows*/
    limit 5
  ;
  
  /*Step into recursion
  to demonstrate concurrent locking*/
  if s > 0 then
    l(s-1);
  end if;
  
  forall i in 1..t_data.count
    insert into t_log(s_id, id)
      values(s, t_data(i).id);
  commit;
  close c;
end;
/

begin
  l(3);
end;
/

/*Observe results*/
select *
from t_log;

S_ID | ID —: | –: 0 | 320 0 | 340 0 | 360 0 | 380 0 | 400 1 | 220 1 | 240 1 | 260 1 | 280 1 | 300 2 | 120 2 | 140 2 | 160 2 | 180 2 | 200 3 | 20 3 | 40 3 | 60 3 | 80 3 | 100