| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How does Oracle support REPEATABLE READ
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.
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."
On 25 Apr 2002 11:59:48 -0700, oratune_at_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
Received on Fri Apr 26 2002 - 04:18:39 CDT
 |
 |