[20170530]寫一緻問題.txt

[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.

   1          1

   2          1

--//注意:事務沒有送出!!

--//session 2:

SCOTT@test01p> @s

SCOTT@test01p(130,325)> select * from t;

--//僅僅看到一條,這是第2個插入還沒有送出.

SCOTT@test01p(130,325)> update t set y = y+1;

--//session 2阻塞.回到session 1:

SCOTT@test01p(369,151)> commit;

Commit complete.

--//回到session 2,修改完成.

1 row updated.

   1          2

--//你可以發現x=2的記錄y=1并沒有修改,這是因為當時session 2看到1條.

2.測試2:

--//重複前面的測試,但是這次引入觸發器:

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;

/

SCOTT@test01p(369,151)> set serveroutput on

updating 1,0 to 1,1

updating 2,0 to 2,1

SCOTT@test01p(130,325)> set serveroutput on

--//出現阻塞.回到session 1:

SCOTT@test01p(369,151)> commit ;

--//回到session 2:

updating 1,1 to 1,2

updating 2,1 to 2,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:

--//重複前面的測試.

create table t as select 1 x, 0 y from dual;

update t set y = y+1 where x >0 and y is not null;

SCOTT@test01p(130,325)> update t set y = y+1 where x >0 and y is not null;

--//奇怪吧!這次修改2條記錄.lz的解析是oracle的bug

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;

--//如下也會

SCOTT@test01p(130,325)> update t set y = y+1 where y>=0;