|
名词解释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获得锁再更新 |
