名詞解釋ACID :The basic properties of a database transaction: Atomicity Consistency Isolation and Durability All Oracle transactions comply with these properties
其中與本文標題相關的Consistency The transaction takes the resources from one consistent state to another
Consistency is the ACID property that ensures that any changes to values in an instance are consistent with changes to other values in the same instance A consistency constraint is a predicate on data which server as a precondition postcondition and transformation condition on any transaction
根據Consistency 給Oracle帶來一個寫一致的問題
先看現象
（from asktom write consistency）
ops$tkyte@ORA> create sequence s;
Sequence created
ops$tkyte@ORA> create table msg
( seq int primary key sid int
old_id int old_y int
new_id int new_y int );
Table created
ops$tkyte@ORA> create table msg
( seq int primary key sid int
old_id int old_y int
new_id int new_y int );
Table created
ops$tkyte@ORA> create table t
as
select rownum id y
from all_users
where rownum <= ;
Table created
ops$tkyte@ORA> select count(*)
from t;
COUNT(*)
so t is our row table well do concurrent things on msg will be a table
well log the before/after image row by row transactionally msg will be a
table well do the same but using an autonomous transaction:
ops$tkyte@ORA> create or replace procedure log_msg( p_seq in int
p_sid in int
p_old_id in int
p_old_y in int
p_new_id in int
p_new_y in int )
as
pragma autonomous_transaction;
begin
insert into msg
(seq sid old_id old_y new_id new_y )
values
(p_seq p_sid p_old_id p_old_y p_new_id p_new_y );
commit;
end;
/
Procedure created
ops$tkyte@ORA> create or replace trigger t_trigger before update on t for
each row
declare
l_seq number;
begin
select snextval into l_seq from dual;
insert into msg
(seq sid old_id old_y new_id new_y )
values
(l_seq userenv(sessionid) :oldid :oldy :newid :newy );
log_msg
(l_seq userenv(sessionid) :oldid :oldy :newid :newy );
end;
/
Trigger created
that trigger will log entries in both tables well be able to see the atrans entry immediately well only see committed entries in msg however
ops$tkyte@ORA> update t set y = where id = ;
row updated
set the middle row to this locks it as well of course
ops$tkyte@ORA> set echo off
in another session issue:
update t set y = where y = or id = or id = ;
then come back and hit enter
ops$tkyte@ORA> pause
I did that it blocked of course it updated a single row where id= and
blocked on id=
ops$tkyte@ORA> select decode(msgseqnullNOT VISIBLE) msg*
from msg msg
where msgseq = msgseq(+)
order by msgseq;
DECODE(MSG SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
NOT VISIBLE
we can see it updated row id= the trigger fired It is waiting on id=
ops$tkyte@ORA> set echo off
in another session issue:
update t set y = where id in ( );
commit;
ops$tkyte@ORA> pause
that is your update the first and last row I did that that transaction went right off no problem:
ops$tkyte@ORA> select decode(msgseqnullNOT VISIBLE) msg*
from msg msg
where msgseq = msgseq(+)
order by msgseq;
DECODE(MSG SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
NOT VISIBLE
we can see now rows in our log table seq= is the first update that we did and is blocking session over there seq= is the one row that blocked session updated so far seq=/ are the entries for the committed first/last row update
ops$tkyte@ORA> commit;
Commit complete
here is where it gets *very* interesting
ops$tkyte@ORA> select decode(msgseqnullNOT VISIBLE) msg*
from msg msg
where msgseq = msgseq(+)
order by msgseq;
DECODE(MSG SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
NOT VISIBLE
NOT VISIBLE
NOT VISIBLE
NOT VISIBLE
NOT VISIBLE
NOT VISIBLE
rows selected
hmmm when we committed the first thing to happen was the row with id= was
updated we can see that seq= is that update HOWEVER that was actually
rolled back (as was the update to id= as well see below further) and the
update itself was restarted from the beginning It reupdated id= and id=
and added ids and to the mix (y= part of the predicate picked them up)
ops$tkyte@ORA> set echo off
commit in the formerly blocked session
ops$tkyte@ORA> pause
ops$tkyte@ORA> select * from t;
ID Y
so we can clearly see it updated rows and
ops$tkyte@ORA> select decode(msgseqnullUNDONE) msg*
from msg msg
where msgseq = msgseq(+)
order by msgseq;
DECODE SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
UNDONE
UNDONE
rows selected
now that everything is committed we can see the seq= and seq= were
undone rolled back It was worked performed that was unperformed in
order to allow us to redo it (dont want to give that % raise times or
times or N times do you?)
（from asktom write consistency）
如上現象即為Minirollback會導致session的統計信息cleanouts and rollbacks consistent read gets 增加
現說明Oracle執行update的過程
）根據條件作一致讀該一致讀SCN為update statement開始的時間
）一致讀中找到滿足條件的記錄(this rowwhen we started at updatestatement SCNwas interested by us )
）然後以當前模式（current mode）讀取到數據塊最新的內容（rowwhen was interested in consistent read）然後比較一致讀和當前讀的數據集
）如果沒有使用觸發器則只比較在where中出現的列 （predicate in consistent read） ;如果包括觸發器則還需要比較trigger內引用的:old 列因為oracle是在一致讀模式得到:old值且在當前模式:old列可能會被改變了
) 如果predicate 發生變化則表示受到其他事務影響則minirollback 否則正常更新
據個例子如果執行update t set x = where y = ;
consistent read中y=為最後一行則通過consistent read找到該紀錄然後以current read 讀取該塊但是發現該紀錄已經被其他session將y= update為 y=( Txanother) 表示該session執行的update的狀態不一致導致minirollback
update的前部分沒有受到Txanother的影響後部分意識到Txanother的影響違背了ACID的C(onsistency):any changes to values in an instance are consistent with changes to other values in the same instance
Minirollback後又如何？
a) Minirollback會釋放塊上的鎖並且回滾（block cleanup and rollback）;但被該session所阻塞的session仍然在等待TX Lock
b) 然後切換到SELECT FOR UPDATE 模式 因為
SELECT FOR UPDATE不會產生太多的 undo & redo only the lock byte and the ITL are touched not the other row (or indexes!) bytes不會觸發triggersetc
因為有可能再次minirollback所以select for update即能獲得鎖且相對成本較低降低再次minirollback可能性
c)再執行更新
因此在batch更新之前避免&減少minirollback的方法就是先select for update獲得鎖再更新
From:http://tw.wingwit.com/Article/program/Oracle/201311/17741.html
