Thread: Need to select and update with the same sql statement
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
/B
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.
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.
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.
>
>
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
>
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
> >
On Wed, Nov 13, 2002 at 15:26:42 -0700, "scott.marlowe" <scott.marlowe@ihs.com> wrote: > > 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. This came up in a discussion recently and it really isn't better. If you do this you have to worry about the select for update returning zero rows (even with the limit 1 clause) and retry the query if it does.