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

Hello all,

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

In case of SELECT only,read consistency is obvious.
But it's ambiguous and difficult for me in case of update or select ..
for update.
There will be many ways of thinking.
Mine is only one of them.

My way of thinking is

The values of columns of tuples to be updated/deleted/sele
cted_for_update which appears in the query should be
latest ones and so they may be different from the values
which read consistency provides.
The time that the query began should be changed to
the time that no such differences can be seen.

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

[My thinkging]
If T2 wouldn't be blocked by T1,T2 woundn't detect the changes
of dt,so we don't have to change the time that the query began.
But if T2 detects the change of dt which is used in the query(
and the tuple is to be updated),the time that the query began
should be changed.

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

My way of thinking can't explain this case.
Oracle seems to ignore columns in the targetlist as you say.

But how about the following case ?
After chainging the query of T2 in [ Case-2 ] to

select dt from t
where id > 10 for update;

the result was
dt
----
8
10
16
(3 rows)

This result is reasonable for me.
Where is the difference ?

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

The result is same as yours.

Thanks.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp