Re: ISOLATION LEVEL

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

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