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@xxxxxxxxxxxxxx> 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

Other related posts: