From: Tom Lane <tgl@sss.pgh.pa.us>
To: cgg007@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: FIFO Queue Problems
Date: Fri, 01 Nov 2002 11:51:01 -0500
Message-ID: <26900.1036169461@sss.pgh.pa.us> (raw)
In-Reply-To: <20021101145630.50886.qmail@web13801.mail.yahoo.com>
References: <20021101145630.50886.qmail@web13801.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, cgg007@yahoo.com
Subject: Re: FIFO Queue Problems
In-Reply-To: <26900.1036169461@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox