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" <scott.marlowe@ihs.com>
To: "David Busby" <busby@pnts.com>
Cc: <pgsql-php@postgresql.org>
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.