Re: Oracle Isolation Levels
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "fmhabash@xxxxxxxxx" <fmhabash@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Feb 2014 08:43:39 -0800 (PST)
I really believe the documentation isn't correct with respect to 'phantom
reads'. Phantom reads are not non-repeatable reads and I've never known Oracle
to generate a phantom read regardless of how many transactions are active
against a table. A non-repeatable read is illustrated here:
--
-- Session #1
--
SQL>
SQL> --
SQL> -- Update the EMP table and give a 10% raise
SQL> -- to all CLERKs
SQL> --
SQL>
SQL> update emp
2 set sal=1.1*sal
3 where job='CLERK';
4 rows updated.
SQL>
SQL> --
SQL> -- Allow a second session to query the EMP
SQL> -- table prior to a commit
SQL> --
SQL>
SQL> pause
--
-- Session #2
--
SQL> select *
2 from emp
3 where job = 'CLERK'
4 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL>
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL>
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL>
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7876 ADAMS CLERK 7788 12-JAN-83 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7934 MILLER CLERK 7782 23-JAN-82 1300
10
SQL>
SQL> pause
No matter how many times session #2 queries the EMP table prior to the commit
no phantom reads occur.
--
-- Session #1
--
SQL>
SQL> --
SQL> -- Commit the changes
SQL> --
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Have second session query EMP again
SQL> -- and experience a non-repeatable read
SQL> --
SQL>
SQL> pause
--
-- Session #2
--
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 880
20
7876 ADAMS CLERK 7788 12-JAN-83 1210
20
7900 JAMES CLERK 7698 03-DEC-81 1045
30
7934 MILLER CLERK 7782 23-JAN-82 1430
10
SQL>
Now session #2 sees the updated values; this is a non-repeatable read, not a
'phantom read'. Phantom reads occur when uncommitted data is returned to the
querying session and Oracle doesn't allow that to happen. It's part of the
read-consistency mechanism Oracle employs to ensure the data returned is
consistent based on the state of the data at the time the query begins.
On to your questions:
1) The SERIAZABLE isolation level is similar to READ_COMMITTED in that
it gurantees cosnstency for the life of the transaction. If this is the
case.why is it needed as a separate isolation level option?
The serializable isolation level extends the read-consistency to the entire
transaction, not just the query level. The transaction can 'see' changes
committed at the time the transaction began and only changes made by that
transaction for the duration of that transaction. Think if it as though the
serializable session is the only one modifying data as a query executed within
the scope of that transaction will return the same results regardless of how
long that transaction has been running or how many other users may have
modified the underlying data. It is different from the default of
READ_COMMITTED.
2) If the default allows phantom & non-repeatable reads, is this OK?
The default does not allow phantom reads, only non-repeatable reads after a
commit issued. And, yes, this is perfectly fine.
3) Did you ever have to change Oracle isolation level? If yes, what was your
real-life scenario?
I personally have never had to do it, and I've not seen it done at any location
where my services have been needed. This is not to say that it doesn't happen
in some environments, just that I've never been working in such an environment.
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Thursday, February 20, 2014 8:52 AM, fmh <fmhabash@xxxxxxxxx> wrote:
I have been doing some research on this topic & this is what I currently
understand ...
1) Oracle allows 3 isolation levels, the default is READ_COMMITTED.
2) This default guarantees data consistency at both statement as well
transaction level.
3) The default allows non-repeatable & phantom reads.
My questions are ...
1) The SERIAZABLE isolation level is similar to READ_COMMITTED in that it
gurantees cosnstency for the life of the transaction. If this is the case.why
is it needed as a separate isolation level option?
2) If the default allows phantom & non-repeatable reads, is this OK?
3) Did you ever have to change Oracle isolation level? If yes, what was your
real-life scenario?
Thanks to all.
--
----------------------------------------
Thank you.
Other related posts:
