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

Hello all,

> -----Original Message-----
> From: root@sunpine.krs.ru [mailto:root@sunpine.krs.ru]On Behalf Of Vadim
> Mikheev
> Sent: Monday, February 01, 1999 1:54 PM
> To: Hiroshi Inoue
> Cc: hackers@postgreSQL.org
> Subject: Re: [HACKERS] READ COMMITTED isolevel is implemented ...
> 
> 
> Hiroshi Inoue wrote:
> > 
[snip]

> > 
> > 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');
> > 
> > id      |dt     |name
> > ----------------------------
> > 10      |5      |a0
> > 20      |10     |b0
> > 30      |15     |c0
> > 
> > 
> > session-1               session-2               session-3
> > 
> > [Case-1]
> > update t set dt=dt+1,
> >                   ^^^^^^^^
> >         name='c1'
> >     where id=30;
> > UPDATE 1
> >                         update t set dt=dt+2
> >                         where dt >7;
> >                         ^^^^^^^^^^^^^
> >                         (blocked)
> >                                                 update t set dt=dt+3,
> >                                                              ^^^^^^^^^
> >                                                         id=id+1
> >                                                     where id=10;
> >                                                 UPDATE 1
> >                                                 commit;
> >                                                 COMMIT
> > commit;
> > COMMIT
> >                         UPDATE 3
> >                         ^^^^^^^^^^^^
> 
> Ops. I'm quite suprized that T2 sees changes made by T3 after
> T2' statement started! What would be results if T3 wouldn't
> make UPDATE but made INSERT INTO t VALUES (11, 8, 'a1') ?
>
The result was
UPDATE 3
id    |dt    |name----------------------------10    |5    |a020    |12    |b030    |18    |c111    |10    |a1 
> > [result]        id      |dt     |name
> >         ---------------------------
> >         11      |10     |a0
> >         20      |12     |b0
> >         30      |18     |c1
> > 
> > If      dt=dt+1      ==>  dt=dt
> >             ^^^^^^^^
> > then            UPDATE 3  ==> UPDATE 2
> >         ^^^^^^^^^^^^
> > 
> > [result]        id      |dt     |name
> >         ---------------------------
> >         11      |8      |a0
> >         20      |12     |b0
> >         30      |17     |c1
> > 
> 
> Why T2 doesn't change id=11 row now???
> Does Oracle re-execute _entire_ query after being blocked
> by concurrent transaction T only if T made changes in columns
> used in QUAL?!
> 
> Yes! Case-2 confirmes this!
> 
> > [Case-2]
> > 
> > update t set dt=dt+1,
> >               ^^^^^^^^
> >         name='c1'
> >     where id=30;
> > UPDATE 1
> >                         update t set dt=dt+2
> >                         where id > 10;
> >                                  ^^^^^^^^^
> >                         (blocked)
> >                                                 update t set dt=dt+3,
> >                                                         id=id+1
> >                                                         ^^^^^^^^
> >                                                     where id=10;
> >                                                 UPDATE 1
> >                                                 commit;
> >                                                 COMMIT
> > commit;
> > COMMIT
> >                        UPDATE 2
> >                       ^^^^^^^^^^^^
> > 
> > [result]        id      |dt     |name
> >         ---------------------------
> >         11      |8      |a0
> >         20      |12     |b0
> >         30      |18     |c1
> 
> id is not changed by T1 and so T2 doesn't re-execute query
> after T1 committed and doesn't see changes made by T3!
> T2 just re-evaluates target list.
> 
> Ok. Postgres always re-executes query after being blocked,
> but makes this for single row only and so all other changes
> made after query started are not visible. I would say that
> we are more consistent than Oracle -:))
> 
> 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 
re-executes the query (or processes something like re-execution 
more effective than re-execution).

So [ Case-1 ] is reasonable for me.

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

> Just wondering what would be the results of these test in
> Informix or Sybase?
> Could someone run them?

Anyway the version of Oracle used for my test is very old.
The result may be different in newer version.
Cound someone run them in Oracle8 ?

Thanks.

Hiroshi Inoue
Inoue@tpf.co.jp