Hi Tom,

Thanks for all the great info! I have a question regarding SELECT FOR UPDATE. This is a slightly contrived example to specifically look at this situation but hopefully it makes sense.

Here's the example set up..

create table t (
  id         number      primary key,
  processed  varchar2(1) not null
);

insert into t values (1, 'N');
insert into t values (2, 'N');
insert into t values (3, 'Y');
insert into t values (4, 'Y');
insert into t values (5, 'N');
commit;

Statuses:
N --> Not Processed
P --> Processing
C --> Complete

Workers need to get the next unprocessed piece of work and should get the 'oldest' (i.e. the one with the lowest ID). There's no strict rules on order of processing just that they should attempt to get the lowest ID first, to avoid the case where picking them randomly happens to leave an unprocessed row for a long period.

So, when accessing the above table, the first client should get the row with ID 1, the second --> 2 and the third --> 5.

When a session gets a row it immediately updates it to indicate that it is processing it and commits the new status before carrying out any 'real' work.

Using this SQL (or even just a straight update to status 'P'), I can ensure that clients get the next row to process correctly (the next unprocessed row by ID).

select id
from t
where id =
(
  select id
  from (
    select id
    from t
    where processed = 'N'
    order by id
  )
  where rownum < 2
)
for update
/

The problem is that other sessions will block until the first session commits the status update. Although this locking time is small it exists and the goal is to reduce this time blocked to be as small as possible.

I've recently come across the undocumented SKIP LOCKED clause so I thought I'd give it a go to see whether it could help out. Trying something like this..

select id
from t
where id =
(
  select id
  from (
    select id
    from t
    where processed = 'N'
    order by id
  )
  where rownum < 2
)
for update skip locked

...the first session receives the row with ID 1 but the second row returns no rows. This makes sense as the inner select with order by and rownum will still be selecting the same row so we find it locked and skip it. So this doesn't achieve the goal.

Changing this to be

select id
from (
  select id
  from t
  where processed = 'N'
  order by id
)
for update skip locked
/

will allow each session to find all rows that are available when it attempts the select, but will lock all of these rows not just the first. The workers need to get one row at a time. Putting the rownum back in leads to the expected ORA-02014.


So back to the drawing board... Another option (I found here http://www.ixora.com.au/q%2Ba/0010/17094208.htm ) uses two loops in PL/SQL with pseudo code like

  cursor1 --> select rowids with status 'N' ordered by ID
  loop
    cursor2 --> select row with rowid
       from cursor1
       where rowid=cursor1.record.rowid
       -- Need to check that row is still unprocessed.
       and status = 'N'
       for update nowait

    If a row is found then we have it and it is now locked,
    return and allow client to process work.
    If no row found then proceed to next row in outer cursor.
  end loop

This seems to do the job in that other sessions will not block but will try all rows in status 'N' in the correct order until they either find one they can lock or until they run out of rows.

This is at the expense of potentially higher complexity, and also potentially more resource usage as rather than blocking, many sessions might churn through the same rows at the head of the list until they find one that is available.


Is this PL/SQL approach a valid approach? Is there a simpler approach? I've looked over the PL/SQL version and couldn't think of any issues but may well be missing something.

Currently I'm thinking that I'd stick to the straight SQL approach and live with the small serialisation rather than the potential increased resource usage and complexity of the second.

Many thanks in advance. Hope this wasn't too wordy - I'm always having issues with the @concise@ key on my keyboard...

Dave