Re: Theoretical Basis for SELECT FOR UPDATE

vc wrote:
> Tony Andrews wrote:
> > ... Mind you, I
> > have a feeling that serialisation would be required even with MV
>
> Not with MV, no. With MV, you can get a previous version from the
> list of committed versions without any row/table locking, "for free" as
> it were, -- that's how Oracle, for example, works.

Yes I know that, I am very familiar with how Oracle works. But...

> > We can't have data on which the statement relies changing under its
> > feet, can we?
>
> Yes, you can, from the version storage. The last committed version
> can be gotten either implicitely during the compound statement
> execution, or explicitely by saying "select * from t1 as of some time
> in the past".

Yes, but what I am saying is, would it be *right* for the DBMS to update a row using stale data in the SET or WHERE clauses? And I think the answer is "no". And if that is the case, then it had better either lock (select for update) that data as it reads it, or be prepared to fail if someone else changes it (isolation level serializable). Let's consider this old friend again:

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

If "under the covers" the DBMS processes that like this...

Time Action
---- ------
T1 update account set bal = (select bal from account where ac_no=123 as of start of txn)+10 where ac_no=123;
T3 update account set bal = (select bal from account where ac_no=456 as of start of txn)+10 where ac_no=456;

...what happens if someone else updates the balance of account 456 at time T2?

Whether we lock the rows and block the second user, or don't block them and instead have our transaction fail, either we are ARE "forcing all the potentially concurrent transactions modifying a common set of rows execute serially", are we not? Received on Thu Oct 06 2005 - 16:50:23 CEST