Re: Theoretical Basis for SELECT FOR UPDATE

Jonathan Leffler wrote:
> 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.

What is the more complex constraint for the u,s,u; sequence then ?

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

All that is very nice, but that's a mental picture in your head. How is it implemented in the database ?

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

Are you saying that update,update; is treated as a single atomic operation in which case the constraints needn't be deferrable, or you mean something completely different ? If you mean the former, how do you imagine such concatenated update might be implemented without dramatic negative impact on concurrency and therefore on performance ?

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

Please explain how the complex constraint is not violated at any point in a multistatement comma separated expression.

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

Please explain the u,s,u; case.

Thanks.

>
>
>
> --
> 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 Tue Oct 04 2005 - 16:08:35 CEST