Re: Need to select and update with the same sql statement - Mailing list pgsql-php

Scott; List,
    Actually running with the "for update" on my SQL statement was one of
the things that I tried, didn't work...It was one that limited by race, so
it only happened on one out of 20/30 messages.  Lock works 100%.

/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 14:26
Subject: Re: [PHP] Need to select and update with the same sql statement


>
> Oh, I just thought of the better way than locking the whole table is to
> use a "select for update" on the row you want to lock.  I think that'll do
> what you want and without locking the whole table.  Of course, 20 lines of
> PHP code runs pretty fast, so unless you're handling lotsa traffic locking
> the table probably works fine too.
>
> On Wed, 13 Nov 2002, scott.marlowe wrote:
>
> > Yeah, the standard way of doing such things would be to create a parent
> > table with a serial ID, and a set of child tables that refer to that id.
> > then, you would do something like this:
> >
> > begin;
> > insert into parent (field1, field2, fieldn) values (....
> > select currval('seqforparenttable');
> > insert into child1 (pid,field1...) values
('idfrompreviouscurrval','data1'..
> > repeat for other children
> > commit;
> >
> > If it's a row that already exists, then your method is the way to do it.
> >
> > On Wed, 13 Nov 2002, David Busby wrote:
> >
> > > 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.
> > >
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >