Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> vc wrote:
> > > 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.
>
> I think the phrase "To cut a long story short" is significant. I don't
> think you can assume that Date meant the insert should necessarily
> SUCCEED in performing the insert, merely that IF the insert succeeds
> that will be the value used.

Right. I think what he meant was that if no constraint is violated then the insert will use the last committed value predating the compound statement. But success or failure is of secondary importance for our discussion since what we are talking about is *how* this last committed value can be obtained unless I misuderstood your point above.

>
> > 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.
>
> Please explain why not. It seems to me that you can, because if you
> select the rows for update (i.e. lock them) then you can be sure that
> the data won't be changed by smeone else while you are using it. Isn't
> that the whole POINT of select for update?

SUCCESS FOR UPDATE in Oracle's MVCC implementation (when used in the SERIALIZANLE i.l. ) is useless because it's treated like an update and may fail for exactly the same reson with the 'cannot serialize' error:

T1:                                            T2:

set transaction isolation serializable
                                         update t1 set x=1 where y=2;
select * from t1 where y=2 for update;
                                         commit;

>
> > Not quite, in some MV implementation that avoids the "first updater
> > wins" rule, you'd not need to abort.
>
> How so? They can't BOTH win?

Oracle's implementation is not the only possible one.

The transaction history you've described would execute successfuly in a multiversion control with timestamp ordering, for exampple, where each version has read and write timestamps, reads are always satisfied and writes are rejected when when an attempt is made to overwrite an already read item. There are other approaches like multiversion with timestamp postordering that delays writes instead of rejecting them.

>
> > 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 ?
>
> Using SELECT FOR UPDATE...? I think perhaps I'm missing your point
> here, because that seems just too obvious!

Yes, in a follow-up I wrote that it was quite easy to do with the UPDLOCK (locking DBs do not have SELECT FOR UPDATE) however at the heavy price of losing concurrency entirely. Received on Fri Oct 07 2005 - 16:10:13 CEST