Lock Promotion Parameter Setting - Determining Which Tables Have Lock Promotion/Escalation

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" wrote in message news:44a7f6bd$1@forums-1-dub...
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:
> Richard Kinread 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.
>
>
> 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