Re: How does Oracle support REPEATABLE READ

In article <3cc904d0.802531_at_news.nl.uu.net>, benbrugman_at_onbekend.nl says...
>
>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, 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).
>

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

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

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.

>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 (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