[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

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.

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

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