Re: ISOLATION LEVEL

Thanks a lot Heman for the nice document that you pointed me to. My code only has queries only NO DML/DDL.

Just for the sake of curiousity if I go for isolation_level=serializable will there be any extra overhead. I want to know internally how do both of the options differ (if at all).

Deepak

On 7/10/07, Hemant K Chitale <hkchital_at_singnet.com.sg> wrote:
>
>
> If your code is going to also run DML, you would have to use SERIALIZABLE.
> Obviously, you cannot have other sessions attempting transactions on
> the same set of rows
> and your DML should be quick and short.
>
> If you are only querying repeatedly and need to see the same data
> across multiple statements, then you could
> go for READ ONLY.
> Note that SET TRANSACTION READ ONLY will *not* prevent you from
> executing DDL
> which, if executed, automatically will end the transaction.
> see
>
> http://hemantoracledba.blogspot.com/2007/06/read-consistency-across-statements.html
> and
> http://radiofreetooting.blogspot.com/2007/06/set-transaction-read-tfm.html
>
> Hemant K Chitale
>
> At 05:09 PM Tuesday, DBA Deepak 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
>
>
> Hemant K Chitale
> http://web.singnet.com.sg/~hkchital
> and
> http://hemantscribbles.blogspot.com
> and
> http://hemantoracledba.blogspot.com
>
> "First they ignore you, then they laugh at you, then they fight you,
> then you win" !"
> Mohandas Gandhi Quotes
> : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
>
>

-- 
Regards,

Deepak
Oracle DBA

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