Re: Oracle Isolation Levels

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_at_gmail.com> 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.

--
http://www.freelists.org/webpage/oracle-l