Chris Gamache <[EMAIL PROTECTED]> 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
