To prepare you to deal with the problems posed by multiuser concurrent access to data, we'll review some of the basic concepts that relate to concurrency.
The transaction is the bedrock of data integrity in multiuser databases and the foundation of all concurrency schemes. A transaction is defined as a single indivisible piece of work that affects some data. All of the modifications made to data within a transaction are uniformly applied to a database with a COMMIT statement, or the data affected by the changes is uniformly returned to its initial state with a ROLLBACK statement. Once a transaction is committed, the changes made by that transaction become permanent and are made visible to other transactions and other users.
Transactions always occur over time, although most transactions occur over a very short period of time. Because the changes made by a transaction aren't official until the transaction is committed, each individual transaction must be isolated from the effects of other transactions. The mechanism used to enforce transaction isolation is the lock.
A database uses a system of locks to prevent transactions from interfering with each other. Transactions can interfere with each other by allowing one transaction to change a piece of data that another transaction is also in the process of changing. Figure 7-1 illustrates a system without locks. Transaction A reads a piece of data; Transaction B reads the same piece of data and commits a change to the data. When Transaction A commits the data, its change unwittingly overwrites the changes made by Transaction B, resulting in a loss of data integrity.
Two types of locks are used to avoid this type of problem. The first is called a write lock, or an exclusive lock. An exclusive lock is applied and held while changes are made to data in the course of a transaction and released when the transaction is ended by either a COMMIT or a ROLLBACK statement. A write lock can be held by only one user at a time, so only one user at a time can change that data.
Some databases also use read locks, or shared locks. A read lock can be held by any number of users who are merely reading the data, because the same piece of data can be shared among many readers. However, a read lock prevents a write lock from being placed on the data, as the write lock is an exclusive lock. In Figure 7-1, if a read lock were placed on the data when Transaction A began, Transaction B would be able to read the same data but would be prevented from acquiring a write lock on the data until Transaction A ended.
Oracle uses read locks only when a SQL operation specifically requests them with the FOR UPDATE clause in a SELECT statement. You shouldn't use the FOR UPDATE clause routinely because it unduly increases the probability that readers will interfere with writersa situation that normally never occurs with Oracle, as you will see shortly.
The safeguards that enforce isolation between concurrent users of data can lead to their own problems if they're improperly implemented. As you can see from the example described above, a single transaction can cause significant performance problems as the locks it places on the database prevent other transactions from completing. The interference caused by conflicting locks is called contention. The more contention in a database, the slower the potential response times and throughput.
In most databases, increased concurrent access to data results in increased contention and decreased performance, in terms of both response time and throughput. Oracle's multiversion read concurrency scheme can greatly reduce contention, as you will see later in this chapter.
Some basic integrity problems can result if transaction isolation isn't enforced. Four of these problems are common to many databases:
Lost updates
The most common type of integrity problem. Occur when two writers are both changing the same piece of data, and one writer's changes overwrite the other writer's changes. This is the problem that exclusive locks are designed to prevent.
Dirty reads
Occur when a database allows a transaction to read data that has been changed by another transaction but hasn't been committed yet. The changes made by the transaction may be rolled back, so the data read may turn out to be incorrect. Many databases allow dirty reads to avoid contention caused by write locks.
Nonrepeatable reads
Occur as a result of changes made by another transaction. One transaction makes a query based on a particular condition. After the data has been returned to the first transaction, but before the first transaction is complete, another transaction changes the data so that some of the previously retrieved data no longer satisfies the selection condition. If the query is repeated in the same transaction, it would return a different set of results, so any changes made on the basis of the original results may no longer be valid. Data that was read once can return different results if it's read again later in the same transaction.
Phantom reads
Also occur as a result of changes made by another transaction. One transaction makes a query based on a particular condition. After the data has been returned to the first transaction, but before the first transaction is complete, another transaction inserts into the database new rows that meet the selection criteria for the first transaction. If the transaction is making changes based on the assumption that the only rows that satisfied the condition were returned, a phantom read could result in improper data. Although all the data read by the first query is returned for the second query, additional data also should have been returned, so any changes made on the basis of the original results may no longer be valid.
The goal of a complete concurrency solution is to provide the highest level of isolation between the actions of different users accessing the same data. As defined by the SQL92 standard, this highest level is called serializable. As the name implies, serializable transactions appear as though they have been executed in a series of distinct, ordered transactions. When one transaction begins, it is isolated from any changes that occur to its data from subsequent transactions.
To the user, a serializable transaction looks as though it has the exclusive use of the database for the duration of the transaction. Serializable transactions are predictable and reproducible, the two cardinal virtues of data integrity.
Of course, it's not trivial to have a database server support thousands of users while each one thinks he is the only one. But Oracle manages to pull off this quietly dramatic feat.