Re: Get the first non-locked record.

There is an undocumented SKIP LOCKED option used with "select for update" in the Advanced Queue packages. You could try that:

select rowid , id
from tableX
for update
skip locked
order by processing_order desc;

Then rollback, then select the first row returned for update (nowait, in case
something else has slipped in between
the select and the rollback)

Unfortunately if you try to use
  where rownum = 1
the rownum take precedence
over the SKIP, so you get the
first row before Oracle checks
to see if it is locked.

You may find a better way of doing
this, as it would be rather expensive
on rollback if the table was allowed to
get fairly large.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Brian Tkatch wrote in message <3bfaab87.103696187_at_news.alt.net>...


>Imagine I have a table:

>

>Item

>======

>Id (PRIMARY KEY)

>Processing_Order (NOT NULL, UNIQUE)

>Process (UNIQUE)

>

>The table lists items that are processed in order.

>

>More than one process will try to take an item at the same time. When

>a process gets an item, it places its name there, and deletes the

>record when the process finishes with the item.

>

>The way to get the next item would be.

>

>SELECT Id FROM Item WHERE Process IS NULL ORDER BY Processing_Order;

>

>I'm assuming it should be SELECTed FOR UPDATE so as to get the record

>it actually thinks it has. But how does the second process skip the

>record the first one has currently LOCKED (if it didn't UPDATE it

>yet)? If I don't say NOWAIT it sits there, and if I do, it returns an

>error rather than going to the next record or returning NULL.

>

>Brian