Thread: FIFO Queue Problems

FIFO Queue Problems

From
Chris Gamache
Date:
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
fifoWHERE 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/


Re: FIFO Queue Problems

From
Richard Huxton
Date:
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


Re: FIFO Queue Problems

From
Bruno Wolff III
Date:
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).


Re: FIFO Queue Problems

From
Tom Lane
Date:
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


Re: FIFO Queue Problems

From
Wei Weng
Date:
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>



Re: FIFO Queue Problems

From
Bruno Wolff III
Date:
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.


Re: FIFO Queue Problems

From
Tom Lane
Date:
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