Isolation Levels

From Robs_Wiki
Jump to: navigation, search
*************************************************************
This article is being delivered in Draft form and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************

PURPOSE
-------

The purpose of this article is to explain the concepts around the error:
ORA-08177: Cannot serialize access for this transaction 

SCOPE & APPLICATION
-------------------

The intended audience are PL/SQL developers and database administrators who
are familiar with basic database concepts.
 
First, let's review the three preventable situations that can occur in
concurrent transactions:

dirty read : A transaction reads data that has been written by another
             transaction that has not been committed yet.

nonrepeatable (fuzzy) read : A transaction rereads data it has previously
       read and finds that another committed transaction has modified or deleted
       the data.

phantom read : A transaction re-executes a query returning a set of rows
               that satisfies a search condition and finds that another
               committed transaction has inserted additional rows that
               satisfy the condition.

SQL92 defines four levels of isolation in terms of the phenomena a transaction
running at a particular isolation level is permitted to experience :

isolation level   | dirty read | nonrepeatable read | phanton read 
-------------------------------------------------------------------
read uncommited   |    x       |          x         |       x
read commited     |            |          x         |       x
repeatable read   |            |                    |       x
serializable      |            |                    |        
-------------------------------------------------------------------

Oracle provides these transaction isolation levels. These isolation levels
are defined in terms of three phenomena that must be prevented between 
concurrently executing transactions. Oracle offers the read committed and
serializable isolation levels, as well as a read-only mode that is not part 
of SQL92.


Read commited : This is the default transaction isolation level. Each query
                executed by a transaction sees only data that was committed
                before the query (not the transaction) began. An Oracle query
                never reads dirty (uncommitted) data. Because Oracle does not
                prevent other transactions from modifying the data read by a 
                query, that data can be changed by other transactions between 
                two executions of the query. Thus, a transaction that executes
                a given query twice can experience both nonrepeatable read and 
                phantoms.

Serializable : Serializable transactions see only those changes that were
               committed at the time the transaction began, plus those
               changes made by the transaction itself through INSERT,
               UPDATE, and DELETE statements. Serializable transactions
               do not experience nonrepeatable reads or phantoms.

Read-only : Read-only transactions see only those changes that were
            committed at the time the transaction began and do not allow
            INSERT, UPDATE, and DELETE statements.

You can set the transaction isolation level using the following statements :

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

Additionally, you can set a transaction to read only by:
SET TRANSACTION READ ONLY;


Oracle generates an error when a serializable transaction tries to update
or delete data modified by a transaction that commits after the serializable
transaction began:

ORA-08177: Cannot serialize access for this transaction

When a serializable transaction fails with the "Cannot serialize access" error, the
application can take any of several actions :

- Commit the work executed to that point
- Execute additional (but different) statements (perhaps after rolling back to a
savepoint established earlier in the transaction)
- Roll back the entire transaction