Concurrency and Multiversioning - Oracle Database Transactions and Locking Revealed - page 56

■ The ansI/Iso sQl standard defines transaction -level characteristics, not just individual
statement-by-statement-level characteristics. In the following pages, we'll examine transaction-level isolation,
not just statement-level isolation.

Note

The SQL isolation levels are defined based on whether or not they allow each of the preceding phenomena.
I find it interesting to note that the SQL standard does not impose a specific locking scheme or mandate particular
behaviors, but rather describes these isolation levels in terms of these phenomena, allowing for many different
locking/concurrency mechanisms to exist (see Table 4-1 ).

Table 4-1. ANSI Isolation Levels

Isolation Level Dirty Read Nonrepeatable Read Phantom Read

READ UNCOMMITTED Permitted Permitted Permitted

READ COMMITTED

-- Permitted Permitted

REPEATABLE READ -- -- Permitted

SERIALIZABLE -- -- --

Oracle explicitly supports the READ COMMITTED and SERIALIZABLE isolation levels as they are defined in the
standard. However, this doesn't tell the whole story. The SQL standard was attempting to set up isolation levels that
would permit various degrees of consistency for queries performed in each level. REPEATABLE READ is the isolation
level that the SQL standard claims will guarantee a read-consistent result from a query. In their definition, READ
COMMITTED does not give you consistent results, and READ UNCOMMITTED is the level to use to get nonblocking reads.
However, in Oracle, READ COMMITTED has all of the attributes required to achieve read-consistent queries. In many
other databases, READ COMMITTED queries can and will return answers that never existed in the database at any point
in time. Moreover, Oracle also supports the spirit of READ UNCOMMITTED . The goal of providing a dirty read is to supply
a nonblocking read, whereby queries are not blocked by, and do not block, updates of the same data. However, Oracle
does not need dirty reads to achieve this goal, nor does it support them. Dirty reads are an implementation other
databases must use in order to provide nonblocking reads.
In addition to the four defined SQL isolation levels, Oracle provides another level, namely READ ONLY . A
READ ONLY transaction is equivalent to a REPEATABLE READ or SERIALIZABLE transaction that can't perform any
modifications in SQL. A transaction using a READ ONLY isolation level only sees those changes that were committed
at the time the transaction began , but inserts, updates, and deletes are not permitted in this mode (other sessions
may update data, but not the READ ONLY transaction). Using this mode, you can achieve REPEATABLE READ and
SERIALIZABLE levels of isolation.
Let's now move on to discuss exactly how multiversioning and read consistency fit into the isolation scheme
and how databases that do not support multiversioning achieve the same results. This information is instructive for
anyone who has used another database and believes she understands how the isolation levels must work. It is also
interesting to see how a standard that was supposed to remove the differences between the databases, ANSI/ISO SQL,
actually allows for them. The standard, while very detailed, can be implemented in very different ways.

READ UNCOMMITTED
The READ UNCOMMITTED isolation level allows dirty reads. Oracle does not make use of dirty reads, nor does it even
allow for them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that
caters for nonblocking reads. As we have seen, Oracle provides for nonblocking reads by default. You would be
hard-pressed to make a SELECT query block in the database (as noted earlier, there is the special case of a distributed