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

7.3 Oracle's Isolation Levels

Oracle, like many other databases, uses the concept of
isolation levels to
describe how a transaction will interact with other transactions and
how it will be isolated from other transactions. An isolation level
is essentially a locking scheme implemented by the database that
guarantees a certain type of transaction isolation.

An application programmer can set an isolation level at the session
level (ALTER SESSION) or transaction level (SET TRANSACTION). More
restrictive isolation levels will cause more potential contention, as
well as delivering increased protection against data integrity
problems.

Two basic isolation levels are used frequently within Oracle:
READ COMMITTED and SERIALIZABLE.
(A third level, READ ONLY, is described later in this section.) Both
of these isolation levels create serializable database operations.
The difference between the two levels is in the duration for which
they enforce serializable operations:

READ COMMITTED

Enforces serialization at the statement level. This means that every
statement will get a consistent view of the data as it existed at the
start of that statement. However, because a transaction can contain
more than one statement, it's possible that
nonrepeatable reads and phantom reads can occur within the context of
the complete transaction. The READ COMMITTED isolation level is the
default isolation level for Oracle.

SERIALIZABLE

Enforces serialization at the transaction level. This means that
every statement within a transaction will get the same consistent
view of the data as it existed at the start of the transaction.

Because of their differing spans of control, these two isolation
levels also react differently when they encounter a transaction that
blocks their operation with a lock on a requested row. Once the lock
has been released by the blocking transaction, an operation executing
with the READ COMMITTED isolation level will simply retry the
operation. Because this operation is concerned only with the state of
data when the statement begins, this is a perfectly logical approach.

On the other hand, if the blocking transaction commits changes to the
data, an operation executing with a SERIALIZABLE isolation level will
return an error indicating that it cannot serialize operations. This
error makes sense, because the blocking transaction will have changed
the state of the data from the beginning of the SERIALIZABLE
transaction, making it impossible to perform any more write
operations on the changed rows. In this situation, an application
programmer will have to add logic to his program to return to the
start of the SERIALIZABLE transaction and begin it again.

There are step-by-step examples of concurrent access later this chapter (in Section 7.6) that illustrate the different ways in which Oracle responds to this type of problem.

One other isolation level is supported by Oracle: you can declare
that a session or transaction has an isolation level of READ ONLY. As
the name implies, this level explicitly prohibits any write
operations and provides an accurate view of all the data at the time
the transaction began.