Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

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 or transaction level with a SQL statement, such as ALTER SESSION or SET TRANSACTION, respectively. A developer should weigh the potential conflicts that will result from a more restrictive isolation level with the benefits that it will deliver in terms of 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, since 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. ...