Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> vc wrote:

[...]

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

Taking Date's quote at its face value ("To cut a long story short, the "a" value that gets inserted into tbl-2 here is whatever it happened to be before the multiple assignment is executed"), yes it's right to use as you put it "stale data", that is the last committed data just before the compound statement started.

> 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?
... and commits.

It's a good example although unrelated to the MV database ability to obtain the last committed value just before the compound statement, rather it has to do with Oracle's MV limitations which exclude certain serializable histories and allow some non-serializable ones (assuming Oracle is set to use the SERIALIZABL i.l.).

The sequence of events you've described is serializable and would execute successfully in a hypothetical MV database although not in Oracle because of the "first updater wins" rule. You cannot fix the issue with SELECT FOR UPDATE. So you have two options: 1. retry; 2. downgrade the i.l. to READ COMMITTED which would be quite adequate for the this specific example.

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

Not quite, in some MV implementation that avoids the "first updater wins" rule, you'd not need to abort.

Regarding the locking approach, I am not sure whether it can be implemented at all. How would you lock/freeze a set of rows in various tables to guarantee a consistent view across the database ? Received on Thu Oct 06 2005 - 20:02:36 CEST