Re: How does Oracle support REPEATABLE READ

On 26 Apr 2002 06:29:59 -0700, Thomas Kyte <tkyte_at_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 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 Received on Fri Apr 26 2002 - 09:45:09 CDT