Db2 Locks And Deadlocks Test

This test, executed by an internal agent, tracks various statistics pertaining to the locks and deadlocks in a DB2 database. The details of the test are provided below:

Target of the test : A DB2 database server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every database being monitored

Configurable parameters for the test
  1. TEST PERIOD – How often should the test be executed
  2. Host – The IP address of the DB2 server
  3. Port – The port number through which the DB2 server communicates. The default port is 50000.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Deadlocks:

Indicates the total number of deadlocks that have been detected.

 

Number

A high value is indicative of contention problems. These problems could be caused by the following reasons:

  • 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.

To resolve the problem, first determine the applications (or application processes) in which the deadlocks are occurring. Then, modify the application to enable it to execute concurrently. Some applications, however, may not be capable of running concurrently.

Exclusive lockescalations:

Indicates the number of times that locks have been escalated from several rowlocks to one exclusive table lock, or the number of times an exclusive lock on a row caused the table lock to become an exclusive lock. Other applications cannot access data held by an exclusive lock; therefore it is important to track exclusive locks since they can impact the concurrency of your data.

Number

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. The amount of lock list space available is determined by the LOCKLIST and MAXLOCKS configuration parameters.

A high value of this measure indicates that an application may be using exclusive locks when share locks are sufficient.

 

Locks held:

Indicates the total number of locks that have been currently held by all applications in/using the database.

Number
  • This measure provides summary information about locking. A high value indicates that one or more of the applications needs to be tuned to improve performance.
  • You can also compare the value of this measure with the results of the following formula to determine the number of additional locks that may be requested. This comparison can help you determine if the configuration parameters need adjusting or your applications need tuning.

(LOCKLIST * 4096 / 36) - locks held = # remaining where:

LOCKLIST is the configuration parameter 4096 is the number of bytes in one 4K page 36 is the number of bytes required for each lock.

Lock timeouts:

When a unit of work exceeds the maximum allowable amount of time, a lock timeout occurs and the unit of work isn’t granted the lock it has been waiting for. This measure indicates the total number of lock timeouts during a specific interval.

Number

If the number of lock timeouts becomes excessive when compared to normal operating levels, an application may be holding locks for long durations. This requires an adjustment in the LOCKTIMEOUT configuration parameter. Committing can also free locks.

If the LOCKTIMEOUT database configuration parameter is set too high, it may result in too few lock timeouts. In such a case, your applications may wait excessively to obtain a lock.

Lock escalations:

Denotes the total number of times that locks have been escalated from several row locks to a table lock.

Number

A high value signifies a problem.

There are several possible causes for excessive lock escalations:

  • The lock list size (LOCKLIST) may be too small for the number of concurrent applications
  • The percent of the lock list usable by each application (MAXLOCKS) may be too small
  • One or more applications may be using an excessive number of locks.

To resolve these problems,

  • Increase the LOCKLIST configuration parameter value.
  • Increase the MAXLOCKS configuration parameter value.

Percent of applications in lock wait:

Indicates the percentage of applications waiting for the release of lock.

Percent

A high value indicates that the applications are experiencing concurrency problems. Hence, the applications that are holding locks or exclusive locks for long periods of time have to be identified.