Thread: 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
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 |a020 |12 |b030 |18 |c111 |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
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;
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/selected_for_update which appears in the query should be latest onesand so they may be different from the values which read consistency provides. The time that the query began should bechanged 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 changesof dt,so we don't have to change the time thatthe query began.But if T2 detects the change of dt which is used in the query(and the tuple is to be updated),the timethat 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