Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> vc wrote:

[...]

>
> > raise salary and fire everybody whose salary is more than 10000:
> >
> > update t1 set salary=salary+1000, delete from t1 where salary >10000;
> >
> > What would be the effect of such statement ? Is it unpredictable or
> > not ? Why ?
>
> It is predictable: it updates all salaries by 1000, and delete all t1
> rows where salary was > 10000 BEFORE the statement.
>
> To achieve effect you intended the statement would be:
>
> update t1 set salary=salary+1000, delete from t1 where salary >9000;

Ok, good, I understand (I hope) how you imagine the compound statement runs, the two operations run independently and in no particular order (correct me if I am wrong).

Now, let's assume that the update for whatever reason started first and changed some rows with salary = 8100 and *then* the delete kicked in and removed those rows. Clearly, not what we intended.

The statement would execute correctly if we assume that the delete predicate applies only to the rows as of the point in time just *before* the compound stement started, and not to the rows modified by the update. Is that what you have in mind ?

Thanks. Received on Wed Oct 05 2005 - 13:11:30 CEST