Re: Theoretical Basis for SELECT FOR UPDATE

David Cressey wrote:
> "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.

Your argument may be valid if the new comma separated syntax disallows selects so that we cannot see 'intermediary' results, but does it ? It's unclear from TTM if it does. If it does not, then there is no difference between u,s,u; and u;s;u;commit;.

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

If so, then the compound u,u,..,d,i; statement should be treated as a single update in order to apply constraints just before and after the list execution. As I wrote earlier, how can such compound statement be implemented without serious concurrency issues ?

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

But we already have this with the old transactional sequence of statements, so what's new apart from hypothetical syntactical conveniences and arguably simplified constraint management ?

>
> Consistent semantically, not just formally.

I'd prefer not to discuss this one ;)

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

Right. But the question is, can the sequence of updates/inserts/deletes be really implemented as an atomic compound statement, or is it just the deferred constraint in sheep's clothing ?

>
>
>
> 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 - 16:30:05 CEST