Re: ISOLATION LEVEL

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, you most certainly should look at
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/consist.htm#sthref1934which describes concurrency in an Oracle database clearly and simply.

cheers

Niall

On 7/10/07, DBA Deepak <oracle.tutorials_at_gmail.com> wrote:
>
> It is a reporting application and we do not want to see inconsistent data.
> And all the queries will be in a single transaction. My doubt is which
> ISOLATION LEVEL should I choose.
>
> On 7/10/07, Niall Litchfield <niall.litchfield_at_gmail.com > wrote:
> >
> > Where does your requirement/code come from. Is your code not a logical
> > transaction? If so why do you need to see wrong/old data at later stages. If
> > it is a logical transaction then make it a single transaction and you'll
> > only see consistent data.
> >
> > Something doesn't sound quite right but without seeing either your code
> > or your requirements it's somewhat tricky to tell.
> >
> >
> >
> > On 7/10/07, DBA Deepak <oracle.tutorials_at_gmail.com > wrote:
> > >
> > > Hi All,
> > >
> > > Have a doubt on transaction isolation.
> > >
> > > My requirement is to query several tables several times inside a piece
> > > of code. The data needs to be consistent across multiple queries. The
> > > underlying tables are subjected to change during the execution of the piece
> > > of code.
> > >
> > > I have two options to be used inside my piece of code to achieve
> > > consistent/repeatable reads.
> > >
> > > 1> to use SET TRANSACTION READ ONLY;
> > > 2> to use ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
> > >
> > > My question is which option is preferable and why? Is there any
> > > performance issues with one of these in the above mentioned scenarios?
> > >
> > > Need your valuable feedback.
> > >
> > > --
> > > Regards,
> > >
> > > Deepak
> > > Oracle DBA
> > >
> >
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > http://www.orawin.info
>
>
>
>
> --
> Regards,
>
> Deepak
> Oracle DBA

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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