pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Busby <busby@pnts.com>
To: scott.marlowe <scott.marlowe@ihs.com>
Cc: pgsql-php@postgresql.org
Subject: Re: Need to select and update with the same sql statement
Date: Wed, 13 Nov 2002 13:45:54 -0800
Message-ID: <02a401c28b5e$0af80bd0$4000000a@busbydev> (raw)
References: <Pine.LNX.4.33.0211131300360.32021-100000@css120.ihs.com>

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.




view thread (7+ messages)  latest in thread

Message-ID: <02a401c28b5e$0af80bd0$4000000a@busbydev>
Permalink:  ../02a401c28b5e$0af80bd0$4000000a@busbydev/
Also on:    postgresql.org/message-id/02a401c28b5e$0af80bd0$4000000a@busbydev

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: busby@pnts.com, scott.marlowe@ihs.com
  Subject: Re: Need to select and update with the same sql statement
  In-Reply-To: <02a401c28b5e$0af80bd0$4000000a@busbydev>
  References: <Pine.LNX.4.33.0211131300360.32021-100000@css120.ihs.com> <02a401c28b5e$0af80bd0$4000000a@busbydev>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox