Re: several process collide when updating one table

From: Keith Boulton <kboulton_at_ntlunspam-world.com>
Date: Tue, 31 Jul 2001 09:56:44 +0100
Message-ID: <Pju97.39777$vN4.237727@news11-gui.server.ntli.net>

>
> 2) use a select statment of the form
> select * from table where rownum = 1 and beingworkedon is null for
> update;
> Update the record using the rowid and process the row;
>

This will still cause collisions because you cannot see the uncommitted changes of another user. It would be better to:

main loop

    open a cursor for select <primary key> from <table> where processed = 'N'

    cursor loop

        fetch a row
        select <columns> from <table> where <primary key> = <value fetched
above> for update nowait
        on error "ORA-00054: resource busy and acquire with NOWAIT
specified" loop
        otherwise close cursor and exit

   end cursor loop
  process the row including update processed to null or N or deleting the row
  commit to save changes and release the lock end loop

This might best be written as a pl/sql procedure.

Note that if there are only a small proportion of rows awaiting processing you can build an index on the processed flag and use NULL to mean processed. The index then only contains unprocessed items and can be very small. Received on Tue Jul 31 2001 - 03:56:44 CDT