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

What's the race in the SELECT FOR UPDATE?

BTW, this is one nice thing about Oracle... it comes with a built-in
queuing mechanism. It would probably be worth trying to write a generic
queuing system and stick it in Gborg.

Incidentally, Oracle also supports user-named locks, which would
probably make this easier to do. LOCK TABLE works, but it's more brute
force than is needed. Unfortunately, I don't see a way to simply add
such a thing onto PostgreSQL without adding it to the core.

On Mon, Sep 20, 2004 at 02:17:38PM -0700, Chris Gamache wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"