052试题 178 - 一致性读 read consistency

题目：
178. All the database users are presently connected to the database instance and working. The HR user
has opened three database sessions and executed the following command in one of his sessions:
SQL> UPDATE persons SET ccode='U031' WHERE ccode='U029';
123 rows updated.
SQL> DELETE FROM persons WHERE exp='Y';
3 rows deleted.
The SYS user opens a new session after HR executed the above commands. Which sessions can see the effect of the UPDATE and DELETE commands?
A.all sessions of the HR user only
B.all sessions of the HR user and the SYS user
C.the session of the HR user that executed the commands
D.all the sessions for which the database users have access privilege to the PERSONS table
参考答案 C
解析
题目意思是HR用户更新了表persions(更新了123条)，然后又删除了表persions三条数据。没有提交。
sys用户连接到了数据库。问那个session可以看到update和delete的效果。
只有HR用户的session才能看到update和delete的结果。
sys用户只能看不到这些update和delete的效果。因为sys用户所在的会话会从undo中读取修改前的数据。
所以选择C 。

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e40540/consist.htm#CNCPT1313

Read Consistency and Undo Segments

To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this goal through undo data.

Whenever a user modifies data, Oracle Database creates undo entries, which it writes to undo segments ("Undo Segments"). The undo segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Thus, multiple versions of the same data, all at different points in time, can exist in the database. The database can use snapshots of data at different points in time to provide read-consistent views of the data and enable nonblocking queries.

Read consistency is guaranteed in single-instance and Oracle Real Application Clusters (Oracle RAC) environments. Oracle RAC uses a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of data blocks from one database instance to another.

See Also:

• "Internal LOBs" to learn about read consistency mechanisms for LOBs

• Oracle Database 2 Day + Real Application Clusters Guide to learn about Cache Fusion

Read Consistency: Example

Figure 9-1 shows a query that uses undo data to provide statement-level read consistency in the read committed isolation level.

Figure 9-1 Read Consistency in the Read Committed Isolation Level

Description of "Figure 9-1 Read Consistency in the Read Committed Isolation Level"
 

As the database retrieves data blocks on behalf of a query, the database ensures that the data in each block reflects the contents of the block when the query began. The database rolls back changes to the block as needed to reconstruct the block to the point in time the query started processing.

The database uses a mechanism called an SCN to guarantee the order of transactions. As the SELECT statement enters the execution phase, the database determines the SCN recorded at the time the query began executing. In Figure 9-1, this SCN is 10023. The query only sees committed data with respect to SCN 10023.

In Figure 9-1, blocks with SCNs after 10023 indicate changed data, as shown by the two blocks with SCN 10024. The SELECT statement requires a version of the block that is consistent with committed changes. The database copies current data blocks to a new buffer and applies undo data to reconstruct previous versions of the blocks. These reconstructed data blocks are called consistent read (CR) clones.

In Figure 9-1, the database creates two CR clones: one block consistent to SCN 10006 and the other block consistent to SCN 10021. The database returns the reconstructed data for the query. In this way, Oracle Database prevents dirty reads.

See Also:

"Database Buffer Cache" and "System Change Numbers (SCNs)"

Read Consistency and Transaction Tables

The database uses information in the block header, also called an interested transaction list (ITL), to determine whether a transaction was uncommitted when the database began modifying the block. The block header of every segment block contains an ITL.

Entries in the ITL describe which transactions have rows locked and which rows in the block contain committed and uncommitted changes. The ITL points to the transaction table in the undo segment, which provides information about the timing of changes made to the database.

In a sense, the block header contains a recent history of transactions that affected each row in the block. The INITRANS parameter of the CREATE TABLE and ALTER TABLE statements controls the amount of transaction history that is kept.

See Also:

Oracle Database SQL Language Reference to learn about the INITRANS parameter

END