Re: distributed concurrency in oracle 7.0

In article <22gut1$3rd_at_infochi.com>, rfinkel_at_infochi.com (Richard Finkelstein) wrote:
>
> Sameer Soni (sameers_at_cs.tamu.edu) wrote:
> : Hi!
> : Can someone tell me how oracle scheduler maintains the concurrency? The only
> : thing I could get from the product info was that it uses multiversion data
> : items and does not require any read locks. I am sort of confused. I have a
> : background of concurrency control including Multiversion
> : TO, Multiversion 2PL,... . I will be thankful if somene could put some light.
> : and It seems the scheduler does not provide serializability by default. I need
> : to know about that also. In this respect, I read a paper titled "Single-Level
> : Multiversion Schedulers for Multilevel Secure Database Systems". This paper
> : discusses scheduler algorithms implemented for Trusted Oracle. The first author
> : of this paper is Mr. William T. Maimone of Oracle Corportaion. Somewhere, this
> : paper says that standard Oracle product provides read-consistency but does not
> : provide repeatable reads that makes its default locking strategy different from
> : serializability. Well, I didn't understand properly because I don't know how
> : Oracle's default locking strategy works. However, it further says that in terms
> : of serializability, the behavior of Oracle is quite similar to that of other
> : commercial DBMS products. So, does that mean that most of the commercial DBMS
> : products sacrifice serializability? Could somebody give me some examples?
> : Thanks a lot.
> : sameer
>
> Every RDBMS is somewhat different in their implementation. For example,
 ... stuff deleted ...
>
> Oracle can enforce repeatable read (no phantom inserts or updates) but to
> do so it must lock the full table. The SELECT FOR UDPATE places exclusive
> locks on all records at the time the cursor is OPENED (not as they are
> read). Since Oracle physically updates the data page when it places this
> exclusive lock, it incurs extra overhead. Oracle's default is to place no
> locks on records which means that programmers must check for record
> changes before updating the record (program controlled optimistic
> technique). There is no way to implement cursor stability.
>
> Hope this helps.
>
> -- Rich
> --
> Richard Finkelstein Voice: 312-549-8325
> Performance Computing, Inc. Fax: 312-549-4824
> Chicago, IL Mail: rfinkel_at_infochi.com

Oracle's read consistency approach can produce incorrect results for simple

update transactions run concurrently.

Consider an example using two typical banking transactions to illustrate the
difference between consistent reads and repeatable reads. Transaction T1 transfers money from a customer's checking to savings account. Transaction T2
bills a service charge if the balance of checking and savings is below $500.

Using repeatable reads, these transactions always produce the correct results
when run concurrently with each other. This is not the case with consistent
reads.

For example, before either transaction a particular savings account has $200
and the corresponding checking account has $400, for a sum of $600. (See the
diagram below.) T2 starts and reads the balance from savings ($200). T1 then
runs and transfers $300 from checking to savings, resulting in savings having
$500 and checking having $100. T2 now continues and reads the balance from

checking ($100). T2 computes the sum (200+100=$300), and determines that the
balance of the two accounts is below $500. T2 updates checking to subtract $5
as a service charge. But this is the wrong answer, since savings plus checking
was more than $500.

DIAGRAM:

T1: Transfer $300 from checking to Time T2: Bill $5 service charge if balance

    savings                           ----       of checking and savings is
below
                                        |        $500.
                                        v
                                             1. READ BALANCE FROM SAVINGS

1. READ BALANCE FROM CHECKING ($400)
2. verify that BALANCE > $300.
3. compute new balance (400-300=100)
4. WRITE BALANCE TO CHECKING ($100)
5. READ BALANCE FROM SAVINGS ($200)
6. compute new balance (200+300=500)
7. WRITE BALANCE TO SAVINGS ($500)
8. COMMIT TRANSACTION
9. READ BALANCE FROM CHECKING ($100)
10. if sum (200+100) < 500 then

                                                compute new balance
(100-5=95)
                                                WRITE BALANCE TO CHECKING
($95)
                                             4. COMMIT TRANSACTION

Since no row was read more than once within each transaction, it would seem

that Repeatable Reads would not be needed. But without Repeatable Reads, you
get the wrong answer. So the difference between consistent reads and repeatable reads is much more subtle than whether records are read more than
once within a transaction. Repeatable Reads guarantee that transactions do

not have stale data. This is one of the problems with consistent reads as implemented in Oracle -- the data, although consistent at the statement level,
can be out of date (stale) at the transaction level.

Oracle manuals say to use the construct "SELECT FOR UPDATE" to get repeatable reads, or to indicate that a record which is being read will subsequently be modified. This prevents other transactions from changing the
row after the first transaction has looked at it. (It's easy to see why this
would be a problem, since the transaction might want to write a new value based on the old one.) Interestingly, even if SELECT FOR UPDATE is used for
all of the READs which are followed by WRITEs in this example, you still get
the wrong answer.

In spite of statements in Oracle manuals to the contrary, using the default

locking and options (i.e., consistent reads) produces the wrong answer in this
example. The manual says: "In all cases, necessary locks are obtained automatically by ORACLE when a SQL statement is executed; users need not be

concerned with such details. ORACLE automatically locks data at the lowest

level of restrictiveness to provide the highest degree of data concurrency yet
provide fail-safe data integrity." The above trivial example provides evidence
to the contrary.

+-----------------------------------------------------------------------+
| Charles Levine                      email:  levine_charles_at_tandem.com |
| Tandem Computers                                                      |
| 19333 Vallco Pkwy, LOC 252-10       phone:  (408) 285-5294            |
| Cupertino, CA  95014                fax:    (408) 285-5245            |
|                                                                       |
| Disclaimer:  I speak for myself.  The opinions expressed here are     |
|              my own, and not necessarily those of my employer; no     |
|              connection should be implied or construed.               |
+-----------------------------------------------------------------------+