Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> vc wrote:
> > 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.
>
> No, the comma operator has nothing to do with other, concurrent
> transactions really. It has to do with making the transaction atomic
> *from the perspective of the session performing the operation*. Gene
> has already given a good example about printing information half-way
> through the transaction; that might look like this:
>
> update account set bal=bal+10 where ac_no=123;
> call print_total_balance_for_all_accounts;
> update account set bal=bal-10 where ac_no=456;
> commit;

OK, let's take a closer look at the transaction example.

update account set bal=bal+10 where ac_no=123; call print_total_balance_for_all_accounts; update account set bal=bal-10 where ac_no=456;

... can be expressed in SQL as:

(1)
update account set bal=bal+10 where ac_no=123; select sum(bal) from account;
update account set bal=bal-10 where ac_no=456;

If the operations are 'deposit' money to account 123 and 'withdraw' money from account 456 (unrelated operations, for whatever reason grouped together), then I do not see a problem with sequence (1).

If the operation is 'transfer' money from 123 to 456, then, of course, we have a bug. Why would anyone do that for a transfer operation instead of:

(2)
update account set bal=bal+10 where ac_no=123; update account set bal=bal-10 where ac_no=456; select sum(bal) from account;

>
> The TTM version would make it impossible to print the intermediate
> total (which would be 10 higher than it should be).

Assuming as you've said that the comma delimited sequence is unrelated to other concurrent transactions, as far as I understand, the TTM version is nothing but a bit of syntactic sugar on top of the usual statement sequence:

E.g. 'update x; update y; commit;' is the same as TTM's 'update x, update y;' where TTM substitutes ',' for ';', and ';' for the commit.

Besides, having the TTM sequence cannot prevent one from using the 'incorrect' sequence (1).

Please explain what I am missing.

Thanks. Received on Mon Oct 03 2005 - 17:59:08 CEST