Re: using database for queuing operations? - Mailing list pgsql-general

SELECT ... FOR UPDATE can and will produce a race condition if multiple
back-ends attempt to access the same row at the exact same time. If you don't
believe me, ask my gray hairs! :) Instead use

LOCK TABLE your_table IN EXCLUSIVE MODE;

Here's what I do:

BEGIN;
LOCK TABLE your_table IN EXCLUSIVE MODE;
UPDATE your_table SET claimed_by = 'unique_processor_id', status = 'IN PROCESS'
WHERE serial_pkey = (SELECT min(serial_pkey) FROM your_table WHERE status =
'UNPROCESSED')
COMMIT;

Then I can

SELECT * FROM your_table WHERE claimed_by = 'unique_processor_id' AND status =
'IN PROCESS';

and I can be sure my multiple processors get one and only one row, marked for
processing by one processor. The statements in the LOCKed transaction are
completely serialized, but the subsequent selects are unencumbered by a lock.

Many thanks to Tom Lane for this solution. It has worked like a charm for two
years and counting.

CG

--- Jeff Amiel <jamiel@istreamimaging.com> wrote:

> .....or instead change the logic to:
>
> So you:
>
> 1.  select for update, with the criteria outlined
> 2.  Check the state (again) to see of we had that particular race condition.
> 3.  If already processed or in processing, somebody else must already be
> working on it....go back to step 1
> 4,  change the state
> 5.  process the image
> 6.  delete.
> 7  go to step 1.
>
>
>
> change the state, then process the image....then delete.
>
>
>
> Jeff Amiel wrote:
>
> > Although....it wont really solve the race condition issue...
> > you can still have a point where 2 processes select the same
> > record...one gets the 'for update' lock on it and the other one just
> > waits for it...
> > Regardless of the 'state', once that lock releases, the second process
> > will grab it.
> > In my world I have a 'main' process that selects all the records that
> > currently meet the criteria I am interested and them 'parse' them out
> > to the sub-processes via unique id.
> >
> > Dont know if this helps....
> > Jeff
> >
> >
> >
> > Mark Harrison wrote:
> >
> >> Jeff Amiel wrote:
> >>
> >>> Add a column to the nameq table designating the 'state' of the image.
> >>> Then your logic changes to "select * from nameq where serial =
> >>> (select min(serial) from nameq) and state="UNPROCESSED" (or whatever)
> >>> So you select for update, change the state, then process the
> >>> image....then delete.
> >>
> >>
> >>
> >> Thanks Jeff, I think that will work perfectly for me!
> >>
> >> Cheers,
> >> Mark
> >>
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>




_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com