× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Oracle Data Concurrency and Consistency

We will learn that data concurrency means that many users can approach data at the same time and data consistency means that each user sees a consistent view of the data, including visible changes made by the users own transactions and transactions of other users.

What is Data Concurrency?

Data Concurrency arrange that both official data source and duplicated data values are consistent, that means whenever data values official data source is updated then the corresponding replicated data values must also be updated via synchronization in order to maintain consistency.

In a single user database, each transaction is processed serially; therefore there is no need for contention with interference from other transactions. But in a large data repository conditions, there could be hundreds or thousands of users and data consumers from across many different locations trying to access the warehouse simultaneously. Therefore, a single user database wills not there are three common ways that databases manage data currency and they are as follows:

  • Pessimistic concurrency control - In this method, a path is available to the users when the record is being retrieved and stays with the user until it is updated within the database.
  • Optimistic concurrency control - With this method, a path cannot be available to other users while the data is currently being renovated. During renovating, the database analysis the row in the database to determine whether or not any alteration has been made. An attempt to renew a record that has already been changed can be flagged as concurrency violation.

What is Data Consistency?

Data consistency is the process of keeping information uniform and unique as it moves across a network and between various applications on a computer.

There are generally three types of data consistency:

Point in time consistency, application consistency and transaction consistency. Satisfying that a computer network has all three elements of data consistency covered is the best way to satisfy that data is not lost or corrupted as it travels in the whole system. In the truancy of data consistency, there are no assuredly that any piece of information on the system is uniform and unique across the breadth of the computer network.

Point in time consistency deals with ensuring that all elements of a system are uniform at a specific period in time. This secure loss and damage of data during system improper shutdowns, crashes, and other problems on the network.

It works by allusion parts of data on the system via timestamps and other markers of consistency, allowing the system to be rearrange to a particular period in time with each piece of data in its original space. Without notifying in time consistency, there would be no assurity that all information on a crashing computer could be obtained to its previous state.

Overview of Locking Mechanisms

In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, integrity and consistency. Locks are contrivance that secures catastrophic interaction between transactions accessing the same resource.

Resources include two basic types of objects:

  • User objects, such as tables and rows (structures and data)
  • System objects invisible to the users, such as shared data structures in the memory and data dictionary path

Data Concurrency and Consistency Mechanism in Oracle

Oracle maintains data consistency in a multiuser environment by using a multiversion consistency model and various types of locks and transactions. The following topics are the mechanism to achieve data Concurrency:

  • Multiversion Concurrency Control
  • Statement-Level Read Consistency
  • Transaction-Level Read Consistency
  • Read Consistency with Real Application Crew
  • Oracle Isolation Levels
  • Comparison of Serializable Isolation and Read Committed
  • Choice of Isolation Level

Multiversion Concurrency Control

Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). Oracle can also maintain read consistency to all of the queries in a transaction (transaction-level read consistency).

Rollback Segment

Figure 1: Rollback Segment

As a query enters the execution stage, the current system change number (SCN) is determined. In Fig, this system change number is 10021. As data blocks are read on behalf of the query, only pattern written with the watched SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the recreated data is returned for the query. Therefore, every query outputs all committed data with respect to the SCN recorded at the time that query performance began. Changes of other performance that occur during a query's execution are not observed, satisfying that consistent data is results for each query.

Statement-Level Read Consistency

Oracle always enforces statement-level read consistency. This satisfies that all the data results by a single query come from a single point in time—the time that the query started. Therefore, a query never sees garbage data or any of the changes made by transactions that commit during query performance. As query performance starts, only data committed before the query started is visible to the query. The query does not see alteration done after statement execution begins.

Transaction-Level Read Consistency

Oracle also offers the option of enforcing transaction-level read consistency. When a occurrence runs in serializable mode, all data used shown the state of the database as of the time the transaction starts. This means that the data seen by all queries within the same transaction is consistent with respect to a single dot in time, except that parts made by a serializable transaction do see changes made by the occurrence itself. Transaction-phase read consistency produces repeatable reads and does not expose a query to phantoms.

Read Consistency with Real Application Crew

Real Application Clusters (RAC) use a cache-to-cache block transfer mechanism known as Cache Fusion to transfer read-consistent images of blocks from one specimen to another. RAC performs this task using high speed; low latency relationship to satisfy remote requests for data blocks.

Set the Isolation Level

Application designers, application developers, and database administrators can decide perfect isolation phases for different transactions, depending on the application and burden. We can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:


To save the networking and processing cost of beginning each transaction with a SET TRANSACTION statement, we can use the ALTER SESSION statement to set the intercourse isolation level for all subsequent transactions:


Read Committed Isolation

The default isolation level for Oracle is read committed. This degree of isolation is opportune for environments where few proceeding is likely to crash. Oracle ensures each query to run with respect to its own materialized view time, thereby permitting no repeatable reads and phantoms for multiple performance of a query, but providing higher probable output. Read committed isolation is the proper phase of isolation for environments where few transactions are likely to conflict.

Serializable Isolation

Serializable isolation is compatible for following conditions:

  • With huge databases and short transactions that renovation only a few rows
  • Where the probability that two concurrent transactions will modify the same rows is relatively low
  • Where relatively long-timing transactions are primarily read only

Comparison of Serializable Isolation and Read Committed:

Oracle gives the application developer a choice of two transaction isolation levels with different characteristics. Both the serializable isolation and read committed levels provide a high degree of consistency and concurrency. Both phases provide the contention-reducing benefits of Oracle's read consistency multiversion concurrency control model and exclusive row-level locking implementation and are designed for real-world application deployment.


In this article, we studied how we can make data unique all over the world by consistency property that a single change to its value will reflect all over the network and concurrency refers to the way in which multiple users can use that data at a time efficiently and fastly.

Have done the bachelor's in computer's and have done various courses on many front-end and back-end languages as Java, HTML, CSS, Javascript.

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login