Re: several process collide when updating one table

I believe this to be a bad solution to the problem.

1. When do you execute the allocation to processes? Often in a situation like this, the table is acting as a queue and new rows are being written all the time.
2. You need to synchronise the update with the number of processes.
3. Changing the number of processes at run time becomes expensive because of the need to do the update.
4. The processes need to identify themselves in some way.
5. If one of the processes dies, some rows will never be processed.
6. If each process takes one row at a time, the system is naturally load balanced. With your solution, it is possible for a backlog to build up on one process.

In summary, while what you propose could work, it is much more complicated and therefore more fragile.

"Colin McKinnon" <colin_at_EditMeOutUnlessYoureABot.wew.co.uk> wrote in message news:3ss5k9.cnk.ln_at_Lonmay.wew.co.uk...
> M. Weiss <weiss_at_virtual7.de> wrote in message
> news:3b65ac18$1_at_netnews.web.de...
> > 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:
> >
> The first thing to do is to identify whether you need to update records
 one
> at a time. Using SQL to update a set of records is far more efficient and
 a
> LOT faster. You'll also be able to use locking to solve your problem.
>
> If you can enumerate your processes (i.e. give them each a unique
 sequential
> number starting at 0) then you can preflag all the records for each
 process
> (can't remember how oracle does modulo - using % in below) e.g.
>
>
> UPDATE myrecords SET processor = rownum % $max_number_of_proc;
>
> Then instead of selecting all records, select those which have been
> preflagged for the process:
>
> SELECT * FROM myrecords WHERE procesor = $proc_enum;
>
> Preflagging a single record isn't going to much help.
>
> HTH
>
> Colin
>
>
Received on Wed Aug 01 2001 - 05:35:33 CDT