Re: distributed concurrency in oracle 7.0
From: <bantonuk_at_Ingres.COM>
Date: 23 Jul 93 04:27:10 GMT
Message-ID: <1993Jul23.042710.2193_at_pony.Ingres.COM>
>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
>($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_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. |
>+-----------------------------------------------------------------------+
Received on Fri Jul 23 1993 - 06:27:10 CEST
Date: 23 Jul 93 04:27:10 GMT
Message-ID: <1993Jul23.042710.2193_at_pony.Ingres.COM>
the correct behavior by default and has done so for years.
In plain English, INGRES transactions are serializables and
respect all the properties that a transaction should have.
Rgds,
Bernard Antonuk
bantonuk_at_ingres.com
In article <levine_charles-210793204202_at_130.252.169.32>, levine_charles_at_tandem.com (Charles Levine) writes...
>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
>($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_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. |
>+-----------------------------------------------------------------------+
Received on Fri Jul 23 1993 - 06:27:10 CEST