Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> 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.

Is the sequence

update,select,update;

legal ? If so, you can 'see' the constraint violation.

Moreover, I bet that 'update,update; ' has to use the same mechanism behinds the scenes, namely deferrable constraints (what else ?), in order to be possible.

All in all, the comma separated statements do not appear to bring much new to the table in comparison to the commit-delimited old style transactions except for perhaps more concise notation.

> If you want to know the full reasoning behind the TTM position I must
> refer you to the book!

I read the book, but did not find a substantial difference between the old style transaction and the new multi-statement one (see above).

Thanks. Received on Mon Oct 03 2005 - 20:04:18 CEST