Very Computer

1. SYBASE page locks outperform ORACLE row locks

Our goal is to have a Many-Reader-One-Writer (MROW) locking model which is the
minimum requirement to have serializable and consistent transactions in general.
In this model many readers or one writer (but not both) are allowed to
concurrently access an object.

This locking behaviour is important in write transactions where the update is
based on decisions made on read data. If read data is changed by other
transactions during my transaction, my decision is not valid anymore and
something wrong is written to the database.

For me the MROW-Locking model is the most natural locking model. It guarantees
correct transactions in general, i.e. if the application programmer does
not care
on locks, he/she still writes correct transactions (concerning the locking) with
a quit good concurrency performance (for example no table locks).

However, with ORACLE this locking behaviour is very hard to implement. In
default
locking mode, ORACLE (and SYBASE) transactions are in general not correct in a
multiuser environment because read data is NOT locked. However, it is
possible to
manually lock read rows but with ORACLE this is very expensive in terms of
concurrency performance: either the whole table has to be locked in share mode
(preventing any updates by other transactions on the whole table) or the row has
to be locked exclusively (preventing other transactions from reading that row
with a "read" lock, i.e. an exclusive lock in this case).

With SYBASE on the other hand, we only have page level locks. But SYBASE
supports
read (share) and write (exclusive) locks on pages which by far outperforms
ORACLE
row level locks in the described case, where actually a read lock on row level
would be required.

What I really cannot understand is why ORACLE does not support read locks on row
level. If they already have exclusive locks on row level, read locks cannot be
that difficult to implement.
Are there other reasons for this?
Are row level read locks planned for a future release of ORACLE?

Is there another way to implement the desired MROW locking model?

Regards,

PS: The whole discussion above only works if all queries and updates only use
unique key columns in their where clauses. If that is not the case, the whole
table has to be locked anyway to prevent other transactions from inserting new
columns or updating existing columns which could then satisfy the query
condition.

2. NC, VA, OH, PA, GA, SC, MD-ORACLE APPLICATIONS CONSULTANT

3. Record Locking - Is there a way to test to see if a record has been locked

4. Informix Certified Professional Job Opportunities

5. Page lock to simulate row level lock

6. how do I put a & in a varchar2?

7. page lock or table lock

8. ODBC access to DECIMAL field

9. Paradox 5.0: Row level locking or page locking?

10. Row-locking instead of page-locking ?

11. Row locking versus page locking

12. help with record locking.. (Pessimistic locking)

13. How to lock and detect the locked record?