asked on

How to select only unlocked rows

I have a long-running transaction that runs concurrently from multiple sessions. Each transaction needs to update a randomly selected row from a table of 100 rows. It needs to do this without waiting for the other transactions to release their update locks. In other words, it needs to randomly select for update any of the rows which is not locked for update by another transaction. If all the rows are locked, it's ok to fail with a resource busy error, however, if any row is not locked, then I want to select one of them for update (don't care which one).

As it stands now, the application does a "Select random_row for update NOWAIT", but the incidence of resource busy errors has become unacceptable. I can remove the NOWAIT, but then the delay waiting for resources to free up is unacceptable.

Any ideas?

sathyagiri

If your transaction is in PL/SQL

you could have something like

declare
var_rowid rowid;
var_col1 table1.col1%type;
var_col2 table1.col2%type;
cursor c is select rowid,col1,col2 from table1;
NOWAIT_EXCEPT EXCEPTION;
PRAGMA EXCEPTION INIT(NOWAIT_EXCEPT , -00054);
BEGIN
FOR I IN C
LOOP
SELECT col1 into VAR_COL1 FROM TABLE1 WHERE ROWID = I.ROWID FOR UPDATE NO WAIT;
EXCEPTION
WHEN NOWAIT_EXCEPT THEN
CONTINUE;
END;
END LOOP;
END:

ASKER CERTIFIED SOLUTION

sathyagiri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Acton Wang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Acton Wang

then you can use this function as:

(1) in pl/sql block:
declare
rid rowid;
..
begin
rid := getRow;
...
update <table> set ... where rowid = rid;
commit;
end;

(2) in sql*plus:
variable rid varchar2(20);

exec :rid := getrow;

update <table> set ... where rowid = :rid;

Acton

Acton Wang

>>cursor c is select rowid from test1;
>>select rowid into ret from test1 where rowid = rec.rowid for update nowait;

replace "test1" by your own table name.

dqmq

ASKER

Thank you both for the suggestions. Since both use essentially the same technique, I am splitting points with most going to sathyagiri. Partly that's for being first, but also because I don't particularly like the "when others then null" handler.

BTW, I am grading 'B' because neither solution addresses the case where all rows are locked and neither really selects randomly from the unlocked rows. But, I have since figured out those details. Also, I oversimplified and didn't mention that there really are more than 100 rows in the table, but only 100 are qualified for update based on a "where" condition. The truely complicating factor is when a row changes between the open cursor and the select for update such that it no longer qualifies for the update. So it is necessary to re-assert the where condition on the select for update and handle no_data_found similar to a locked row.

Acton Wang

for random, it is easy to implement and I forgot to put it in. I guess you already get it.