Suggest a new parameter for the TABLESPACE definition (perhaps named "LOCKMAXCANCEL").
This would allow you to specific a number that represent the maximum number of locks that can be acquired for the tablespace at which point the thread would be cancelled (i.e. resource unavailable).
Why is it useful?
|Who would benefit from this IDEA?|
How should it work?
A new sub-parameter of LOCKMAX, coded as follows:
LOCKMAX nnnn CANCELTHREAD
When a non-zero value is supplied for LOCKMAX, you can optionally code "CANCELTHREAD" which would mean that instead of lock escalation occurring, the thread is cancelled.
There have been times where a call results in too many locks being taken on a heavily referenced table and the thread continues for too long thereby locking other threads out. This is usually caused by poor application design/coding, and sometimes just due to the (state of) the data at the time. For those infrequent, but costly, occasions where this occurs, this capability would help prevent this from happening.
Current Work Around:
None at this time.
Considered customizing a third-party monitoring tool to identify the situation and cancel the thread. This would result in additional overhead and would probably be a bear to maintain from a DBA/admin perspective.
|Customer Name||Morgan Stanley|
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "email@example.com" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions