[20170530]写一致问题.txt
--//oracle 通过undo等保持读一致性.
--//假如一个回话修改1条记录 y字段+1,另外的回话也修改相同记录,y字段+1.
--//这样第2个回话会出现阻塞,等待第1个回话提交或者回滚,当第1个回话提交后,第2个回话会重读y的值,
--//然后再增加1.如果最开始y=0.这样第2个回话显示y=2.上午看了一个系列测试:
raajeshwaran.blogspot.com/2016/06/write-inconsistency-part-i.html
raajeshwaran.blogspot.com/2016/06/write-inconsitency-part-ii.html
raajeshwaran.blogspot.com/2016/06/write-inconsitency-part-iii.html
--//我重复测试.通过一些例子说明问题.
0.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
1.测试1:
--//session 1:
--//drop table t purge;
SCOTT@test01p(369,151)> create table t as select 1 x,0 y from dual;
Table created.
SCOTT@test01p(369,151)> insert into t(x,y) values(2,0);
1 row created.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 0
2 0
SCOTT@test01p(369,151)> update t set y = y+1;
2 rows updated.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//注意:事务没有提交!!
--//session 2:
SCOTT@test01p> @s
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 0
--//仅仅看到一条,这是第2个插入还没有提交.
SCOTT@test01p(130,325)> update t set y = y+1;
--//session 2阻塞.回到session 1:
SCOTT@test01p(369,151)> commit;
Commit complete.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//回到session 2,修改完成.
SCOTT@test01p(130,325)> update t set y = y+1;
1 row updated.
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 2
2 1
--//你可以发现x=2的记录y=1并没有修改,这是因为当时session 2看到1条.
2.测试2:
--//重复前面的测试,但是这次引入触发器:
--//drop table t purge;
create table t as select 1 x,0 y from dual;
create or replace trigger t_trig
before update on t
for each row
begin
dbms_output.put_line('updating '|| :old.x ||','|| :old.y
||' to '|| :new.x || ',' || :new.y );
end;
/
--//session 1:
SCOTT@test01p(369,151)> set serveroutput on
SCOTT@test01p(369,151)> insert into t(x,y) values(2,0);
1 row created.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 0
2 0
SCOTT@test01p(369,151)> update t set y = y+1;
updating 1,0 to 1,1
updating 2,0 to 2,1
2 rows updated.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//注意:事务没有提交!!
--//session 2:
SCOTT@test01p(130,325)> set serveroutput on
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 0
SCOTT@test01p(130,325)> update t set y = y+1;
--//出现阻塞.回到session 1:
--//session 1:
SCOTT@test01p(369,151)> commit ;
Commit complete.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//回到session 2:
--//session 2:
SCOTT@test01p(130,325)> update t set y = y+1;
updating 1,0 to 1,1
updating 1,1 to 1,2
updating 2,1 to 2,2
2 rows updated.
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 2
2 2
--//你可以发现x=2,y=2,从某种程度认为trigger is evil.从dba角度认为触发器是应用的大忌,从某种角度要尽量避免.
--//lz的解析:
http://raajeshwaran.blogspot.co.id/2016/06/write-inconsitency-part-ii.html
The output from the trigger confirms, the update statement from session#2
a) Did a consistent read on Table 'T'
b) Got the first row for update with x=0 and y=1 and changed y=2 and invoked the trigger before executing the update
statement (since it is a before update trigger).
c) Trigger put this message "updating 1,0 to 1,1" in buffer
d) Upon trying to make changes to that block, it realizes that row is locked by session#1 and hence this update got
blocked.
e) When session#1 got committed – this update from session#2 resumes – in-turn got restarted with SCN later than
commit SCN generated by the first transaction.
· So do again a consistent read, go two rows, modify them, have those before and after values recorded by trigger in
buffer.
f) Session#2 get current read on block to make changes for new values of Y. (since session#1 got committed, block is
available for current read to Session#2)
g) For each row updated trigger tries to put message "updating x1, y1 to x2, y2" in buffer
h) Once the update statement completes, message from buffer got printed on screen.
The presence of three message confirms that update got restarted and produced correct results at the end of this
transaction.
The set of columns that "trigger" the restart are the ones used to locate rows (of which there are none, the where
clause doesn't exist) plus any columns referenced in a trigger. Since the trigger refers to X and Y - they become part
of the set of columns responsible for triggering the restart of this update.
--//自己认为不好理解^_^.
3.测试3:
--//重复前面的测试.
--//drop table t purge;
--//session 1:
create table t as select 1 x, 0 y from dual;
SCOTT@test01p(369,151)> insert into t(x,y) values(2,0);
1 row created.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 0
2 0
SCOTT@test01p(369,151)> update t set y = y+1;
2 rows updated.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//注意:事务没有提交!!
--//session 2:
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 0
update t set y = y+1 where x >0 and y is not null;
--//出现阻塞.回到session 1:
--//session 1:
SCOTT@test01p(369,151)> commit ;
Commit complete.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//回到session 2:
--//session 2:
SCOTT@test01p(130,325)> update t set y = y+1 where x >0 and y is not null;
2 rows updated.
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 2
2 2
--//奇怪吧!这次修改2条记录.lz的解析是oracle的bug
--//lz的解析:
http://raajeshwaran.blogspot.co.id/2016/06/write-inconsitency-part-iii.html
So having the set of columns in where clause "triggers" the transaction restart, which in-turn advances SCN later than
the commit SCN generated by the first transaction.
That is the whole story of getting consistent result after the update from Session#2.
So why this statement "update T set y = y+1" behaves different from this statement "update T set y = y+1 where x >0
and y is not null"? (Since both the statement is supposed to update all the rows from the Table T) – This is
identified as an Internal Bug and not yet fixed still in Oracle 12c (12.1.0.2)
So please aware of these and have your DML's to be tested properly in applications, to avoid these kind of
inconsistency, till they get fixed.
4.补充测试:
--//自己做了补充测试只要条件是如下,就修改2条.
SCOTT@test01p(130,325)> update t set y = y+1 where y is not null;
2 rows updated.
--//如下也会
SCOTT@test01p(130,325)> update t set y = y+1 where y>=0;
2 rows updated.
--//oracle 通过undo等保持读一致性.
--//假如一个回话修改1条记录 y字段+1,另外的回话也修改相同记录,y字段+1.
--//这样第2个回话会出现阻塞,等待第1个回话提交或者回滚,当第1个回话提交后,第2个回话会重读y的值,
--//然后再增加1.如果最开始y=0.这样第2个回话显示y=2.上午看了一个系列测试:
raajeshwaran.blogspot.com/2016/06/write-inconsistency-part-i.html
raajeshwaran.blogspot.com/2016/06/write-inconsitency-part-ii.html
raajeshwaran.blogspot.com/2016/06/write-inconsitency-part-iii.html
--//我重复测试.通过一些例子说明问题.
0.环境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
1.测试1:
--//session 1:
--//drop table t purge;
SCOTT@test01p(369,151)> create table t as select 1 x,0 y from dual;
Table created.
SCOTT@test01p(369,151)> insert into t(x,y) values(2,0);
1 row created.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 0
2 0
SCOTT@test01p(369,151)> update t set y = y+1;
2 rows updated.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//注意:事务没有提交!!
--//session 2:
SCOTT@test01p> @s
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 0
--//仅仅看到一条,这是第2个插入还没有提交.
SCOTT@test01p(130,325)> update t set y = y+1;
--//session 2阻塞.回到session 1:
SCOTT@test01p(369,151)> commit;
Commit complete.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//回到session 2,修改完成.
SCOTT@test01p(130,325)> update t set y = y+1;
1 row updated.
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 2
2 1
--//你可以发现x=2的记录y=1并没有修改,这是因为当时session 2看到1条.
2.测试2:
--//重复前面的测试,但是这次引入触发器:
--//drop table t purge;
create table t as select 1 x,0 y from dual;
create or replace trigger t_trig
before update on t
for each row
begin
dbms_output.put_line('updating '|| :old.x ||','|| :old.y
||' to '|| :new.x || ',' || :new.y );
end;
/
--//session 1:
SCOTT@test01p(369,151)> set serveroutput on
SCOTT@test01p(369,151)> insert into t(x,y) values(2,0);
1 row created.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 0
2 0
SCOTT@test01p(369,151)> update t set y = y+1;
updating 1,0 to 1,1
updating 2,0 to 2,1
2 rows updated.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//注意:事务没有提交!!
--//session 2:
SCOTT@test01p(130,325)> set serveroutput on
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 0
SCOTT@test01p(130,325)> update t set y = y+1;
--//出现阻塞.回到session 1:
--//session 1:
SCOTT@test01p(369,151)> commit ;
Commit complete.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//回到session 2:
--//session 2:
SCOTT@test01p(130,325)> update t set y = y+1;
updating 1,0 to 1,1
updating 1,1 to 1,2
updating 2,1 to 2,2
2 rows updated.
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 2
2 2
--//你可以发现x=2,y=2,从某种程度认为trigger is evil.从dba角度认为触发器是应用的大忌,从某种角度要尽量避免.
--//lz的解析:
http://raajeshwaran.blogspot.co.id/2016/06/write-inconsitency-part-ii.html
The output from the trigger confirms, the update statement from session#2
a) Did a consistent read on Table 'T'
b) Got the first row for update with x=0 and y=1 and changed y=2 and invoked the trigger before executing the update
statement (since it is a before update trigger).
c) Trigger put this message "updating 1,0 to 1,1" in buffer
d) Upon trying to make changes to that block, it realizes that row is locked by session#1 and hence this update got
blocked.
e) When session#1 got committed – this update from session#2 resumes – in-turn got restarted with SCN later than
commit SCN generated by the first transaction.
· So do again a consistent read, go two rows, modify them, have those before and after values recorded by trigger in
buffer.
f) Session#2 get current read on block to make changes for new values of Y. (since session#1 got committed, block is
available for current read to Session#2)
g) For each row updated trigger tries to put message "updating x1, y1 to x2, y2" in buffer
h) Once the update statement completes, message from buffer got printed on screen.
The presence of three message confirms that update got restarted and produced correct results at the end of this
transaction.
The set of columns that "trigger" the restart are the ones used to locate rows (of which there are none, the where
clause doesn't exist) plus any columns referenced in a trigger. Since the trigger refers to X and Y - they become part
of the set of columns responsible for triggering the restart of this update.
--//自己认为不好理解^_^.
3.测试3:
--//重复前面的测试.
--//drop table t purge;
--//session 1:
create table t as select 1 x, 0 y from dual;
SCOTT@test01p(369,151)> insert into t(x,y) values(2,0);
1 row created.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 0
2 0
SCOTT@test01p(369,151)> update t set y = y+1;
2 rows updated.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//注意:事务没有提交!!
--//session 2:
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 0
update t set y = y+1 where x >0 and y is not null;
--//出现阻塞.回到session 1:
--//session 1:
SCOTT@test01p(369,151)> commit ;
Commit complete.
SCOTT@test01p(369,151)> select * from t;
X Y
---- ----------
1 1
2 1
--//回到session 2:
--//session 2:
SCOTT@test01p(130,325)> update t set y = y+1 where x >0 and y is not null;
2 rows updated.
SCOTT@test01p(130,325)> select * from t;
X Y
---- ----------
1 2
2 2
--//奇怪吧!这次修改2条记录.lz的解析是oracle的bug
--//lz的解析:
http://raajeshwaran.blogspot.co.id/2016/06/write-inconsitency-part-iii.html
So having the set of columns in where clause "triggers" the transaction restart, which in-turn advances SCN later than
the commit SCN generated by the first transaction.
That is the whole story of getting consistent result after the update from Session#2.
So why this statement "update T set y = y+1" behaves different from this statement "update T set y = y+1 where x >0
and y is not null"? (Since both the statement is supposed to update all the rows from the Table T) – This is
identified as an Internal Bug and not yet fixed still in Oracle 12c (12.1.0.2)
So please aware of these and have your DML's to be tested properly in applications, to avoid these kind of
inconsistency, till they get fixed.
4.补充测试:
--//自己做了补充测试只要条件是如下,就修改2条.
SCOTT@test01p(130,325)> update t set y = y+1 where y is not null;
2 rows updated.
--//如下也会
SCOTT@test01p(130,325)> update t set y = y+1 where y>=0;
2 rows updated.
