Re: distributed concurrency in oracle 7.0

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, Informix, DB2 (all versions), and SQLBase support cursor stability and repeatable read. Ingres supports repeatable read. Sybase supports repeatable read (no phantom inserts or updates) using the HOLDLOCK parameter but this is not often used because it can quickly lead to deadlocks. Sybase's default mode releases locks immediately after the record is read from the database and set to the client application.

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