Re: Theoretical Basis for SELECT FOR UPDATE

"vc" <boston103_at_hotmail.com> wrote in message news:1128355148.877601.136760_at_o13g2000cwo.googlegroups.com...

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

Let's change it just a bit, as follows:

set transaction;
update account set bal=bal+10 where ac_no=123; select sum(bal) from account into memory_variable; update account set bal=bal-10 where ac_no=456; commit;

What's the problem? The problem is that, at the moment when sum(bal) has been copied to
memory_variable, the database was in an inconsistent state. Not an inconsistent state from
a database operations point of view, but definitely an inconsistent state from an accountant's point of view.

You see, from a double entry bookkeeping point of view, the first update, which debits account 123 with a certain amount, and the second update, which credits account 456 with the *same amount* (emphasis added), are a single atomic transaction. They are not "unrelated operations" at all. And while the "set transaction" and the "commit" make the combined transaction atomic from the point of view of any other transaction that looks at the database, it's not atomic from the point of view of the process itself.

I *think*, although I'm not sure, that the goal of the TTM authors is to ensure that a single transaction must be atomic, both from the perspective of anybody else looking at the database, *and also* from the persperctive of the process itself. In other words, whenever control is returned to the program, the database is in a consistent state. If I've got that right, then it's a radical departure.

Consistent semantically, not just formally.

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

If there were a database constraint that says that, at the end of every transaction, sum(bal) must equal zero,
then the difference between

update x; update y;

and

update x, update y;

is that the semicolon is where the constraint would be imposed. It would be like what I think of as "a constraint
deferred until end of transaction". So, in the example we are following, the case of
update x; update y would fail, because the constraint would be chacked at a time when it is not valid.

At least, this is my understanding of what the dialogue has need trying to say. I may, of course, have gotten it wrong. Received on Tue Oct 04 2005 - 08:49:48 CEST