Transactions - Concurrent Read Consistency (multiversion read)

Data System Architecture


concurrent read data consistency (also known as multi version consistency) means that the database can present a view of data to multiple concurrent users, with each view consistent at a point in time.

Ie The data returned by a request (query) is consistent with respect to a single point in time.

Because different versions of data can exist simultaneously, transactions can read the version of data committed at the point in time required and return results that are consistent to a single point in time.

Multiversioning is the ability to simultaneously materialize multiple versions of data.



Multiversion read consistency means that database queries have the following characteristics:

Dirty read

Dirty read can not provide read consistency. Dirty reads occur when a transaction reads uncommitted data in another transaction.

Dirty reads:

  • compromise data integrity,
  • violate foreign keys,
  • and ignore unique constraints.

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.
  • In a serializable or read-only transaction this point is time the transaction began. For example, if a transaction begins at 10h25, and if multiple SELECT statements occur in this transaction, then each statement is consistent to the data state at 10h25.
  • In a Flashback Query (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.


In Statement-level read consistency, the point in time is generally the point in time at which the statement was opened.

Statement-level read consistency guarantees that data returned by a single query is:

  • and consistent with respect to a single point in time.


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.

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. A Database achieves this goal through a rollback journal.

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.

Discover More
Data System Architecture
Concurrency - Concurrency

Data concurrency means that many thread (that may represents users) can access and modify data at the same time. Data concurrency ensures that users can access data at the same time ...reubenbond/status/662061791497744384/photo/1Reuben...
Data System Architecture
Dirty read

s is a phenomena (data problem) that occurs when a transaction reads uncommitted data in another transaction. can not provide read consistency. s compromise data integrity, violate foreign keys,...
Card Puncher Data Processing
Oracle Database - Read Consistency

in Oracle 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...
Data System Architecture
Shared Lock

A shared lock is a lock acquired in the shared mode over a data structure. The data structure can be any: from a file to any in-memory structure (ie variable with a complex type, generally a collection)...
Data System Architecture
Transactions - Rollback Journal (Undo journal)

A rollback journal consists of records of the actions of transactions, primarily before they are committed. Its name comes from the fact that its primary function is to roll back (undo) changes from...
Data System Architecture
Transactions - Write-Ahead Logging (Rollback journal) - WAL

Write-Ahead Logging (WAL) is a rollback journal implementation. This implementation writes change directly to the rollback journal whereas the traditional rollback journal writes changes to the original...

Share this page:
Follow us:
Task Runner