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