We have recently changed some tables from All Pages to Data Rows and it has
been suggested that we look at the lock promotion parameters. There is some
promotion from shared row to shared table according to sp_sysmon. How does
one identify the tables where the lock promotion is occurring?

Thanks

Richard Kinread

Richard Kinread wrote:
You'd have to scan syslocks. I'd do a select from it based on the
dbid and the lock type. Probably the best way to do this would be
to write a query that loops.

-am © MMVI

I suppose you could sample the monLocks MDA table to see when row/page/table
level locks are occuring. If a table is a datarows locked table, then as you
start to see page/table level locks, those are escalations.

For Datapages and Allpages locked tables the occurence of table level locks
indicate escalations as well.

So as others have said, it's a matter of sampling your catalog tables, MDA, ASE
Monitor, sp_object_stats, information periodically.


"Richard Kinread" <rkinread1 (AT) sympatico (DOT) ca> wrote

I gathered that he just wants to know what tables have ever had lock
escalations done. I'm not sure he wants to know specifically when it
happened, or if it just plained happened period, so that he can adjust the
HWM, and LWM settings appropriately. Not sure.

"Jesus M. Salvo Jr." <noone (AT) noone (DOT) org> wrote

It would be nice to know when the escaltions were happening as well as which
tables are afected. I'd like to fix the SQL if that is the problem.

Thanks for the comments and tips.

"Sherlock, Kevin" <ksherlock (AT) saionline (DOT) com> wrote

Richard Kinread wrote:
Its not so much any problem with the T-SQL usually, but the settings
for lock promotion. Check your current settings and see if they
require revision (obviously they do if you are getting lock promotion
and you think its a problem).

-am © MMVI

Lock promotion is a good and necessary thing. Certain tasks which would
otherwise attempt to lock every row/page in a table, or at least a great
many of them, should instead get a table lock. Setting the promotion
parameters correctly enables this to happen only at a time when too many
rows/pages would be locked by an operation, which is unweildy for Sybase
to track, troublesome for DBAs to monitor, and potentially uses all
available locks. Promoting more quickly causes blocking by locking the
whole table but can actually reduce deadlock situations by forcing
database operations on the same table to occur serially instead of in
parallel.

SQL which triggers lock promotion should not automatically be considered
wrong or even inefficient. It may be, it is almost certainly a "heavy"
operation since it is hitting a lot of rows/pages, but heavy operations
have to be performed sometimes.

--ian

A.M. wrote:

I agree, and this would probably be something to suggest as an enhancement to
the MDA tables. As mentioned, sometimes it's a matter of "fixing" the
escalation settings for the table. Some options that may also affect
escalations include "concurrency_opt_threshold", and "optimistic_index_lock"
attributes for a table.

"Richard Kinread" <rkinread1 (AT) sympatico (DOT) ca> wrote

We have had problems with lock promotion caused by a missing index or with
the isolation level set higher than necessary. If these causes for lock
promotion can be ruled out, we will consider adjusting the promotion levels.

Richard Kinread
"Iann" <iann (AT) nospam (DOT) com> wrote

Lock promotion is a good and necessary thing. Certain tasks which would
otherwise attempt to lock every row/page in a table, or at least a great
many of them, should instead get a table lock. Setting the promotion
parameters correctly enables this to happen only at a time when too many
rows/pages would be locked by an operation, which is unweildy for Sybase
to track, troublesome for DBAs to monitor, and potentially uses all
available locks. Promoting more quickly causes blocking by locking the
whole table but can actually reduce deadlock situations by forcing
database operations on the same table to occur serially instead of in
parallel.

SQL which triggers lock promotion should not automatically be considered
wrong or even inefficient. It may be, it is almost certainly a "heavy"
operation since it is hitting a lot of rows/pages, but heavy operations
have to be performed sometimes.

--ian

A.M. wrote: