Oracle Database - Read Consistency

1 - About

Oracle Database maintains data consistency in a concurrency environment by using:

In this way, the database can present a view of data to multiple concurrent users, with each view consistent to a point in time. Because different versions of data blocks can exist simultaneously, transactions can read the version of data committed at the point in time required by a query and return results that are consistent to a single point in time.

3 - Multiversion Read Consistency

In Oracle Database, multiversioning is the ability to simultaneously materialize multiple versions of data.

Oracle Database maintains multiversion read consistency, which means that database queries have the following characteristics:

  • Read-consistent queries

The data returned by a query is committed and consistent with respect to a single point in time. Oracle Database never permits dirty reads, which occur when a transaction reads uncommitted data in another transaction. Dirty reads compromise data integrity, violate foreign keys, and ignore unique constraints.

  • Nonblocking queries

Readers and writers of data do not block one another.

4 - Read Level Consistency

4.1 - Statement

Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time.

The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query:

  • In the read committed isolation level, this point is the time at which the statement was opened. For example, if a SELECT statement opens at SCN 1000, then this statement is consistent to SCN 1000.
  • In a serializable or read-only transaction this point is time the transaction began. For example, if a transaction begins at SCN 1000, and if multiple SELECT statements occur in this transaction, then each statement is consistent to SCN 1000.
  • In a Flashback Query operation (SELECT … AS OF), the SELECT statement explicitly specifies the point in time. For example, you can query a table as it appeared last Thursday at 2 p.m.

4.2 - Transaction

Oracle Database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began.

Queries made by a serializable transaction see changes made by the transaction itself. For example, a transaction that updates employees and then queries employees will see the updates. Transaction-level read consistency produces repeatable reads and does not expose a query to phantom reads.

5 - 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.

6 - Example of Read Consistency

Read Consistency in the Read Committed Isolation Level"

The figure shows a query that uses undo data to provide statement-level 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 the Figure, this SCN is 10023. Each query in the transaction must return committed data with respect to SCN 10023.

In the figure, 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.

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.

7 - Read Consistency and Transaction Tables

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

db/oracle/consistency.txt · Last modified: 2017/09/07 13:50 by 108.162.246.129