Re: How does Oracle support REPEATABLE READ

Hello,

How likely it will be for a late mail to give us a deeper insight on the topic of the transaction isolation level?

I hope that Thomas Kyte will apply some good intelligence to my strategy. I have already seen a message 'Do not use SET TRANSACTION with XA'.

benbrugman_at_onbekend.nl (Ben Brugman) wrote in message news:<3cc802e0.22565062_at_news.nl.uu.net>...
> Our developers use Enterprise JavaBeans.
> Choosen isolation level is REPEATABLE READ.
> Does Oracle give this isolation level (or better)
> as described below ?
> (Text is taken from a standard JavaBeans book).
>
> Thanks,
> Ben Brugman
>
> Environment :
> Borland Enterprise Server.
> Oracle JDBC driver.
>

Our developers use Tuxedo servers.
One application 'needs' isolation level REPEATABLE READ. It uses container (Tuxedo) managed XA transactions.

Please read below how we implemented this:

I was able to run 'serializable' XA transactions through and was able to observe the REPEATABLE READ isolation level, using the following scenario:

<<--ENVIRONMENT-->

Tuxedo 6.5, Oracle 9i, Solaris 8

<<--APPLICATION-->

Server0, Server1 and Server2 live in the same XA group in Tuxedo, and are connected to the same Oracle Resource (same DB instance). We use container-managed transactions (AUTOTRAN=Y).

<<--PROCESS-->>

The generic client ud32 calls service SERIALIZE (AUTOTRAN = Y)

                                        provided by Server0.
service SERIALIZE uses tpcall to call service TPTEST1 (AUTOTRAN=Y)

                                        provided by Server1.
service TPTEST1 uses tpforward to call service TPTEST2 (AUTOTRAN=Y)

                                        provided by Server2.

<<--FUNCTION-->>

service SERIALIZE does

     EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; service TPTEST1 and service TPTEST2 each does         

     EXEC SQL SELECT TPVALUE INTO :tpvalue FROM TPTEST WHERE TPKEY = 1;

     printf("Value One = %d\n", tpvalue);
     
     sleep(sleepin); 

     EXEC SQL SELECT TPVALUE INTO :tpvalue FROM TPTEST WHERE TPKEY = 1;

     printf("Value Two = %d\n", tpvalue);

 

<<--RESULTS-->>

All four select statements in this transaction return the same tpvalue, even when tpvalue has been changed and committed on the database by another concurrent transaction, after the transaction managed by Server0 starts and before it ends.

<<--FEEDBACK-->

It would be good to get some criticism on whether this scenario can possibly be part of a production application where most of the transactions are read only.

THANK YOU! Tatiana Djidjev

...

> Side question: On which ORACLE isolation level is the JDBC REPATABLE
> READ isolation level mapped ?
>
> Ben Brugman
Received on Mon Apr 29 2002 - 10:20:03 CDT