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 not 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)