RE: Oracle Isolation Levels

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 20 Feb 2014 12:36:03 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF022A98A7_at_WIN02.hotsos.com>

I believe it's how you define a phantom read. Your example David shows that oracle will not allow what it calls a "dirty read". That is allowing you to read uncommitted data. That might be called a phantom read in other (lessor) databases but in oracle land it's called Dirty Read.  

Think of starting a read on a 10,000,000 row table, while your running some folks come in and change some rows you haven't read yet, and commit them. You wouldn't see the new committed data because your statement started before the changes happened. Then you run the select again right after it's done and you get different results. The first read could be regarded as a phantom read. With the default level you're guaranteed consentient results based on when the statement started, not any "transaction".  

So yes it does allow for non-repeatable reads. Is that OK? Well you need to decide if it's ok or not. Knowing how it works will give you the ability to make the right decision.  

I have used SERIAZABLE a couple of times in 20+ years. And it's likely that even those times I did I could have done things differently and not needed it. The longer you stay in the mode the more likely a ORA-1555 will be raised. What basically happens is oracle extents the Read Consistency model out to the Transaction level rather than just the Statement level.    

+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

Ric Van Dyke

Education Director

Hotsos Ltd.  

Hotsos Symposium March 2-6 2014

Make your plans to be there now!        

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Fitzjarrell Sent: Thursday, February 20, 2014 11:44 AM To: fmhabash_at_gmail.com; oracle-l_at_freelists.org Subject: 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 Received on Thu Feb 20 2014 - 19:36:03 CET