Tom's suggested solution worked GREAT in 7.2.x ... I tried taking the plunge
into 7.3.1 tonight. In 7.3.1, when my FIFO queue program goes to grab a row,
TWO processes grab the same row, almost without fail. I even changed my locking
statement to the dreaded
LOCK TABLE fifo IN ACCESS EXCLUSIVE MODE;
it still exhibits the same behavior. I've tried variations on the theme, but I
can't seem to figure it out. I'm stumped!
The postgresql configuration is as identical (IMO) as I could possibly make it
considering the changes from 7.2 to 7.3. I can't imagine a config option would
control something so basic. I can't find any reference to it in the 7.3 docs,
and my tired eyes did not pick any fixes remotely pertaining to this type of
locking problem in the HISTORY file.
I'm (sadly) switching back to 7.2 until we can figure this out.
CG
>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
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com