Re: Repeatable Read isolation (was Re: TPC-A and -B Benchmarks)

Charles Levine of Tandem just posted this lovely example of why Oracle Read Consistency falls short of Repeatable Read to comp.databases.oracle. As it pertains to the recent difference of opinion between snieuwen@nl.oracle.com ("Sandor (Mr. Oracle7)") and myself on this group I thought I would cross-post here FYE: In <levine_charles-210793204202@130.252.169.32> levine_charles@tandem.com (Charles Levine) writes: >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 >($200) >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 > 2. READ BALANCE FROM CHECKING >($100) > 3. 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@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. | >+-----------------------------------------------------------------------+ graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.