名词解释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, post-condition, and transformation condition on any transaction.
根据Consistency ，给Oracle带来一个写一致的问题。
先看现象
------- （from asktom "write "consistency""）---
ops$tkyte@ORA920> create sequence s;
Sequence created.
ops$tkyte@ORA920> create table msg1
2 ( seq int primary key, sid int,
3 old_id int, old_y int,
4 new_id int, new_y int );
Table created.
ops$tkyte@ORA920> create table msg2
2 ( seq int primary key, sid int,
3 old_id int, old_y int,
4 new_id int, new_y int );
Table created.
ops$tkyte@ORA920> create table t
2 as
3 select rownum id, 0 y
4 from all_users
5 where rownum <= 5;
Table created.
ops$tkyte@ORA920> select count(*)
2 from t;
COUNT(*)
----------
5
so, t is our 5 row table we'll do concurrent things on. msg1 will be a table
we'll log the before/after image row by row transactionally. msg2 will be a
table we'll do the same but using an autonomous transaction:
ops$tkyte@ORA920> create or replace procedure log_msg2( p_seq in int,
2 p_sid in int,
3 p_old_id in int,
4 p_old_y in int,
5 p_new_id in int,
6 p_new_y in int )
7 as
8 pragma autonomous_transaction;
9 begin
10 insert into msg2
11 (seq, sid, old_id, old_y, new_id, new_y )
12 values
13 (p_seq, p_sid, p_old_id, p_old_y, p_new_id, p_new_y );
14 commit;
15 end;
16 /
Procedure created.
ops$tkyte@ORA920> create or replace trigger t_trigger before update on t for
each row
2 declare
3 l_seq number;
4 begin
5 select s.nextval into l_seq from dual;
6
7 insert into msg1
8 (seq, sid, old_id, old_y, new_id, new_y )
9 values
10 (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
11
12 log_msg2
13 (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
14 end;
15 /
Trigger created.
that trigger will log entries in both tables -- we'll be able to see the atrans entry immediately, we'll only see committed entries in msg1 however
ops$tkyte@ORA920> update t set y = -1 where id = 3;
1 row updated.
set the "middle" row to -1, this locks it as well of course
ops$tkyte@ORA920> set echo off
in another session issue:
update t set y = 1 where y = -1 or id = 2 or id = 3;
then come back and hit enter
ops$tkyte@ORA920> pause
I did that, it blocked of course. it updated a single row -- where id=2 and
blocked on id=3
ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
we can see it updated row id=2 -- the trigger fired. It is waiting on id=3...
ops$tkyte@ORA920> set echo off
in another session issue:
update t set y = -1 where id in ( 1,5 );
commit;
ops$tkyte@ORA920> pause
that is your "update the first and last row". I did that, that transaction went right off -- no problem:
ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
we can see now 4 rows in our log table. seq=1 is the first update that we did and is blocking session2 over there. seq=2 is the one row that blocked session updated so far. seq=3/4 are the entries for the committed "first/last" row update
ops$tkyte@ORA920> commit;
Commit complete.
here is where it gets *very* interesting
ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
NOT VISIBLE 5 12566 3 0 3 1
NOT VISIBLE 6 12566 1 -1 1 1
NOT VISIBLE 7 12566 2 0 2 1
NOT VISIBLE 8 12566 3 -1 3 1
NOT VISIBLE 9 12566 5 -1 5 1
9 rows selected.
hmmm -- when we committed the first thing to happen was the row with id=3 was
updated -- we can see that -- seq=5 is that update. HOWEVER, that was actually
rolled back (as was the update to id=2 as we'll see below further) and the
update itself was restarted from the beginning. It "reupdated" id=2 and id=3
and added ids 1 and 5 to the mix (y=-1 part of the predicate picked them up).
ops$tkyte@ORA920> set echo off
commit in the formerly blocked session
ops$tkyte@ORA920> pause
ops$tkyte@ORA920> select * from t;
ID Y
---------- ----------
1 1
2 1
3 1
4 0
5 1
so we can clearly see it updated 4 rows and
ops$tkyte@ORA920> select decode(msg1.seq,null,'UNDONE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
------ ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
UNDONE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
UNDONE 5 12566 3 0 3 1
6 12566 1 -1 1 1
7 12566 2 0 2 1
8 12566 3 -1 3 1
9 12566 5 -1 5 1
9 rows selected.
now that everything is "committed" we can see the seq=2 and seq=5 were
"undone" -- rolled back. It was worked performed that was "unperformed" in
order to allow us to "redo it" (don't want to give that 10% raise 2 times, or 3
times or N times do you?)
------- （from asktom "write "consistency""）------
如上现象即为Mini-rollback，会导致session的统计信息cleanouts and rollbacks - consistent read gets 增加。
现说明Oracle执行update的过程：
1）根据条件作一致读。该一致读SCN为update statement开始的时间。
2）一致读中找到满足条件的记录。(this row,when we started at update-statement SCN,was interested by us )
3）然后以当前模式（current mode）读取到数据块最新的内容（row,when was interested in consistent read），然后比较一致读和当前读的数据集。
4）如果没有使用触发器，则只比较在where中出现的列 （predicate in consistent read） ;如果包括触发器，则还需要比较trigger内引用的:old 列。因为oracle是在一致读模式得到:old值，且在当前模式:old列可能会被改变了。
5) 如果predicate 发生变化，则表示受到其他事务影响，则mini-rollback. 否则正常更新。
据个例子，如果执行update t set x = 5 where y = 6;
consistent read中y=6为最后一行，则通过consistent read找到该纪录；然后以current read 读取该块；但是发现该纪录已经被其他session将"y=6" update为 "y=7"( Tx-another), 表示该session执行的update的状态不一致，导致mini-rollback.
update的前部分没有受到Tx-another的影响,后部分意识到Tx-another的影响。违背了ACID的C(onsistency):any changes to values in an instance are consistent with changes to other values in the same instance.
Mini-rollback后又如何？
a) Mini-rollback会释放块上的锁并且回滚（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不会触发triggers，etc
因为有可能再次mini-rollback,所以select for update即能获得锁且相对成本较低；降低再次mini-rollback可能性。
c)再执行更新。
因此，在batch更新之前，避免&减少mini-rollback的方法就是先select for update获得锁，再更新。