Re: [HACKERS] READ COMMITTED isolevel is implemented ...

Hiroshi Inoue wrote:
>
> > Oracle says:
> > ---
> > Oracle always enforces statement-level read consistency.
> > This guarantees that the data returned by a single query
> > is consistent with respect to the time that the query began.
> > Therefore, a query never sees dirty data nor any of the changes
> > made by transactions that commit during query execution. As query
> > execution proceeds, only data committed before the query began is
> > visible to the query. The query does not see changes committed after
> > statement execution begins.
> > ---
> >
> > Your tests show that Oracle breaks this statement if re-evaluation
> > of query' qual is required!
> >
>
> I don't think so.
> It seems that Oracle changes the time that the query began and
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
What's the reason to do this?

> re-executes the query (or processes something like re-execution
> more effective than re-execution).
>
> So [ Case-1 ] is reasonable for me.

Why?
If T2 wouldn't be blocked by T1 then T2 wouldn't see T3 changes
even if T3 would be committed "just after" T2 started - before
T2 read id=10 row.

> In case of dt=dt+1,the time that the query of T2 began was changed
> to the time when T1 was committed and in case of dt=dt,it was not
> changed from the time when the query command was issued.
> I think that both cases hold column-level read consistency.
> But [ Case-2 ] is a question for me.
>
> When did the query of T2 begin ?
> It seems that only the values of old target dt ( ... set dt=dt+2 ...) are
> at the time when T1 was committed. ^^^

And this shows that Oracle doesn't re-execute query at all when qual
columns were not changed - just because of this is not required
from any point of view. Oracle just gets new version of row
and re-evaluates target list - to performe update over new version,
not old one.

Ok. Please try to run this modified test:

create table t (id int4,dt int4,name text);
insert into t values (10,5,'a0');
insert into t values (20,10,'b0');
insert into t values (30,15,'c0');

create table updatable (id int4);
insert into updatable select id from t;

[Case-2]

update t set dt=dt+1,
name='c1'
where id=30;
UPDATE 1
update t set dt=dt+2
where id > 10 and
id in (select * from updatable);
(blocked)

delete from updatable
where id = 30;
DELETE 1
COMMIT;
END
COMMIT;
END
UPDATE 2
(actually, I got UPDATE 1 - due to bug in subqueries:
subplan' snapshot wasn't initialized, fixed, patch attached).
select * from t;

id|dt|name
--+--+----
10| 5|a0
20|12|b0
30|18|c1
^^^^^^^^
updated... What's the result in Oracle?

Vadim