Re: [HACKERS] READ COMMITTED isolevel is implemented ... - Mailing list pgsql-hackers
From | Vadim Mikheev |
---|---|
Subject | Re: [HACKERS] READ COMMITTED isolevel is implemented ... |
Date | |
Msg-id | 36B5AC50.785C59F2@krs.ru Whole thread Raw |
In response to | RE: [HACKERS] READ COMMITTED isolevel is implemented ... ("Hiroshi Inoue" <Inoue@tpf.co.jp>) |
Responses |
RE: [HACKERS] READ COMMITTED isolevel is implemented ...
|
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;
pgsql-hackers by date: