Concurrency is the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity. Destructive interactions between concurrent transactions must be prevented.
Oracle resolves such issues by using various types of locks and a multiversion consistency model. This features are based on the concept of a transaction.
Read consistency:
read consistency supports the following features:
1. Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution(statement-level read consistency)
2. Ensures that readers of database data do not wait for writers or other readers of the same data.
3. Ensures that writers of database data do not wait for readers of the same data
4. Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions.
The simplest way to think of read consistency is to imagine each user operating a private copy of the database, hence the multi-version consistency model.
Read consistency, undo records and transactions: When an update occurs, the original data values changed by the update are recorded in the database undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table’s data for a query.
The transaction is key to Oracle’s strategy for providing read consistency. This unit of committed or uncommitted SQL statements:
1. Dictates the start point for read-consistent views generated on behalf of readers.
2. Controls when modified data can be seen by other transactions of the database for reading or updating.
Read-only transactions: By default, oracle guarantees statement-level read consistency. The set of data returned by a single query is consistent with respect to a single point in time. In some situations, you might also require transaction-level read consistency. This is the ability to run multiple queries within a single transaction, all of which are read-consistent with respect to the same point in time, so that queries in this transaction do not see the effects of intervening committed transactions. If you want to run a number of queries against multiple tables and if you are not doing any updating ,you prefer a read-only transaction.
Locking Mechanisms
Oracle also uses locks to control concurrent access to data. When updating information, the data server holds that information with a lock until the update is submitted or committed. Until that happens, no one else can make changes to the locked information.
Oracle provides unique non-escalating row-level locking. Oracle always locks only the row of information being updated. Oracle can lock an unlimited number or rows so users can work concurrently without unnecessary delays.
Automatic Locking: Oracle locking is performed automatically and requires no user action.
The two general types of locks are exclusive locks and share locks. Only one exclusive lock can be placed on a resource(such as a row or a table), Many share locks can be placed on a single resource. Both exclusive and share locks always allow queries on the locked resource but prohibit other activity on the resource(such as updates and deletes).
Manual locking: …..
Quiesce Database: