db2 9.7 Monitoring database locking

Diagnosing and correcting lock contention situations in large DB2® environments can be complex and time consuming. The lock event monitor and other facilities are designed to simplify this task by collecting locking data.

Introduction

The lock event monitor is used to capture descriptive information about lock events at the time that they occur. The information captured identifies the key applications involved in the lock contention that resulted in the lock event. Information is captured for both the lock requestor (the application that received the deadlock or lock timeout error, or waited for a lock for more than the specified amount of time) and the current lock owner.

The information collected by the lock event monitor is written in binary format to an unformatted event table in the database. The captured data is processed in a post-capture step improving the efficiency of the capture process.

You can also directly access DB2 relational monitoring interfaces (table functions) to collect lock event information by using either dynamic or static SQL.

Determining if a deadlock or lock timeout has occurred is also simplified. Messages are written to the administration notification log when either of these events occurs; this supplements the SQL0911N (sqlcode -911) error returned to the application. In addition, a notification of lock escalations is also written to the administration notification log; this information can be useful in adjusting the size of the lock table and the amount of the table an application can use. There are also counters for lock timeouts (lock_timeouts), lock waits (lock_waits), and deadlocks (deadlocks) that can be checked.

The types of activities for which locking data can be captured include the following:

• SQL statements, such as:
  • DML
  • DDL
  • CALL
• LOAD command
• REORG command
• BACKUP DATABASE command
• Utility requests

The lock event monitor replaces the deprecated deadlock event monitors (CREATE EVENT MONITOR FOR DEADLOCKS statement and DB2DETAILDEADLOCK) and the deprecated lock timeout reporting feature (DB2_CAPTURE_LOCKTIMEOUT registry variable) with a simplified and consistent interface for gathering locking event data, and adds the ability to capture data on lock waits.

Functional overview

Two steps are required to enable the capturing of lock event data using the locking event monitor:

1. You must create a LOCK EVENT monitor using the CREATE EVENT MONITOR FOR LOCKING statement. You provide a name for the monitor and the name of an unformatted event table into which the lock event data will be written.
2. You must specify the level for which you want lock event data captured by using one of the following methods:
   • You can specify particular workloads by either altering an existing workload, or by creating a new workload using the CREATE or ALTER WORKLOAD statements. At the workload level you must specify the type of lock event data you want captured (deadlock, lock timeout or lock wait), and whether you want the SQL statement history and input values for the applications involved in the locking. For lock waits you must also specify the amount of time that an application will wait for a lock, after which data is captured for the lock wait.
   • You can collect data at the database level and affect all DB2 workloads by setting the appropriate database configuration parameter:

     mon_lockwait

     This parameter controls the generation of lock wait events

     Best practice is to enable lock wait data collection at the workload level.

     mon_locktimeout

     This parameter controls the generation of lock timeout events

     Best practice is to enable lock timeout data collection at the database level if they are unexpected by the application. Otherwise enable at workload level.

     mon_deadlock

     This parameter controls the generation of deadlock events

     Best practice is to enable deadlock data collection at the database level.

     mon_lw_thresh

     This parameter controls the amount of time spent in lock wait before an event for mon_lockwait is generated

The capturing of SQL statement history and input values incurs additional overhead, but this level of detail is often needed to successfully debug a locking problem.

After a locking event has occurred, the binary data in the unformatted event table can be transformed into an XML or a text document using a supplied Java-based application called db2evmonfmt. In addition, you can format the binary event data in the unformatted event table BLOB column into either an XML report document, using the EVMON_FORMAT_UE_TO_XML table function, or into a relational table, using the EVMON_FORMAT_UE_TO_TABLES procedure.

To aid in the determination of what workloads should be monitored for locking events, the administration notification log can be reviewed. Each time a deadlock or lock timeout is encountered, a message is written to the log. These messages identify the workload in which the lock requestor and lock owner or owners are running, and the type of locking event. There are also counters at the workload level for lock timeouts (lock_timeouts), lock waits (lock_waits), and deadlocks (deadlocks) that can be checked.

Information collected for a locking event

Some of the information for lock events collected by the lock event monitor include the following:

• The lock that resulted in an event
• The application holding the lock that resulted in the lock event
• The applications that were waiting for or requesting the lock that result in the lock event
• What the applications were doing during the lock event

Limitations

• There is no automatic purging of the lock event data written to the unformatted event table. You must periodically purge data from the table.
• You can output the collected event monitor data to only the unformatted event table. Outputs to file, pipe, and table are not supported.
• It is suggested that you create only one locking event monitor per database. Each additional event monitor only creates a copy of the same data.

Deprecated lock monitoring functionality

The deprecated detailed deadlock event monitor, DB2DETAILDEADLOCK, is created by default for each database and starts when the database is activated. The DB2DETAILDEADLOCK event monitor must be disabled and removed, otherwise both the deprecated and new event monitors will be collecting data and will significantly affect performance.

To remove the DB2DETAILDEADLOCK event monitor, issue the following SQL statements:

SET EVENT MONITOR DB2DETAILDEADLOCK state 0
DROP EVENT MONITOR DB2DETAILDEADLOCK