How does Oracle support REPEATABLE READ
Ben Brugman
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
Vladimir M. Zakharychev
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. 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 (b...@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" <benbr...@onbekend.nl> wrote in message news:3cc802e0...@news.nl.uu.net...
Ben Brugman
<b...@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
Ben Brugman
Peter Sylvester
transaction" statement to implement the setTRansactionIsolation()
method. You could probably verify this by using the trace mode. See the
SQL Reference and Concepts manual for the fine print on each supported
isolation mode.
-Peter
David Fitzjarrell
> 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.
benbr...@onbekend.nl (Ben Brugman) wrote in message news:<3cc813a4...@news.nl.uu.net>...
Daniel Morgan
What I wonder is why you are asking. And are you aware of multiversioning?
Daniel Morgan
Thomas Kyte
>
>On Thu, 25 Apr 2002 18:06:44 +0400, "Vladimir M. Zakharychev"
><b...@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.
.......
--
Thomas Kyte (tk...@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
Ben Brugman
Not all the messages are appearing in the newsgroups.
I did send some answers yesterday, but they do not
appear in the newsgroup (as I see it) and not on Google.
The answer of Thomas Kyte does appear in Google but
not in the newgroup. So if you see two answers from me
sorry for that.
Why this discussion; our EJB developers using JDBC expect
a certain behavior from their end of the connection. I have some
experience with the Oracle Server but can only guess how this
behavior appears on the other side off the JDBC channel.
To be Java compliant there should be a "REPEATABLE READ"
isolation level with a described behavior. I "GUESS/THINK" that
the 'REPEATABLE READ" is implemented as the "SERIALIZABLE"
level in Oracle. This level does not offer
"...knowing that not of the rows are being modified by other
concurrent transactions."
=================================================
But as I said I GUESS/THINK this is the case, the developers
and myself would like to know for sure.
=================================================
On 25 Apr 2002 11:59:48 -0700, ora...@msn.com (David Fitzjarrell)
wrote:
>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):
>So if you CAN accomplish this feat please show us how you did it. We
>could all learn something.
Business rule is : ONE PING ONLY.
(In the column B3 in table tB there is only one row allowed
which contains the word PING).
Transaction 1 :
select * from tB
set transaction isolation level serializable
select * from tB
/* Business rule check if there is already a ping if do not
proceed.*/
update tB set B3='Ping' where B1 = 'AA'
select * from tB
commit
Transaction2 :
select * from tB
set transaction isolation level serializable
select * from tB
/* Business rule check if there is already a ping if do not proceed.*/
update tB set B3='Ping' where B1 = 'BB'
select * from tB
commit
Running both transactions concurrently, it is possible that
both transactions finish and after both transactions are finished
there are two rows with a PING.
(There can only be one.).
(To force this effect in a testing environment, pause before the
commit, then run the other transaction).
Other examples from reality :
1.
Bank account, money transactions are done by adding a row
for each transaction. Offcourse the bankaccount is not allowed
to become sub zero. So the transaction exists of reading all info
of the account, see if the money transaction is allowed then
execute the transaction.
2.
Two partners having both an account. The business rule is both
accounts together can not become negative. But each individual
account is allowed to become negative.
3.
Physicians describing medicines have to read the complete
medication folder of a patient. Then he is allowed to add, change
or delete medication. If during the transaction any change is done
on the medication of the folder the physician is not allowed to
continue.
Offcourse the above problems can be solved with placing the
right locks, or with optimistic updating algorithms or with a
changenumber.
But one would expect, (using texts as given in the thread) that
one transaction is isolated from an other transaction, that rules
can be applied and that the transactions are serializable. (Meaning
that there is an order of executing the transactions after each
other which gives the same results).
If business rules can not be enforced by the serialization isolation
level as described in the iso standard and in loads of textbooks
then what is the use of isolation levels in connection to business
rules.
Key phrases from books :
Mastering Enterprise JavaBeans:
"...knowing that not of the rows are being modified by other
concurrent transactions."
The Essential Client/Server Survival Guide. (Orfali/Harkey/Edwards)
"....after a transaction executes, it must leave the system in a
correct state or it must abort"
"A multi-user program running under transaction protection must
behave exactly as it would in a single-user environment."
There are lot of texts about isolation in all kinds of books,
developers reading these books expect a certain behavior of
the RDBMS.
Often I have had this discussion with developers, they don't
believe the described effect, because their assumptions used
during development where often wrong. Also the word serialization
in the dictionary at least suggests that the transactions should
deliver a same outcome as run is series.
Business rules are often enforced with coding in a transaction.
Developers expect that if the transaction is correct that the
database will be left in a correct state.
If this is not the case Developers should be warned against
these effects.
(I do not see Thomas Kyte's answer in my newsreader, but in Google so
I'll respond here).
Thomas Kyte wrote :
>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
I do not see two different definitions but two descriptions which
together define serializable. I think that compliand means compliand
to the hole text and not to parts. The two parts are not in
disagreement with each other. Both requirements can be implemented
at the same time.
The ISO standard describes isolation levels as avoiding the
anomalies dirty reads/nonrepeatable reads/phantoms AND
as the given text :
>>
>> 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."
>>
A RDBMS should comply to both of the descriptions of serializable and
not to only a part of the total document.
(Or books and texts should be altered so that the actual behavior is
described, so that developers know that they have to take precautions
to make their transactions with business rules serializable).
Thanks for all your attention,
ben brugman.
Ben Brugman
Thomas Kyte
>
>FIRST MY APPOLOGIES :
>Not all the messages are appearing in the newsgroups.
>I did send some answers yesterday, but they do not
>appear in the newsgroup (as I see it) and not on Google.
>The answer of Thomas Kyte does appear in Google but
>not in the newgroup. So if you see two answers from me
>sorry for that.
>
first -- expect major differences (just EXPECT major differences) from all
databases with regards to concurrency control. It is a major, huge difference
between them.
>Why this discussion; our EJB developers using JDBC expect
>a certain behavior from their end of the connection. I have some
>experience with the Oracle Server but can only guess how this
>behavior appears on the other side off the JDBC channel.
>To be Java compliant there should be a "REPEATABLE READ"
>isolation level with a described behavior. I "GUESS/THINK" that
>the 'REPEATABLE READ" is implemented as the "SERIALIZABLE"
>level in Oracle. This level does not offer
>"...knowing that not of the rows are being modified by other
>concurrent transactions."
serializable will give you repeatable read. Make sure you test lots with this,
playing with the initrans on the objects to avoid the "cannot serialize access"
errors you will get otherwise (in other databases, you will get "deadlocks", in
Oracle "cannot serialize access")
>=================================================
>But as I said I GUESS/THINK this is the case, the developers
>and myself would like to know for sure.
>
>=================================================
>
>On 25 Apr 2002 11:59:48 -0700, ora...@msn.com (David Fitzjarrell)
>wrote:
>>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):
>
>>So if you CAN accomplish this feat please show us how you did it. We
>>could all learn something.
>
>Business rule is : ONE PING ONLY.
>(In the column B3 in table tB there is only one row allowed
>which contains the word PING).
>
and anyone who doesn't use declaritive integrity constraints to implement this
business rule is not doing it the right way. Enforcing a unique constraint with
procedural code is not a very bright idea.
The funny thing is if you run this scenario below in a database that uses locks
to physically serialize -- you will get a deadlock. The transaction will not go
through anyway. The select * from tb will put shared locks (not in oracle) on
the rows -- both updates will block on these shared locks -- deadlock, game
over, you lose.
Simply using a UNIQUE constraint, problem solved -- consistent behavior in
pretty much every database I can think of....
Yes, and you have to serialize at the ACCOUNT level. In a database with read
locks -- two people doing this at the same time would again DEADLOCK.... So the
transaction fails (maybe thats why my ATM refuses to give me money ;)
Again, there is a correct and proper way to implement this business rule.
>2.
>Two partners having both an account. The business rule is both
>accounts together can not become negative. But each individual
>account is allowed to become negative.
Same thing as #1.
>3.
>Physicians describing medicines have to read the complete
>medication folder of a patient. Then he is allowed to add, change
>or delete medication. If during the transaction any change is done
>on the medication of the folder the physician is not allowed to
>continue.
>
Same thing as #1.
>Offcourse the above problems can be solved with placing the
>right locks, or with optimistic updating algorithms or with a
>changenumber.
The right locks -- that's the ticket. Highly concurrent without DEADLOCKS all
over the place.
>But one would expect, (using texts as given in the thread) that
>one transaction is isolated from an other transaction, that rules
>can be applied and that the transactions are serializable. (Meaning
>that there is an order of executing the transactions after each
>other which gives the same results).
>
>If business rules can not be enforced by the serialization isolation
>level as described in the iso standard and in loads of textbooks
>then what is the use of isolation levels in connection to business
>rules.
>
Did you read the sigmond paper? There is more then one way to crack this nut.
Databases are different, this area (concurrency controls) is the biggest area of
differences.
>Key phrases from books :
>Mastering Enterprise JavaBeans:
>"...knowing that not of the rows are being modified by other
>concurrent transactions."
>The Essential Client/Server Survival Guide. (Orfali/Harkey/Edwards)
>"....after a transaction executes, it must leave the system in a
>correct state or it must abort"
>"A multi-user program running under transaction protection must
>behave exactly as it would in a single-user environment."
>
excellent statements, agree 100% with them all.
--
Ben Brugman
>>Business rule is : ONE PING ONLY.
>>(In the column B3 in table tB there is only one row allowed
>>which contains the word PING).
>>
>
>and anyone who doesn't use declaritive integrity constraints to implement this
>business rule is not doing it the right way. Enforcing a unique constraint with
>procedural code is not a very bright idea.
The business rule was One Ping only, it didn't say anything about the
column being unique. Secondly the example was only provided to show
that there are transactions which sometimes can not be serialized.
The question was "Show us these transactions and the results.", not
give us an example which we can not solve in another way.
Other database will give you deadlocks, I have no problem
with that, deadlock detection takes care of that (in some databases).
I could rewrite the query as :
set transaction isolation level serializable;
update tB set B3='PING' where B1 = 'BB' and not exists(select * from
tb where B3='PING');
commit
Other (some) databases do not deadlock on the above, and still
do produce a serializable result.
In Oracle you still can get a result which can not be obtained by
the same transactions played after each other. This is a simple
example where rows can be altered by another proces.
There are loads of business rules which can not be enforced in the
database. In the third example (medication for a patient) the business
rules are in the head of the physician and are different for different
patients. (Yes they depend on the judgement of the physician as well,
so different physicians have different sets of rules as well).
So they can not even be enforced by an automated system.
>>Key phrases from books :
>>Mastering Enterprise JavaBeans:
>>"...knowing that not of the rows are being modified by other
>>concurrent transactions."
....
>
>excellent statements, agree 100% with them all.
The text
"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."
During the example transaction rows with the same Primairy Keys
are being modified by other concurrent transactions. The READ rows can
be updated by concurrent transactions, not the updated rows. Which
breaks the above rule in my oppinion. So for Oracle this excellent
statement should not be used.
By now I know (almost for sure) the answer to my original question.
Applications and developers have to take into account that read rows
can still be altered by concurrend processes in the database.
ben brugman
Ben Brugman
Thomas Kyte
>
>On 26 Apr 2002 06:29:59 -0700, Thomas Kyte <tk...@oracle.com> wrote:
>>>Business rule is : ONE PING ONLY.
>>>(In the column B3 in table tB there is only one row allowed
>>>which contains the word PING).
>>>
>>
>>and anyone who doesn't use declaritive integrity constraints to implement this
>>business rule is not doing it the right way. Enforcing a unique constraint with
>>procedural code is not a very bright idea.
>
>The business rule was One Ping only, it didn't say anything about the
>column being unique. Secondly the example was only provided to show
k, concede that - misread the req. enter function based index.
create unique index on t( decode( b3, 'PING', b3, null ) );
that would do it.
>that there are transactions which sometimes can not be serialized.
> The question was "Show us these transactions and the results.", not
>give us an example which we can not solve in another way.
>
>Other database will give you deadlocks, I have no problem
>with that, deadlock detection takes care of that (in some databases).
and other databases (other then oracle) will not. The basic point, my bottom
line (enough said on this, this is my last followup) is this:
databases differ FUNDEMENTALLY from eachother in concurrency controls. There
are two camps with many products in each:
o those that lock
o those that do not
If you are going to develop "database independent" applications, you must be
aware of this. Matters not how many papers you read, this is the way that it
works -- for better or worse. It is simply a matter of that (in real life)
....
>ben brugman
Ben Brugman
>line (enough said on this, this is my last followup) is this:
Thanks for your insight in this.
I have learned form this thread, thanks for that,
ben brugman
Tatiana Djidjev
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'.
benbr...@onbekend.nl (Ben Brugman) wrote in message news:<3cc802e0...@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
...