Oracle7 Server Concepts
Data Concurrency and Consistency
This section explains the software mechanisms used by Oracle to fulfill the following important requirements of an information management system:
- Data must be read and modified in a consistent fashion.
- Data concurrency of a multi-user system must be maximized.
- High performance is required for maximum productivity from the many users of the database system.
Concurrency
A primary concern of a multi-user database management system is how to control concurrency, or the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.
If many people are accessing the same data, one way of managing data concurrency is to make each user wait his or her turn. The goal of a database management system is to reduce that wait so it is either non-existent or negligible to each user. All DML statements should proceed with as little interference as possible and destructive interactions between concurrent transactions must be prevented. Destructive interaction is any interaction that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.
Oracle resolves such issues by using various types of locks and a multiversion consistency model. Both features are discussed later in this section. These features are based on the concept of a transaction. As discussed in "Data Consistency Using Transactions" ![[*]](jump.gif)
, it is the application designer's responsibility to ensure that transactions fully exploit these concurrency and consistency features.
Read Consistency
Read consistency, as supported by Oracle, does the following:
- 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)
- ensures that readers of database data do not wait for writers or other readers of the same data
- ensures that writers of database data do not wait for readers of the same data
- ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions
The simplest way to think of Oracle's implementation of read consistency is to imagine each user operating a private copy of the database, hence the multiversion consistency model.
Read Consistency, Rollback Segments, and Transactions
To manage the multiversion consistency model, Oracle must create a read-consistent set of data when a table is being queried (read) and simultaneously updated (written). When an update occurs, the original data values changed by the update are recorded in the database's rollback segments. 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 rollback segments to construct a read-consistent view of a table's data for a query. Only when a transaction is committed are the changes of the transaction made permanent. Statements, which start after the user's transaction is committed, only see the changes made by the committed transaction.
Note that a transaction is key to Oracle's strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements
- dictates the start point for read-consistent views generated on behalf of readers
- 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. However, in some situations, you may also require transaction-level read consistency -- 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 doing no updating, you may prefer a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any table, knowing that the results of each query are consistent with respect to the same point in time.
Locking
Oracle also uses locks to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing Oracle data.
Locks are used to achieve two important database goals:
| consistency | Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data. |
| integrity | Ensures that the database's data and structures reflect all changes made to them in the correct sequence. |
Locks guarantee data integrity while allowing maximum concurrent access to the data by unlimited users.
Automatic Locking
Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested.
Oracle's sophisticated lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized.
Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource (such as a row or a table); however, many share locks can be obtained 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
Under some circumstances, a user may want to override default locking. Oracle allows manual override of automatic locking features at both the row level (by first querying for the rows that will be updated in a subsequent statement) and the table level.