Re: Get the first non-locked record.

From: Dmitry E. Loginov <dmitry_loginov_at_mtu.ru>
Date: Tue, 20 Nov 2001 23:50:38 +0300
Message-ID: <9tefqv$1bht$1@gavrilo.mtu.ru>

You can try following thing:

declare
 RESOURSE_BUSY exception;
 pragma exception_init(RESOURSE_BUSY,-54);  cursor C_MAIN is SELECT Id FROM Item WHERE Process IS NULL ORDER BY Processing_Order;
 cursor C_LOCK(p_id Item.ID%type) is SELECT id from ITEM    where ID=p_id and Process IS NULL
   for update nowait;
 R_LOCK C_LOCK%rowtype;
begin
 for I in C_MAIN loop
   begin

     open C_LOCK(i.id);
     fetch C_LOCK into R_LOCK; -- Try to lock record, exception will be
raised for the locked recs
     if C_LOCK%found then
       -- Record locked for you; Do something for this one.
       -- Place your code here, for example:
       update Item set Process='processed' where current of C_LOCK;
     end if;
     close C_LOCK;

   exception
    when RESOURSE_BUSY then

• Just skip locked record if C_LOCK%isopen then close C_LOCK; end if; when OTHERS then if C_LOCK%isopen then close C_LOCK; end if; raise; end; end loop; end;

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news: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
Received on Tue Nov 20 2001 - 14:50:38 CST