Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How does Oracle support REPEATABLE READ

Re: How does Oracle support REPEATABLE READ

From: David Fitzjarrell <oratune_at_msn.com>
Date: 25 Apr 2002 11:59:48 -0700
Message-ID: <32d39fb1.0204251059.1db64b00@posting.google.com>


> Oracle definitively does not support the serializable as described
> above.

Serializable isolation permits concurrent transactions to make only those database changes they could have made if the transactions had been scheduled to execute one after another. Specifically, Oracle permits a serializable transaction to modify a data row only if it can determine that prior changes to the row were made by transactions that had committed when the serializable transaction began.

> ... 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).
>

Then prove it. Show us these transactions and the results. I've tried and I can't do it (Oracle 8.1.6.3.8):

Connection #1 --

SQL> alter session set isolation_level=serializable;

Session altered.

SQL> update emp set comm=nvl(comm, 0)*50;

14 rows updated.

Connection #2, run at the same time as the previous transaction --

SQL> alter session set isolation_level=serializable;

Session altered.

SQL> update emp set sal=sal*5;
update emp set sal=sal*5

       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

This second transaction will not complete until the first update has been committed:

Connection #1 --

SQL> commit
  2 ;

Commit complete.

Connection #2 --

SQL> update emp set sal=sal*5;

14 rows updated.

So if you CAN accomplish this feat please show us how you did it. We could all learn something.

benbrugman_at_onbekend.nl (Ben Brugman) wrote in message news:<3cc813a4.26857562_at_news.nl.uu.net>...
> 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 - 13:59:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US