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.
You didn't like the quality of this content?
Would you like to comment what you didn't like?
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
LOCK TABLE student IN EXCLUSIVE MODE NOWAIT;
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).
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:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ ONLY;
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:
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE; ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
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 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.