Locking In Oracle 8 /Oracle Rdb

[Fry, Mark]

Hi Neil,

The default concurrency transaction model for RDB uses 2 transactions - the
QUERY transaction for finds, and the UPDATE transaction for lookups and
updates.  The default concurrency transaction model for ORACLE 8 (and 7)
uses one transaction - UPDATE, which is used for everything.  ORACLE does
not need a separate QUERY transaction because it supports a feature known as
"Hold Cursor", which allows the internal pointer within a read chain to be
maintained after a commit.

In terms of locking, the QUERY transaction for RDB is a Repeatable Read
transaction (no locking - it uses the snapshot file) and the UPDATE
transaction is a Serializable transaction (it uses protected read locks for
reading and protected write locks for writing).  It also applies locks on
the index pages.  In many cases the index page locks block other users from
updating the table until they are released.  For this reason, the Optimistic
transaction model is often used with RDB [this does all reads *and* lookups
using the QUERY transaction to minimize index page locks].

For ORACLE 8, the UPDATE transaction is a Read Committed transaction.  It
uses a basic SELECT operation for its reads, which applies no locks.  For
writes, each row to be written is reselected with a "SELECT FOR UPDATE",
then the SQL UPDATE of the row follows.  The update results in an RX lock
(Row Exclusive Table Lock), which blocks other updates to the row, but
allows other people to read from and write to other rows in the table.

Is it just a casual enquiry, or are you planning to migrate from RDB to
ORACLE?  If so, it might be worth your while spending some time getting to
know both databases beforehand, particularly with respect to the way they
each handle locks.  This knowledge will definitely make the transition
easier.

Best regards,

Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com

 -----Original Message-----
From: 	Chamberlain, Neil [mailto:Neil.Chamberlain@bpb.com] 
Sent:	06 March 2000 15:24
To:	'POWERHOUSE Listserv'
Subject:	Locking In Oracle 8 /Oracle Rdb

I am looking for information on the differences (in terms of
locking/transaction models) between Oracle Rdb on Alpha and Oracle 8 on HP
UX.

Any information would be greatly appreciated.

Thanks
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.