Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C84794760D5 for ; Fri, 1 Nov 2002 11:50:57 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 215044760CB for ; Fri, 1 Nov 2002 11:50:57 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id gA1Gp2hR026901; Fri, 1 Nov 2002 11:51:02 -0500 (EST) To: cgg007@yahoo.com Cc: pgsql-sql@postgresql.org Subject: Re: FIFO Queue Problems In-reply-to: <20021101145630.50886.qmail@web13801.mail.yahoo.com> References: <20021101145630.50886.qmail@web13801.mail.yahoo.com> Comments: In-reply-to Chris Gamache message dated "Fri, 01 Nov 2002 06:56:30 -0800" Date: Fri, 01 Nov 2002 11:51:01 -0500 Message-ID: <26900.1036169461@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/11 X-Sequence-Number: 10153 Chris Gamache writes: > I have a program that claims a row for itself > my $processid = $$; > my $sql_update = < 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