Application concurrency health indicators

db.deadlock_rate - Deadlock Rate health indicator

Deadlock rate tracks the rate at which deadlocks are occurring in the database and the degree to which applications are experiencing contention problems.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2® pureScale® environments. For more information, see Health monitor has been deprecated.

Identifier: db.deadlock_rate
Health monitor level: Database
Category: Application Concurrency
Type: Upper-bounded threshold-based
Unit: Deadlocks per hour

Deadlocks may be caused by the following situations:

Lock escalations are occurring for the database
An application may be locking tables explicitly when system-generated row locks may be sufficient
An application may be using an inappropriate isolation level when binding
Catalog tables are locked for repeatable read
Applications are getting the same locks in different orders, resulting in deadlock.

The indicator is calculated using the formula:

(db.deadlocks_t - db.deadlocks_t-1)

where t is the current snapshot and t-1 is the last snapshot, taken 60 minutes before the current snapshot.

The higher the rate of deadlocks, the greater the degree of contention which may generate an alert.

db.locklist_util - Lock List Utilization health indicator

This indicator tracks the amount of lock list memory that is being used.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2 pureScale environments. For more information, see Health monitor has been deprecated.

Identifier: db.locklist_util
Health monitor level: Database
Category: Application Concurrency
Type: Upper-bounded threshold-based
Unit: Percentage

There is one lock list per database and it contains the locks held by all applications concurrently connected to the database. There is a set limit on lock list memory. Once the limit is reached, performance degrades because of the following situations:

Lock escalation converts row locks to table locks, thereby reducing concurrency on shared objects in the database.
More deadlocks between applications can occur since applications are waiting for a limited number of table locks. As a result, transactions are rolled back.

An error is returned to the application when the maximum number of lock requests has reached the limit set for the database.

The indicator is calculated using the formula:

(db.lock_list_in_use / (locklist * 4096)) * 100

Utilization is measured as a percentage of memory consumed, where a high percentage represents an unhealthy condition.

Consider using the self-tuning memory feature to have lock memory resources automatically allocated as required by the current workload. If you have the self-tuning memory feature enabled for the lock memory area, you should configure this health indicator to disable threshold checking.

db.lock_escal_rate - Lock Escalation Rate health indicator

This indicator tracks the rate at which locks have been escalated from row locks to a table lock thereby impacting transaction concurrency.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2 pureScale environments. For more information, see Health monitor has been deprecated.

Identifier: db.lock_escal_rate
Health monitor level: Database
Category: Application Concurrency
Type: Upper-bounded threshold-based
Unit: Lock escalations per hour

A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application, or the lock list space consumed by all applications is approaching the total lock list space. The amount of lock list space available is determined by the maxlocks and locklist database configuration parameters.

When an application reaches the maximum number of locks allowed and there are no more locks to escalate, the application uses the space in the lock list allocated for other applications. There is one lock list per database and it contains the locks held by all applications concurrently connected to the database. When the entire lock list is full, an error occurs.

The indicator is calculated using the formula:

(db.lock_escals_t - db.lock_escals_t-1)

where 't' is the current snapshot and 't-1' is the last snapshot, taken 60 minutes before the current snapshot.

The higher the rate of deadlocks, the greater the degree of contention which may generate an alert.

Consider using the self-tuning memory feature to have lock memory resources automatically allocated as required by the current workload. If you have the self-tuning memory feature enabled for the lock memory area, you should configure this health indicator to disable threshold checking.

db.apps_waiting_locks - Percentage of Applications Waiting on Locks health indicator

This indicator measures the percentage of all currently executing applications that are waiting on locks.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2 pureScale environments. For more information, see Health monitor has been deprecated.

Identifier: db.apps_waiting_locks
Health monitor level: Database
Category: Application Concurrency
Type: Upper-bounded threshold-based
Unit: Percentage

A high percentage can indicate that applications are experiencing concurrency problems which can negatively affect performance.

The indicator is calculated using the formula:

(db.locks_waiting / db.appls_cur_cons) *100)