Hiroshi Inoue wrote:
> 
> Recently I thought about UPDATE operations in READ COMMITED
> mode a little and found it's very difficult.
> I read Oracle's manual but couldn't find the algorithm.
> 
> After I read your posting [READ COMMITTED isolevel is implemented ...],
> I tested the following [Case-1] in my Oracle database environment(Version
> 7).
> I believed that changes of the values of columns used in a QUERY caused
> re-execution.
> 
> After I posted my mail,I tested another case(the following [Case -2]) and
> found that changes of the values of columns used in a QUERY don't
> necessarily cause re-execution.
> To tell the truth,I can't understand the result of this case.
> IMHO this case requires re-execution after re-setting the time of execution
> and the result should be same as [Case-1] .
> But Oracle doesn' work as I believe.
> 
> 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') ?
 
> [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!

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

Vadim

Mon, 01 Feb 1999 11:54:20 +0700
Vadim Mikheev <vadim@krs.ru>
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	|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@tpf.co.jp

Mon, 1 Feb 1999 18:17:52 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
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
Mon, 01 Feb 1999 20:29:52 +0700
Vadim Mikheev <vadim@krs.ru>
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@tpf.co.jp

Tue, 2 Feb 1999 15:27:42 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] READ COMMITTED isolevel is implemented ...