ORACLE record locking acting like page locking

ORACLE record locking acting like page locking

Post by Jim Smit » Sun, 16 Feb 1997 04:00:00

Quote:>ORACLE record level locking?  When I try to update an EXPIRATIONDATE
>field in approximately 23,0000 records with SQL*Plus, my DLL, which uses
>the Oracle Objects for OLE (OO402 Class Library) has to wait a long time
>(normally less than 3 seconds becomes about 1.5 minutes) to create a

>The dynaset is just a simple SELECT statement (no update) with flags

>It's as if Oracle is doing page or block level locking. From what I've
>read, I know this is not the case, so I wonder what's going on.

>I am running Oracle Workgroup Server on Windows NT.  We have a
>simple database for our Website that just verifies a
>username/password/access rights/expiration dates.

>My SQL*PLUS statments are:

>Since the SQL commit is immediate I would expect the performance of my
>DLL dynaset creation to change very little.  Instead it can take a user
>1.5 minutes to log in!

>Your thoughts and comments would be greatly appreciated.

What evidence do you have that this is a locking problem? It sounds to
me as the server is busy.

Have you looked at either System stats (CPU usage, IO rates) or database
stats while this is going on?

How long does the update statement take? How many records are there in
the table altogether and is the expirationdate indexed? If the update is
taking a long time, then you should look into tuning it.

I don't understand your comment about the immediate commit. AUTOCOMMIT
executes a commit after each statement and should have no effect on
performance of either the current session or anybody elses.
Jim Smith


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
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
read (share) and write (exclusive) locks on pages which by far outperforms
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?


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


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?