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

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