Re: How does Oracle support REPEATABLE READ

In article <3cc813a4.26857562_at_news.nl.uu.net>, benbrugman_at_onbekend.nl says...
>
>On Thu, 25 Apr 2002 18:06:44 +0400, "Vladimir M. Zakharychev"
><bob_at_dpsp-yes.com> wrote:
>
>>The only isolation levels supported in Oracle are READ COMMITTED (default)
>>and SERIALIZABLE. Excerpt from the docs:
>>SERIALIZABLE indicates that transactions in the session use the serializable
>>
>>transaction isolation mode as specified in SQL92. That is, if a serializable
>>transaction
>>
>>attempts to execute a DML statement that updates rows currently being updated by
>>
>>another uncommitted transaction at the start of the serializable transaction,
>>then the
>>
>>DML statement fails.
>
>From :
> (Second Informal Review Draft) ISO/IEC 9075:1992, Database
> Language SQL- July 30, 1992
>Extracts :
>"The execution of concurrent SQL-transactions at isolation level
>SERIALIZABLE is guaranteed to be serializable. A serializable
>execution is defined to be an execution of the operations of
>concurrently executing SQL-transactions that produces the same effect
>as some serial execution of those same SQL-transactions. A serial
>execution is one in which each SQL-transaction executes to completion
>before the next SQL-transaction begins."
>

same doc describes the isolation levels in conjunction with 3 phenomena --

o dirty read
o non-repeatable read
o phantom read

They define the isolation levels then as:

                     dirty read        non-repeatable read       phantom read
read uncommitted     permitted         permitted                 permitted
read committed       NO                permitted                 permitted
repeatable read      NO                NO                        permitted
serializable         NO                NO                        NO

and in fact, ansi compatibility with the standard has been shown. The sql 92 spec offers two different definitions in different places.

You might be interested in
http://www.cs.pitt.edu/~panos/teaching/d3550/ansiSQL_sigmod95.pdf

it says, amongst other things, that:

...
This paper shows a number of weaknesses in the anomaly approach to defining isolation levels. The three ANSI phenomena are ambiguous, and even in their loosest interpretations do not exclude some anomalous behavior that may arise in execution histories. This leads to some counter-intuitive results. In particular, lock-based isolation levels have different characteristics than their ANSI equivalents. This is disemeerting because commercial database systems typically use locking implementations. Additionally, the ANSI phenomena do not distinguish between a number of types of isolation level behavior that are popular in commercial systems. Additional phenomena to characterize these isolation levels are suggested here. .......

>Oracle definitively does not support the serializable as described
>above. It's fairly easy to create two concurrend transactions under
>Oracle with the isolation level set to serializable which run together
>can result in a database which can not be the result of the
>two transactions run after each other in any order.
>(The DML statement does not fail in that case as it should under the
>ISO/IEC 9075:1992).
>
>The question remains for the ORACLE JDBC driver does
>this have a REPEATABLE READ level.
>Is this level compliand to the level described in the
>original message ?
>If not then ORACLE does not have a compliant JDBC driver.
>And Oracle is not Java compliant.
>If it is Java compliant how is it implemented ?
>
>ben brugman
>
>
>
>
>> A serializable transaction can see its own updates.
>>
>>READ COMMITTED indicates that transactions in the session will use the default
>>
>>Oracle transaction behavior. Thus, if the transaction contains DML that requires
>>row
>>
>>locks held by another transaction, then the DML statement will wait until the
>>row locks
>>
>>are released.
>>
>>
>>--
>>Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
>>Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
>>applications.
>>All opinions are mine and do not necessarily go in line with those of my
>>employer.
>>
>>
>>"Ben Brugman" <benbrugman_at_onbekend.nl> 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.
>>>
>>>
>>> From: Mastering Enterprise JavaBeans.
>>>
>>> "When to Use REPEATABLE READ
>>> Use REPEATABLE READ when you need to update one or more data elements
>>> in a resource, such as one or more records in a relational database.
>>> You want to read each of the rows that you're modifying and then be
>>> able to update each row, knowing that none of the rows are being
>>> modified by other concurrent transactions. If you choose to reread any
>>> of the rows at any time later in the transaction, you'd be guaranteed
>>> that the rows still have the same data that they did at the beginning
>>> of the transaction."
>>>
>>> JDBC drivers should support the REPEATABLE READ isolation level.
>>> When using the Oracle JDBC driver will it support the REPEATABLE READ
>>> isolation level with the functionality as stated above? Especially the
>>> part : "knowing that none of the rows are being modified by other
>>> concurrent transactions" ?
>>>
>>>
>>> Side question: On which ORACLE isolation level is the JDBC REPATABLE
>>> READ isolation level mapped ?
>>>
>>> Ben Brugman
>>
>
>Ben Brugman

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp