Re: How does Oracle support REPEATABLE READ

Look at Tom Kyte's book "expert one-on-one Oracle" for a very complete and lucid description.

What I wonder is why you are asking. And are you aware of multiversioning?

Daniel Morgan

Ben Brugman wrote:

> 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."
>
> 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
Received on Thu Apr 25 2002 - 17:26:32 CDT