Lock and deadlock event monitoring

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

Note: The deadlocks event monitor has been deprecated, and the function it provided is included in the locking event monitor. In addition, the DB2DETAILDEADLOCK event monitor is also deprecated. See Deprecated lock monitoring functionality for important usage information about this event monitor.

The locking 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 requester (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 can be written in binary format to an unformatted event table in the database, in which case the captured data must be processed in a post-capture step. Alternatively, the lock event information can be written to a set of regular tables. See Output options for event monitors for more information about how to choose the most appropriate output format.

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 are as follows:
  • 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, if you are using UE tables as the output format, the name of an unformatted event table into which the lock event data is written.
    Note: If you choose to use regular tables for event monitor output, default table names are assigned. You can override the defaults from the CREATE EVENT MONITOR statement, if you prefer.
  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

Capturing of SQL statement history and input values uses additional processor time, memory and storage, but this level of detail is often needed to successfully debug a locking problem.

After a locking event has occurred, you can view the event data in the output produced by the event monitor. If you used UE tables, 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.

If you used regular tables as the output format, you can query the data directly using SQL.

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 requester 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
Note:
  • In DB2 pureScale® environments, when the cluster caching facility (CF) is busy, a lock holder just released a lock, or due to other reasons, you might see the message "Unable to obtain Lock Holder information during the occurrence of the lock event", with no information about the lock holder.
  • In DB2 pureScale environments, the deadlock event monitor does not always report the lock_mode monitor element. You might see deadlock event monitor output with no information about the lock holder. This can happen even when the CF is not busy.

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. If you use the locking event monitor to detect deadlocks, consider disabling the DB2DETAILDEADLOCK event monitor. If the DB2DETAILDEADLOCK event monitor remains active while the locking event monitor also collects deadlock information, both event monitors will be collecting data, which can significantly affect performance.

To remove the DB2DETAILDEADLOCK event monitor, issue the following SQL statements: 
SET EVENT MONITOR DB2DETAILDEADLOCK state 0
DROP EVENT MONITOR DB2DETAILDEADLOCK