Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

7.1 Basics of Concurrent Access

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.

7.1.1 Transactions

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.

7.1.2 Locks

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.

Figure 7-1. Transactions over time

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.

7.1.3 Concurrency and Contention

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.

7.1.4 Integrity Problems

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.

7.1.5 Serialization

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.