Re: Theoretical Basis for SELECT FOR UPDATE

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.

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

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

> 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! Received on Fri Oct 07 2005 - 12:12:30 CEST