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

Hello all,

> -----Original Message-----
> From: root(at)sunpine(dot)krs(dot)ru [mailto:root(at)sunpine(dot)krs(dot)ru]On Behalf Of Vadim
> Mikheev
> Sent: Monday, February 01, 1999 1:54 PM
> To: Hiroshi Inoue
> Cc: hackers(at)postgreSQL(dot)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 |a0
20 |12 |b0
30 |18 |c1
11 |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(at)tpf(dot)co(dot)jp