Transactions - Concurrent Read Consistency (multiversion read)

1 - About

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.


3 - Query

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

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

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

5.1 - Statement

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.

5.2 - Transaction

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.

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

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

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap