0

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

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

1 Answer 1

1

So, you have discovered that you can't do what you want; so you may want to do what you can by redesigning your process. One approach might be to introduce a new column, e.g. session_id, to indicate what application session, if any, is processing the row.

Each session will then start by atomically reserving a set of rows:

UPDATE phone_number SET session_id='<whatever>' 
WHERE status='O' AND session_id IS NULL AND ROWNUM<=:sqli_n;
COMMIT;

It will then do whatever it needs to do and finally release the reserved rows:

UPDATE phone_number SET session_id=NULL 
WHERE session_id='<whatever>';
COMMIT;

You'll likely need to revise indexes on this table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.