I didn't hear anything back on this. Does someone have a little time or
a pointer to a good resource that will clarify the use of the SELECT FOR
UPDATE syntax?

Tim

-------- Original Message --------
Subject: Re: haven't forgotten about you...
Date: Mon, 07 Aug 2000 16:08:29 -0700
From: Tim Perdue <tperdue@valinux.com>
To: Benjamin Adida <ben@mit.edu>
CC: scrappy@hub.org
References: <B5934C52.708E%ben@mit.edu>

Benjamin Adida wrote:
> 
> on 7/13/00 10:39 AM, Tim Perdue at tperdue@valinux.com wrote:
> 
> > I wouldn't really worry about that right now.
> 
> Oh okay, I thought this was an emergency because you were looking at
> switching possibly to another DB. I hope you won't make the Oracle jump!
> 
> > I *would* like to see an article on transactions though.
> 
> Okay, fair enough. I'll get working on that ASAP.


Are you going to do this?

I've been recently asked to write an article for Linux Journal about
"Deploying a Serious Application With PHP". I'd like to use postgres for
a "serious" application rather than MySQL, but I would like to see this
tutorial to understand the nuances first. (as I mentioned, I don't think
I understand the SELECT * FOR UPDATE syntax)

Tim


-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Thu, 10 Aug 2000 20:36:14 -0700
Tim Perdue <tperdue@valinux.com>
[Fwd: Re: haven't forgotten about you...]
Ben Adida wrote:
> begin transaction
> select balance from accounts where account_id=2 for update
> 
> will select the balance and lock the row for account #2
> You can then perform some math on the balance, and do something like:
> 
> update accounts set balance= $new_balance where account_id=2
> end transaction
> 

Great - I assume end transaction is going to do a commit. If you don't
do an end transaction and you don't issue a rollback... I assume it
rolls back?

This is pretty slick - over the last month or so I've come up with about
8 different places where I really wish I had transactions/rollbacks on
SourceForge. Also running into lots of places where I really, really
wish I had fscking subselects...

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Thu, 10 Aug 2000 21:13:52 -0700
Tim Perdue <tperdue@valinux.com>
Re: [Fwd: Re: haven't forgotten about you...]
At 09:13 PM 8/10/00 -0700, Tim Perdue wrote:
>Ben Adida wrote:
>> begin transaction
>> select balance from accounts where account_id=2 for update
>> 
>> will select the balance and lock the row for account #2
>> You can then perform some math on the balance, and do something like:
>> 
>> update accounts set balance= $new_balance where account_id=2
>> end transaction
>> 
>
>Great - I assume end transaction is going to do a commit. If you don't
>do an end transaction and you don't issue a rollback... I assume it
>rolls back?

It is best not to assume, and to do so explicitly.  I base this on the
theory that you ought to know what your code does, and what it did to
get there.

(end transaction is indeed "commit", you can use "commit" if you prefer).

>This is pretty slick - over the last month or so I've come up with about
>8 different places where I really wish I had transactions/rollbacks on
>SourceForge.

Yes.  That's the realization one comes to when working on complex database
apps.

>Also running into lots of places where I really, really
>wish I had fscking subselects...

As someone who uses Oracle, I feel the same way, but Postgres doesn't
make me feel that way nearly as often as MySQL would :)

(and actually, Oracle's outer join syntax requires subselects if you are
to mix and match inner and outer joins and control the priority of execution
order - which the vastly superior SQL92 syntax solves in a reasonably 
elegant manner).



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 10 Aug 2000 21:34:50 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [Fwd: Re: haven't forgotten about you...]
Tim Perdue wrote:

> I didn't hear anything back on this. Does someone have a little time or
> a pointer to a good resource that will clarify the use of the SELECT FOR
> UPDATE syntax?

Uggh, just when I finally had some time to answer :) Let me attempt to answer
it anyways. SELECT for UPDATE is a means of explicitly locking a row for
later updating within the same transaction. For example (this is a simplified
example):

begin transaction
select balance from accounts where account_id=2 for update

will select the balance and lock the row for account #2
You can then perform some math on the balance, and do something like:

update accounts set balance= $new_balance where account_id=2
end transaction

Thus, this construct makes this safe in a multi-client environment. Even if
two clients perform these actions simultaneously, the "for update" will
guarantee that one of the two locks that row at the select statement level,
and the second waits until the first transaction commits (at which point the
lock is transparently released).

Note that if you *didn't* have the "for update", no lock would be acquired at
the select level, and you could run into a race condition where two processes
grab the same balance from the account, and independently update that amount,
thereby losing the effect of one of those updates (and  probably robbing you
of money).

Note also that the lock acquired is row-level, which means that if two
processes are updating two different accounts, both processes can proceed
without blocking each other. This will thus behave not only correctly, but as
efficiently as possible.

I hope this clears things up. I am writing that article about transactions
and locking, it's on its way, I swear.

-Ben


Fri, 11 Aug 2000 00:37:08 -0400
Ben Adida <ben@openforce.net>
Re: [Fwd: Re: haven't forgotten about you...]
Tim Perdue wrote:

> Great - I assume end transaction is going to do a commit. If you don't
> do an end transaction and you don't issue a rollback... I assume it
> rolls back?

Yes, when I said end transaction, I meant commit.

The precise behavior you're inquiring about is dependent on your web server
/ driver setup. In AOLserver's Postgres driver, if a database handle is
released when a transaction is still open, the transaction is rolled back.
I can imagine other drivers behaving differently, but implicit commits
sound very dangerous to me.

> This is pretty slick - over the last month or so I've come up with about
> 8 different places where I really wish I had transactions/rollbacks on
> SourceForge. Also running into lots of places where I really, really
> wish I had fscking subselects...

Yes, Postgres is definitely pretty slick...

-Ben


Fri, 11 Aug 2000 00:54:09 -0400
Ben Adida <ben@openforce.net>
Re: [Fwd: Re: haven't forgotten about you...]