Re: Theoretical Basis for SELECT FOR UPDATE

vc wrote:
> Tony Andrews wrote:
> [...]
> > It is not necessarily the only way - for example we could perhaps add
> > "AND EXISTS (SELECT ...)" to the WHERE clause of each subsequent
> > operation to ensure that the data is still there and has not been
> > changed - i.e. optimistic rather than pessimistic locking.
> >
>
> The usual technique is to use an additional column containing a
> timestamp so that one did not need to check all the column values.
> Besides, the timestamp can be used to prevent updates during some
> resonable period, like a couple of second. Clearly, this kind of
> locking is advisory in nature.

Agreed.

> > I believe the TTM is against transactions also, requiring integrity to
> > be maintained at statement boundaries. If that were so, there would be
> > no requirement for SELECT FOR UPDATE.
>
> This statement is unclear. Please elaborate on "integrity to be
> maintained at statement boundaries". The only way for a set of
> transactions to behave in a consistent way is to be SERAILIZABLE (not
> to be confused with serial execution).

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

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.

That is my understanding of the TTM position, I hope I have it right. Received on Fri Sep 30 2005 - 14:59:08 CEST