Re: Theoretical Basis for SELECT FOR UPDATE

vc wrote:
> Please explain what I am missing.

Well, I'm not trying to justify the TTM position, just explain it as I understand it! You are right, nothing prevents you from writing the two updates as separate statement (and so separate transactions) - unless there are constraints in place that would be violated by doing so. TTM also proscribes deferred constraint checking (in fact, the term becomes meaningless if there are no multi-statement transactions).  Suppose there is a constraint to the effect that the sum of all account balances must always be zero. In the multi-statement transaction:

update account set bal=bal+10 where ac_no=123; -- Constraint is currently violated (must be a deferred constraint) update account set bal=bal-10 where ac_no=456; commit;
-- Constraint is met

In the TTM version:

update account set bal=bal+10 where ac_no=123, update account set bal=bal-10 where ac_no=456; -- Constraint is met

Again, there is no place in the transaction where you could potentially see inconsistent data.

Of course, your objection about referencing inconsistent data between steps 1 and 2 of the multi-statement transaction being a bug still applies.

If you want to know the full reasoning behind the TTM position I must refer you to the book! Received on Mon Oct 03 2005 - 12:12:19 CDT