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.
A lower isolation
level increases the ability of many users to access data at the same time, but
increases the number of concurrency effects (such as dirty reads or lost
updates) users might encounter. Conversely, a higher isolation level reduces
the types of concurrency effects that users may encounter, but requires more
system resources and increases the chances that one transaction will block
another.[1]
In Oracle there
are following Isolation Levels:
READ COMMITTED (This is the default isolation
level in Oracle database)
SERIALIZABLE
There are more like Dirty Read and Repeatable
Read but these are I think not in Oracle. They be in other databases.
READ COMMITTED: A select
statement will see whatever has been committed (in other sessions).
Session 1:
SQL> set transaction isolation level read committed;
Transaction
set.
Session 2:
SQL>
update emp set sal=4000 where ename='SCOTT';
1 row
updated.
Session 1:
SQL> select
sal from emp where ename='SCOTT';
SAL
----------
3000
Session 2:
SQL>
commit;
Commit
complete.
Session 1:
SQL>
select sal from emp where ename='SCOTT';
SAL
----------
4000
SQL>
update emp set sal=3000 where ename='SCOTT';
1 row
updated.
SQL>
commit;
Commit
complete.
When the session 1 reads the salary of Scott, it
gets the value that is committed in the database.
SERIALIZABLE:
The table vik_emp
has below record:
Session 1:
SQL>
set transaction isolation level serializable;
Transaction
set.
Session 2:
SQL>
update vik_emp set salary=5000 where ename='SCOTT';
1 row
updated.
SQL>
commit;
Commit
complete.
Session 1:
SQL>
Select * from vik_emp;
Ename Salary
SCOTT 5000
Updated salary is shown here. But since we have selected
the data from vik_emp now, there is lock on it; Now if we update the salary in
Session 2 and commit that change will not reflect here.
Session 1:
update vik_emp set salary=6000 where ename='SCOTT';
commit;
Session 2:
select * from
vik_emp;
Still 5000 even
if we committed the update in session 1.
