Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> vc wrote:
> > 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).
>
> Correct so far.

According to the link you've given (much appreciated), it's incorrect.  Date gives an example of the "multiple assignment":

Column 'a' is required to be between 1 and 100.

UPDATE tbl SET a = 250 WHERE id = 1,
INSERT INTO tbl-2 (select a from tbl where id = 1), UPDATE tbl SET a = 90 WHERE id = 1;

... and says, "For the record, the final "a" value for id = 1 in table tb1 will be 90". This quote implies that: 1) the statements inside the multiple assignment are executed strictly sequentially and '90' is assigned by the last update; 2) 'update tbl set a=250' and 'update tbl set a=90' do *not* conflict with each other.

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

That assumption (predicate applies only to the rows as of the point in time just *before* the compound stement started) appears to be correct.  From the same reference: "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".

This feature is interesting because in fact it implies that the hypothetical database has to implement multiversioning. How else can it get the last consistent value of 'a' after 'a' has already been changed ? This would exclude from consideration all purely locking databases similar to DB2, SQL Server, Informix, Sybase, etc.

Thanks. Received on Wed Oct 05 2005 - 14:55:14 CEST