Re: Theoretical Basis for SELECT FOR UPDATE

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 04 Oct 2005 03:20:02 GMT
Message-ID: <Cxm0f.6695$oc.119@newsread2.news.pas.earthlink.net>

vc wrote:

> 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.

No; then the constraint has been changed - and is more complex. You no longer have a zero-sum system unless you account for 'money in transit' that is not in any account. Further, since the money here is added to one account before being debited to the other, there's a negative 'cash in transit' value - 10 currency units more have been deposited than withdrawn.

> 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.

No; the sequence is defined as 'do all the operations; check all the constraints'.

> 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.

They leave you with the option of having complex constraints that are not violated at any point. The alternative leaves you with a complex constraint that is violated at the intermediate point.

>>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).

TTM currently supports both multiple-assignment and transactions. The main difference is precisely in the area of which constraints can be seen to be enforced. The constraint in the case with SELECT is more complex than the constraint in the case without.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Received on Mon Oct 03 2005 - 22:20:02 CDT