SYBASE page locks outperform ORACLE row locks

SYBASE page locks outperform ORACLE row locks

Post by Andreas Rohr » Fri, 01 Dec 1995 04:00:00



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.

 
 
 

SYBASE page locks outperform ORACLE row locks

Post by Jonathan Lewi » Fri, 01 Dec 1995 04:00:00




: 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?

An associated problem with the same solution is the one of foreign
key constraints.

If a foreign key is instantiated as an index on a child table, Oracle
will use the uncommitted entries in the index to check the validity
of attempts to delete parent rows.

If a foreign key is not instantiated through an index on a child
table, then Oracle takes out a share lock on the parent table
when you insert child rows,so that no-one else can take out the
exclusive lock needed to delete the parent of the rows you are
inserting.

This means that

a)  You could have lots of undesirable indexes on large tables
or
b)  From time to time you try to change a parent row, and end
    up either waiting for all child changes to commit, or you
    force all child-changes to wait for your change to commit.

The underlying problem is that Oracle needs to ensure that a
parent row is not deleted whilst an uncommitted child row
for that parent exists.  A possible solution to the problem
is to allow the child insert to take out a form of shared-row-lock
on the parent row.

So ... maybe your wish will be granted in a future release.

--
Jonathan Lewis

 
 
 

SYBASE page locks outperform ORACLE row locks

Post by Jim Kenne » Sat, 02 Dec 1995 04:00:00



>Path: nntp.teleport.com!psgrain!newsfeed.internetmci.com!in2.uu.net!usc!math.ohio-state.edu!jussieu.fr!univ-lyon1.fr!in2p3.fr!swidir.switch.ch!scsing.switch.ch!ns.access.ch!gatezh1-07.access.ch!user

>Newsgroups: comp.databases.oracle,comp.databases.sybase
>Subject: SYBASE page locks outperform ORACLE row locks
>Date: Thu, 30 Nov 1995 11:08:08 +0100
>Organization: Ecofin Research & Consulting, Switzerland
>Lines: 50

>NNTP-Posting-Host: gatezh1-07.access.ch
>Xref: nntp.teleport.com comp.databases.oracle:53546 comp.databases.sybase:27004

I think Oracle has (natively) what you want.  However, I think you will get a
more accurate response if you include a simple example of how you are
expecting things to work.  

In your first pharagraph it sounds like if I am updating row X in
table Y and you come along and want to read row X in table Y that you should
not be able to.  Do you mean that you would have to wait until I committed my
update for you to be able to read row X in table Y at all?  I can understand
you might want to be able to see row X in table Y as it existed before I
made my update (since I have not decided to keep it or not yet.), but would
you really want to not be able to access that row at all?  

Quote:>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.

Quote:>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).

I do not think this is true.  If I want to possibly effect a set of rows in
Oracle and guarrentee that no other writer is going to do something with them
in my transaction I can do a select for update. Depending upon how large my
result set is I can lock 0 rows up to the whole table.  That seems like a fine
enough level of granularity.  Yes, other people cannot write to those
particular rows during my transaction, but they can write to other rows.

Quote:>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?

Do you mean you want to lock rows so no one else can read them?  What would be
the default behavior when you try to read locked rows?  Error or you wait
forever or some timeout?  Also you might lock yourself.(deadly embrace)

If it is your application that is using the database you can impliment this
yourself.  Quite often 2 users want to edit the same info in a small time
frame.  Let one get in and grab a token  and the other cannot get that token
for that set of data so they are notified that someone else is working with
that.  If you store info about the user you can even tell the user who failed
who has the token.  Also with db alerts you can notify the second user when
the first is done.  Thus, the second could go do something else - if they want
to - and be notified when the first user has finished their work.  This works
quite well because the first user's work may span multiple transactions and
this way the first user says "I'm done." when they have completed all their
transactions not just one.

The token mechanism does not have to do any table locks or anything
restrictive.

I think you do need to include a few simple examples of what you need to do
(and why) so people can give you better assistance.

Jim Kennedy

- Show quoted text -

>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.

 
 
 

SYBASE page locks outperform ORACLE row locks

Post by Alexandr I. Alesinsk » Mon, 01 Jan 1996 04:00:00


Hi Andreas,

I'm sorry, but I don't understand your problem. Doesn't Oracle's SELECT ...
FOR UPDATE satisfy your needs ?

Best wishes,

Alexander Alesinsky

 
 
 

1. Page lock to simulate row level lock

 You can't - unless you mean setting IRL for a table - but this only works on
the last page for inserts.

 > Hey, does any one remember how to set SQL 6.5 page level lock to achieve a

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq

2. SQL Query Results

3. Row locking versus page locking

4. Retrieving topmost Child ID in Hierarchical Query ?

5. Row-locking instead of page-locking ?

6. ..Free Cash Grants...

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

8. dynamic range lookup (XREF?)

9. ORACLE record locking acting like page locking

10. page lock or table lock

11. Row lock automagically being escalated to Table Locks

12. table-level lock or row-level lock

13. row level locking and dead-lock prevention