Database Reference
In-Depth Information
But Oracle's support for concurrency goes beyond efficient locking. It implements a multiversioning architecture
(introduced in Chapter 1) that provides controlled yet highly concurrent access to data. Multiversioning describes
Oracle's ability to simultaneously materialize multiple versions of the data and is the mechanism by which Oracle
provides read-consistent views of data (i.e., consistent results with respect to a point in time). A rather pleasant side
effect of multiversioning is that a reader of data will never be blocked by a writer of data. In other words, writes do
not block reads. This is one of the fundamental differences between Oracle and other databases. A query that only
reads information in Oracle will never be blocked; it will never deadlock with another session, and it will never get an
answer that didn't exist in the database.
There is a short period of time during the processing of a distributed Two Phase Commit where oracle will
prevent read access to information. as this processing is somewhat rare and exceptional (the problem applies only to
queries that start between the prepare and the commit phases and try to read the data before the commit arrives), I will
not cover it in detail.
Note
Oracle's multiversioning model for read consistency is applied by default at the statement level (for each
and every query) and can also be applied at the transaction level . This means that each and every SQL statement
submitted to the database sees a read-consistent view of the database, at least—and if you would like this read-
consistent view of the database to be at the level of a transaction (a set of SQL statements), you may do that as well, as
we'll see in the “Serializable” section in this chapter.
The basic purpose of a transaction in the database is to take the database from one consistent state to the next.
The ISO SQL standard specifies various transaction isolation levels , which define how sensitive one transaction is
to changes made by another. The greater the level of sensitivity, the greater the degree of isolation the database
must provide between transactions executed by your application. In the following section, we'll look at how,
via its multiversioning architecture and with absolutely minimal locking, Oracle can support each of the defined
isolation levels.
Transaction Isolation Levels
The ANSI/ISO SQL standard defines four levels of transaction isolation, with different possible outcomes for the
same transaction scenario. That is, the same work performed in the same fashion with the same inputs may result in
different answers, depending on your isolation level. These isolation levels are defined in terms of three “phenomena”
that are either permitted or not at a given isolation level:
Dirty read : The meaning of this term is as bad as it sounds. You are permitted to read
uncommitted, or dirty , data. You would achieve this effect by just opening an OS file that
someone else is writing and reading whatever data happens to be there. Data integrity is
compromised, foreign keys are violated, and unique constraints are ignored.
Nonrepeatable read : This simply means that if you read a row at time T1 and attempt to reread
that row at time T2, the row may have changed, or it may have disappeared, or it may have
been updated, and so on.
Phantom read : This means that if you execute a query at time T1 and re-execute it at time
T2, additional rows may have been added to the database, which will affect your results. This
differs from the nonrepeatable read in that with a phantom read, data you already read has not
been changed, but rather that more data satisfies your query criteria than before.
 
 
Search WWH ::




Custom Search