Subject: Re: FIFO Queue Problems
From: Wei Weng <wweng@kencast.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <26900.1036169461@sss.pgh.pa.us>
References: <20021101145630.50886.qmail@web13801.mail.yahoo.com>
	<26900.1036169461@sss.pgh.pa.us>
Content-Type: text/plain
Organization: 
Message-Id: <1036178317.23296.2.camel@Monet>
Mime-Version: 1.0
Date: 01 Nov 2002 14:18:37 -0500
Content-Transfer-Encoding: 7bit

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>