Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E66CD475F7C for ; Wed, 13 Nov 2002 16:49:31 -0500 (EST) Received: from seapdc01.PNTS (ip63.c216.blk1.bel.nwlink.com [209.20.216.63]) by postgresql.org (Postfix) with ESMTP id 921BA475EE2 for ; Wed, 13 Nov 2002 16:49:28 -0500 (EST) Received: from busbydev (BUSBY-DEV [10.0.0.64]) by seapdc01.PNTS with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id VLKPFTKJ; Wed, 13 Nov 2002 13:54:47 -0800 Message-ID: <02a401c28b5e$0af80bd0$4000000a@busbydev> From: "David Busby" To: "scott.marlowe" Cc: References: Subject: Re: Need to select and update with the same sql statement Date: Wed, 13 Nov 2002 13:45:54 -0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/58 X-Sequence-Number: 1640 Scott,; List, The transaction didn't work (for some reason) What I ended up having to do (which isn't that bad really) is to lock the table exclusively while doing the read/write. So my code looks like begin; lock table "chunks" exclusive mode; select * from "chunks" order "lastchecked" limit 1; # Do some PHP code here, couple 20 lines or so if ($success) pg_exec("update "chunks"; commit;"); else pg_exec("rollback;"); This seems to work and removes the race condition. As a note, I tried the incantation that was provided by Scott below (thx) and some other modifications to it as well. All still had the race (though not as bad) but the above code eliminated the condition entirely. Don't know about it's performance implications. /B ----- Original Message ----- From: "scott.marlowe" To: "David Busby" Cc: Sent: Wednesday, November 13, 2002 12:03 Subject: Re: [PHP] Need to select and update with the same sql statement > On Wed, 13 Nov 2002, David Busby wrote: > > > List, > > I need to do a command like: > > > > select * from "table" where "id"=54; update "table" set "col"=value > > where "id"=just selected id > > > > Is that possible? How would I work that into a StoredProcedure? I'm > > getting a race condition where two+ clients are asking for data but getting > > the same record, (each record is a datachunk for a distributed client). > > This results in each client working on the same data, not good. Any ideas? > > I'm posting to the php/sql list cause the clients ask for the datachunk via > > SOAP request that is processed via PHP. Any assistance would be great > > It's time for transactions! > > You should be able to do this in a transaction: > > (pg_exec the SQL code here) > begin; > select * from table where "id"=54; > (assign the id to a var $id here) > update "table" set "col"=$value where "id"=$id; > end; > > This should happen in such a way that other users can't see what's > happening until it's done.