Re: [HACKERS] READ COMMITTED isolevel is implemented ... - Mailing list pgsql-hackers

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*** nodeSubplan.c.orig    Fri Nov 27 23:13:02 1998
--- nodeSubplan.c    Mon Feb  1 18:19:05 1999
***************
*** 121,126 ****
--- 121,127 ----
          ExecCreateTupleTable(ExecCountSlotsNode(node->plan) + 10);
      pfree(sp_estate->es_refcount);
      sp_estate->es_refcount = estate->es_refcount;
+     sp_estate->es_snapshot = estate->es_snapshot;

      if (!ExecInitNode(node->plan, sp_estate, NULL))
          return false;