Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ISOLATION LEVEL

Re: ISOLATION LEVEL

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 10 Jul 2007 23:38:10 +0800
Message-Id: <200707101538.l6AFcJCj015807@smtp18.singnet.com.sg>

Niall,

Also read in the same article by Tom Kyte, in the section on READ COMMITTED (the default mode) :

The READ COMMITTED isolation level states that a transaction may read only data that has been committed in the database. There are no dirty reads (reads of uncommitted data). There may be nonrepeatable reads (that is, rereads of the same row may return a different answer in the same transaction) and phantom reads (that is, newly inserted and committed rows become visible to a query that weren't visible earlier in the trans-action).

What Deepak needs is REPEATABLE READs. Furthermore, he wants to prevent PHANTOM READs.
That is why he is asking for either of SERIALIZABLE or READ ONLY. The difference between SERIALIZABLE and READ ONLY (also explained in Tom Kyte's article)
is that READ ONLY does not allow any DML by the transaction. Both SERIALIZABLE and READ ONLY meet Deepak's requirements.

Hemant K Chitale

At 09:13 PM Tuesday, Niall Litchfield wrote:
>Oracle will always return consistent data from standard selects,
>unless you try extremely hard to shoot yourself in the head. code
>that looks like this
>
>begin
>
>select cols from tab1 where ...;
>select more cols from tab2 where...;
>select even more cols from tab3 where ...;
>do some calculations and validations.
>select yet more cols from tab4 based on the earlier calcs;
>
>end;
>
>will be consistent. What Oracle is doing essentially is the following
>
>begin -- note that I have started a transaction at SCN x .
>
>select cols from tab1 where ...; as of x
>select more cols from tab2 where...; as of x
>select even more cols from tab3 where ...; as of x
>do some calculations and validations.
>select yet more cols from tab4 based on the earlier calcs; as of x
>
>end; -- note the transaction has now ended.
>
>when Oracle comes to query the tables it will built a version of the
>datablocks in memory as of SCN x. Later changes will not be seen.
>This comes at a cost of course, Oracle needs access to the undo data
>to rebuilt the blocks it will need to satisfy the query You might
>also wish to look at
><http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html>http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html,
>you most certainly should look at
><http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#sthref1934>http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#sthref1934
>which describes concurrency in an Oracle database clearly and simply.
>
>cheers
>
>Niall
>
>

Hemant K Chitale

http://hemantoracledba.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 10 2007 - 10:38:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US