Re: several process collide when updating one table

I see two possible solutions to this problem.

1. create a broker process that each process will interogate for a new row to work on. That way only one process is updating the table to hand out work.
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;

You might want to use the nowait clause and thus retry if two processes collide on the same row. One will have an exception throw resource busy (I think)

Good luck
Scott.

"M. Weiss" <weiss_at_virtual7.de> wrote in message news:3b65ac18$1_at_netnews.web.de...
> Hi people,
>
>
> I got a tiny little problem with perl, working on an ORA-DB. I start
 several (up
> to 8) perl-processes which all work on one table. Each process among them
 shall
> be able to read a row from the table, mark it as processed and work down
 what
> ever to do. First I tried to do it the following way:
>
>
> 1. select the next item from table
> 2. update this row to mark it as processed.
>
>
> Ok... the problem which occurred was, that during selecting and updating
 from
> one process, a second process selected the same item and did also his job
 with
> that row. So I decided to go another way:
>
>
> 1. update the next item from table with an unique id (pid of perl-process)
 using
> the following statement:
> "update test set processed=[pid] where processed=0 and rownum between 0
 and 1"
>
>
> 2. select all information from this row:
> "select * from test where processed=[pid]"
>
>
> 3. and update it to be done:
> "update test set processed=1 where processed=[pid]"
>
>
> Well... the number of collisions was hardly reduced, but in the end there
 still
> remained some collisions.
>
>
> Now my question is: What to do in this case... ? Doesn't Oracle manage
 those
> update-statements in a sequential way ?
>
>
> Hopin' for any ideas...
>
>
> Regards.
> Marcus
> --
> __________________________________________________________
> News suchen, lesen, schreiben mit http://newsgroups.web.de
Received on Mon Jul 30 2001 - 14:04:22 CDT