Sybase 11.9.2 Locking

[Kapal Mo]

Be forewarned not a Sybase DBA.

In our Sybase 11.9.2 on AIX we have a small table about 10-20 rows, row
length less than 500. This table is heavily updated.

The first problem I encountered was that of page locking. Since multiple
jobs trying to update different rows on the table, Sybase would lock the
whole page blocking the other update jobs. Tried to resolve this by alter
table lock datarows and also changing 1 row per page. This has fixed the
problem somewhat but the problem still occurs.

I am thinking that Sybase will still do a table lock since it thinks table
lock is more efficient than a row lock considering the number of rows
involved.

Basic question then is how can I ensure that Sybase will strictly do a row
lock. Is there a setting in 11.9.2 or is this not an issue with future
Sybase versions.

Thank you.

[hra]

How many rows are being modified per update? One, or more?
You need to find out for sure whether it really is taking a table lock
and can do this by beginning a transaction, doing one of the updates,
then executing sp_lock without committing the transaction.
If only one row is being updated, there are other possible reasons why
more than one row could be locked e.g. poor index choice (use showplan
to check)
Another thought is to switch on deadlock tracing, since you may be
seeing deadlocks as well as livelocks and this will give more
information as to why e.g. the contention could be on index pages not
data pages.
Also sp_object_stats on the affected table should give a clue as to
what is being waited for.

"Kapal Mo" <kap...@nospam.hotmail.com> wrote in message news:<68Zha.37729$cB3.2...@nnrp1.uunet.ca>...