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:
- Read-consistent queries
- Non-blocking queries. Readers and writers of data do not block one another. (only with a Transactions - Write-Ahead Logging (Rollback journal) - WAL)
- compromise data integrity,
- violate foreign keys,
- and ignore unique constraints.
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
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.