Re: FIFO Queue Problems

Chris Gamache <cgg007@yahoo.com> writes:
> I have a program that claims a row for itself

>   my $processid = $$;
>   my $sql_update = <<EOS;
>     UPDATE fifo
>       set status=$processid
>     WHERE id = (SELECT min(id) FROM fifo WHERE status=0);
> EOS

> The problem occurrs when two of the processes grab the exact same row at the
> exact same instant.

Probably the best fix is to do it this way:
BEGIN;LOCK TABLE fifo IN EXCLUSIVE MODE;UPDATE ... as above ...COMMIT;

The exclusive lock will ensure that only one process claims a row
at a time (while not preventing concurrent SELECTs from the table).
This way you don't need to worry about retrying.
        regards, tom lane