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
Advertisement
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 lockedID | 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; /
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/*Observe results*/ select * from t_log;