Lock and deadlock event monitoring
Diagnosing and correcting lock contention situations in large database environments can be complex and time-consuming. The locking event monitor designed to simplify this task by collecting locking data.
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 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.
- SQL statements, such as:
- 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.
- 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.
- 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 database workloads by setting the appropriate
database configuration parameter:
- This parameter controls the generation of lock wait events
Best practice is to enable lock wait data collection at the workload level.
- 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.
- This parameter controls the generation of deadlock events
Best practice is to enable deadlock data collection at the database level.
- 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
- 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
- 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.
SET EVENT MONITOR DB2DETAILDEADLOCK state 0 DROP EVENT MONITOR DB2DETAILDEADLOCK