Do you need to unlock the table fifo when you are done?
On Fri, 2002-11-01 at 11:51, Tom Lane wrote:
> Chris Gamache <cgg007(at)yahoo(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Wei Weng <wweng(at)kencast(dot)com>