pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFIFO Queue Problems
7+ messages / 5 participants
[nested] [flat]
* FIFO Queue Problems
@ 2002-11-01 14:56 Chris Gamache <cgg007@yahoo.com>
2002-11-01 16:20 ` Re: FIFO Queue Problems Richard Huxton <dev@archonet.com>
2002-11-01 16:51 ` Re: FIFO Queue Problems Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 16:54 ` Re: FIFO Queue Problems Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 7+ messages in thread
From: Chris Gamache @ 2002-11-01 14:56 UTC (permalink / raw)
To: pgsql-sql
I'm having a race condition with a FIFO queue program that I've created...
CREATE TABLE fifo (
id serial,
data varchar(100),
status int4 DEFAULT 0
);
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
I can then
my $sql_get = <<EOS
SELECT data FROM fifo WHERE status=$processid
EOS
The problem occurrs when two of the processes grab the exact same row at the
exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if
setting the transactions to serializable would fix the problem since it occurrs
between different postmasters.
My experience with Views and Triggers is minimal. Would they even help? Could
someone suggest a better FIFO queue method?
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FIFO Queue Problems
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
@ 2002-11-01 16:20 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2002-11-01 16:20 UTC (permalink / raw)
To: cgg007@yahoo.com; pgsql-sql
On Friday 01 Nov 2002 2:56 pm, Chris Gamache wrote:
> I'm having a race condition with a FIFO queue program that I've created...
> I have a program that claims a row for itself
[code sample]
> The problem occurrs when two of the processes grab the exact same row at
> the exact same instant. It happens roughly 1 out of 1000 times. I'm not
> sure if setting the transactions to serializable would fix the problem
> since it occurrs between different postmasters.
>
> My experience with Views and Triggers is minimal. Would they even help?
> Could someone suggest a better FIFO queue method?
Check out SELECT FOR UPDATE - this locks the row and should do what you want.
This means that 1 time in 1000 your query will fail and need to be retried.
HTH
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FIFO Queue Problems
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
@ 2002-11-01 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 19:18 ` Re: FIFO Queue Problems Wei Weng <wweng@kencast.com>
2 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2002-11-01 16:51 UTC (permalink / raw)
To: cgg007@yahoo.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FIFO Queue Problems
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
2002-11-01 16:51 ` Re: FIFO Queue Problems Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-11-01 19:18 ` Wei Weng <wweng@kencast.com>
2002-11-01 19:47 ` Re: FIFO Queue Problems Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 19:56 ` Re: FIFO Queue Problems Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 7+ messages in thread
From: Wei Weng @ 2002-11-01 19:18 UTC (permalink / raw)
To: pgsql-sql
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@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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Wei Weng <wweng@kencast.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FIFO Queue Problems
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
2002-11-01 16:51 ` Re: FIFO Queue Problems Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 19:18 ` Re: FIFO Queue Problems Wei Weng <wweng@kencast.com>
@ 2002-11-01 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2002-11-01 19:47 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
Wei Weng <wweng@kencast.com> writes:
> Do you need to unlock the table fifo when you are done?
That happens at COMMIT.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FIFO Queue Problems
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
2002-11-01 16:51 ` Re: FIFO Queue Problems Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 19:18 ` Re: FIFO Queue Problems Wei Weng <wweng@kencast.com>
@ 2002-11-01 19:56 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2002-11-01 19:56 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
On Fri, Nov 01, 2002 at 14:18:37 -0500,
Wei Weng <wweng@kencast.com> wrote:
> Do you need to unlock the table fifo when you are done?
Locks only apply for the duration of a transaction. When you commit or
roleback the lock will be released.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FIFO Queue Problems
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
@ 2002-11-01 16:54 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2002-11-01 16:54 UTC (permalink / raw)
To: Chris Gamache <cgg007@yahoo.com>; +Cc: pgsql-sql
On Fri, Nov 01, 2002 at 06:56:30 -0800,
Chris Gamache <cgg007@yahoo.com> wrote:
>
> The problem occurrs when two of the processes grab the exact same row at the
> exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if
> setting the transactions to serializable would fix the problem since it occurrs
> between different postmasters.
I played with this a bit and it looks like you want transaction isolation
level serializable and have the application retry when necessary.
Doing a select for update won't work cleanly. What will happen is that
you will get 0 rows returned when there is a conflict and you will need
to detect that same as you would by changing the isolation level.
What happens in the select for update case is you first rewrite the select
to use order by and limit to pick the minimum row since you can't use
for update when returning an aggregate. Then when the matching row is
found there is a possible lock contention with another update. If this
happens then when the other update commits the locked row is rechecked and
will no longer match and gets removed from the list of rows returned (leaving
no rows).
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-11-01 19:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-01 14:56 FIFO Queue Problems Chris Gamache <cgg007@yahoo.com>
2002-11-01 16:20 ` Richard Huxton <dev@archonet.com>
2002-11-01 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 19:18 ` Wei Weng <wweng@kencast.com>
2002-11-01 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-01 19:56 ` Bruno Wolff III <bruno@wolff.to>
2002-11-01 16:54 ` Bruno Wolff III <bruno@wolff.to>
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