Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle ISOLATION Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: ISOLATION

Definition:
In Oracle PL/SQL, the ISOLATION level is a property of a transaction in a session. Isolation Levels are how Oracle executes SQL statements in regards to read consistency, and is directly related to what lock(s) may be ignored.

There are three types of ISOLATION levels:

READ COMMITTED: - This is the default Isolation Level. A SQL query can access only committed data but affected by other transaction changes. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query will never read dirty (uncommitted) data.

Example Usage:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED; (session level)


SERIALIZABLE TRANSACTIONS: A SQL query can access the data which has been committed before the current transaction has begun. But changes in the current transaction will be visible. Note that it cannot be set for distributed transactions. 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 will be seen.

Example Usage:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE; (session level)


READ ONLY: Data cannot be modified in the transaction but SQL query can access data committed before the current transaction. Only changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements will be seen.

Example Usage:

SET TRANSACTION ISOLATION LEVEL READONLY; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL READONLY; (session level)



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 53 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?