Oracle 如何实现 Read Consistency（读一致性）

 Oracle的官方文档中这样说：
    Oracle uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions

    As a query enters the execution stage, the current system change number (SCN) is determined. In Figure 20-1, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.

    一直以为Oracle直接从 rollback segments 中直接可以拿到 reconstructed data,
    只到今天看到这篇文章：
   

The session, when reading the block (possibly still in cache), finds the open ITL. It checks the transaction status (by reading the rollback segment header) and finds that it is still active. This means that the session must roll back the changes made by the active transaction to create a view (snapshot) of the block before the first change was made. This is called making a consistent read (CR) copy of the block, and is achieved by first cloning the block and then rolling back the latest changes by applying undo records from the rollback segment.

Note that CR requires access to the data block, rollback segment header, and undo records. If any of these are corrupted, CR is affected.

    也就是说当一个session要一致读的时候,它实际上是直接把当前正在被修改的block拷贝了一份,然后回滚当前操作.

 

附：一致读步骤：

1. Read the Data Block.

2. Read the Row Header.

3. Check the Lock Byte to determine whether there's an ITL entry.

4. Read the ITL entry to determine the Transaction ID (Xid).

5. Read the Transaction Table using the Transaction ID. If the transaction has been committed and has a System Commit Number less than the query's System Change Number, update the status of the block (block cleanout) and start over at step 1.

6. Read the last undo block (Uba).

7. Compare the block transaction ID with the transaction table transaction ID. If the Transaction ID in the undo block doesn't equal the Transaction ID from the Transaction Table, then issue ORA-1555, Snapshot Too Old.

8. If the Transaction IDs are identical, make a copy of the data block in memory. Starting with the head undo entry, apply the changes to the copied data block.

9. If the tail undo entry (the actual first undo entry in the chain, or the last in the chain going backwards!) indicates another data block address, read the indicated undo block into memory and repeat steps 7 and 8 until the undo entries don't contain a value for the data block address.

10. When there's no "previous data block address," the transaction has been completely undone.

11. If the undo entry contains:

      a. a pointer to a previous transaction undo block address, read the Transaction ID in the previous transaction undo block header and read the appropriate Transaction Table entry. Return to step 5.

      b. an ITL record, restore the ITL record to the data block. Return to step 4.

 

注释说明：

事实上在第6步之前，会先比较query scn和control scn(下面红色字体是解释)，如果control scn > query scn，oracle根本就不去尝试读取undo block了，因为注定失败，立刻报1555错误.

第7步，表示

不是修改了当前block的事务没有commit

就是事务commit了但是commit scn >query scn，（因为第5步已经判断commit scn<query scn的情况）

所以准备要读取回滚段中的前镜像。

第9步，你可以把rollback的过程想象为一个链表，从一个undo block链到另一个undo block，直到the undo entries don't contain a value for the data block address，表示整个rollback的过程完毕了。

 

补充知识：

为什么事务完成后不马上作clean out（在data block的ITI写入commit SCN，清除锁标志）呢？

一般情况下是这样的,这种情况称为fash clean out，但是有些时候比如只会CLEANOUT 10% buffer cache中的buffers，其余的会等下一次data block被重新读入再作clean out ,这种情况称为delay clean out，万一此Transaction Table上的记录被覆盖了（很有可能，因为可能很长时间后才被重新读入），则使用upper bound commit scn 作为其commit scn

 

什么是upper bound commit？

oracle 会把query scn 去和 回滚段中 事务的提交scn比较，如果没有找到事务结束scn，说明回滚信息已经被覆盖了。这时就把query scn 去和 control scn比较，因为 control SCN is the commit SCN of the most recently reused transaction slot's previous transaction. 如果 query scn 依然小于这个control scn ，由于被覆盖的事务的commit scn 肯定比回滚段中 contrl scn 小，则暗示着 oracle 无法知道 query scn 和 commit scn 之间的大小关系，就报了 1555 错误。

这个时候，oracle就把这个 control scn 当作 upper bound commit scn 。这是因为，真实的cmmit scn 必然小于这个 control scn .而current scn必然远大于这个 control scn。所以将 commit scn 设置为这个control scn 必然是安全的。

commit scn < control scn (upper bound scn) < current scn

 

 

注：整理自网络