Re: Theoretical Basis for SELECT FOR UPDATE

Gene Wirchenko wrote:
> On 30 Sep 2005 08:32:12 -0700, "vc" <boston103_at_hotmail.com> wrote:
>
> >Tony Andrews wrote:
>
> [snip]
>
> >> It means that after any executable statement that changes the data, the
> >> data is is a valid state - i.e. each executable statement IS a
> >> transaction. The TTM therefore requires it to be possible to combine
> >> multiple operations into a single statement. i.e. instead of:
> >>
> >> update account set bal=bal+10 where ac_no=123;
> >> update account set bal=bal-10 where ac_no=456;
> >> commit;
> >>
> >> TTM would have something more like:
> >>
> >> update account set bal=bal+10 where ac_no=123,
> >> update account set bal=bal-10 where ac_no=456;
> >>
> >> (Note the comma rather than semi-colon on line 1).
>
> >But that's just a syntactical difference because S1;S2;commit; is
> >another way of saying S,S; (or vice versa).
>
> No, it is not. Imagine a statement in between the two updates
> that, say, prints data from the transaction in progress. Then,
> imagine the transaction being rolled back. There is a potential for
> loss of consistency.

It depends on how the comma delimiter is implemented. See below.

>
> >> The important difference is that in the TTM version there is no time
> >> when even this transaction can see data that is in an inconsistent
> >> state.
>
> >Time is immaterial. How do two atomic writes with a pause in between,
> >however small, can avoid inconsistency slipping in ?
>
> Disallow the access between the two statements, and how can you
> lose constistency? Allow it, and the barndoor is open.

But any update in the database already locks the rows being updated. What is gained by the comma operator ? If you mean that concurrent select operations will be blocked during a set of comma delimited operators execution, then in fact you convert all database transactions from being concurrent to serial. If I misunderstood you, please elaborate.

>
> Gene Wirchenko
Received on Fri Sep 30 2005 - 23:39:26 CEST