Example explains how to monitor the occurrence of deadlocks in DB2 UDB

This article illustrates how to monitor the occurrence of deadlock in DB2 UDB through concrete examples. There are two types of monitors in the DB2 UDB: The snapshot Monitor and the event monitor. A snapshot, as its name suggests, is a slice of the database's continuous state, and through the snapshot monitor, you can easily view the currently connected application, the currently waiting lock, the current deadlock, and the SQL statement being executed, while you can view the buffer, table, and table space usage. If you save historical data and can make comparisons, it is helpful to analyze the concurrency performance of your database.

But we can not guess when the deadlock, so if there is a background program can always monitor the activities of the database, record all the deadlock events, this is very important for the database administrator. The DB2 UDB provides event monitors. By not missing out on all the database events (only the deadlock events in this article), the event Monitor provides a way to analyze historical data (the focus of this article) and to speculate about future trends. DB2 UDB also provides DB2 performance Expert (DB2/PE) or similar programs for generating analysis reports, but this is beyond the scope of this article.

Common terminology

Locks are database software mechanisms that control the concurrency of applications, and locks are used to prevent the following:

1. Lost previous update

2. Non-repeatable reads

3. Access to uncommitted data

The mode of the lock includes shared and exclusive locks, and shared locks allow other programs to read resources that are already occupied by other shared locks, so are also called read locks, which means that other applications cannot access the same resource until the resource is freed, so also called write locks. In addition, the DB2 UDB also provides different lock levels, and different applications may require access to different ranges of data, and lock levels facilitate the full utilization of system resources and improve system performance. If an application requests a lock that is used by another application and cannot be shared, the DB2 UDB suspends the previous application. Lock escalation is when the Locklist (Locklist represents the memory space that the lock can occupy) or when an application has a lock greater than maxlocks*locklist (MAXLOCKS represents the percentage of space that the application owns), DB2 UDB Attempts to merge several row-level locks into a table-level lock to free up lock space. Although the lock escalation itself does not take much time, locking the entire table typically reduces concurrency performance significantly.

When the application is in a suspended state for a specified amount of time, the DB2 UDB automatically aborts the application and sends a descriptive error message to the SQLCA. A deadlock occurs when two or more applications hold locks on the resources required by another application, without which those applications cannot continue to perform their work.

After the Dlchktime timeout, the DB2 udb aborts an application (usually the least-done one) where the deadlock occurs, releasing all the locks held by the application and promising other applications to continue working, DB2 UDB A descriptive error message is sent to the SQLCA of the terminated application. LOCKTIMEOUT Specifies the time that an application is allowed to wait for a lock, which avoids the global deadlock and causes the entire application to crash. If the value of Locktimeout is-1, the application waits until the lock is released or a deadlock occurs.

Event Monitor

The event monitor is used to collect information about the application that is associated when a database event occurs. The events here refer to, connect, deadlock, declare and transaction. You can define the monitor for the type of event you want to monitor. For example, a deadlock monitor is used to monitor the occurrence of deadlocks.

There are two deadlock-related event types in the DB2 UDB:

Deadlocks

Log simple application information.

Deadlocks with DETAILS

Record all the complex information, including the application, execution statement declarations, and deadlock details. But using this event monitor can degrade the performance of the system because it requires a lot of extra information.