DB2 database lock problems

Introduction

A lock is a mechanism introduced by a database to control the integrity of concurrent data. In concurrent applications, locks are not terrible. locks are generally divided into deadlock and lock wait.

The deadlock is caused by two concurrent processes or threads occupying one resource at the same time and occupying the other's resources, this is usually because the program is not considered weekly in terms of concurrency.

Lock wait is the most common condition in the database. A lock is required for each application to prevent other processes or applications from damaging data, other processes or applications have to wait for the previous application to release the lock during this period. The lock wait time parameter is adjustable, but depends on the actual application, such as in a network environment, a complex application environment, or an environment with low real-time requirements, you can increase the lock wait time in some cases. The lock wait is different from the deadlock. the deadlock is due to improper program concurrency. You need to adjust the program concurrency mechanism. The lock wait is a performance problem and may need to adjust the SQL statement of the program.

Whether it is a deadlock or lock wait, the database has adjustable parameters and corresponding tools for capturing and analyzing. The following are the common methods for Lock processing.

View and modify lock-related key database Parameters
View Current concurrent applications
View and change snapshot Parameters
Get Snapshot
Use Event Viewer

1. View and change the lock-related main configuration parameters.
CLP mode:
Db2 get db cfg
Find the DLCHKTIME and LOCKTIMEOUT parameters in the parameter list.
-The unit of DLCHKTIME is millisecond, which is the interval between the DB2 deadlock check. If this value is 10000 ms, it means to check whether there is a deadlock in the current database every 10 seconds. If there is a deadlock, one of the transactions will be rolled back to let another transaction complete the transaction.
-The unit of LOCKTIMEOUT is second, which is the maximum lock wait time. If the lock is not obtained after this time, an error is returned.
Setting prompt:
-By default, LOCKTIMEOUT is-1, which means that the lock wait time is indefinite, which is not consistent with the actual application requirements. You need to set the value to a value greater than 0.
-The DLCHKTIME is usually set to a lower time than the LOCKTIMEOUT time. Otherwise, an error is returned when no deadlock is found.

Change the CLP mode)
Db2 update db cfg using locktimeout 10

2. view the current Concurrent Application
CLP mode:
Db2 list applications
Or db2 list applications show detail
Or db2 list applications for database dbname [show detail]

This command can check whether multiple applications are currently connected to the database, so as to check whether concurrency exists.
Note that in the Application Name and Application Id columns, the Application Name column lists the Application names. db2bp usually means that the current CLP is connected to the database, java means that db2cc or your own java applications may be connected to the database. In the application Id column, you can see which machines these applications come from, the LOCAL host is displayed as the LOCAL + User Name + start connection time, and the remote host is displayed as the hexadecimal IP address + User Name + start connection time. Eliminate unnecessary locks during testing by troubleshooting concurrent applications.

3. View and change snapshot Parameters
If the DLCHKTIME and LOCKTIMEOUT parameters are properly set, you can view the snapshot or create an event monitor to analyze the cause. To use snapshots, you must first enable the snapshot switch.
Db2 get monitor switches
The output will contain the following parameters:
Monitoring switch Database Manager parameter comment
BUFFERPOOL DFT_MON_BUFPOOL buffer read/write status and occurrence time
LOCK DFT_MON_LOCK hold, LOCK wait, and deadlock
SORT DFT_MON_SORT Heap usage and sorting performance
STATEMENT DFT_MON_STMT STATEMENT start time, STATEMENT content
TABLE DFT_MON_TABLE Measure of activity (rows read/written)
UOW DFT_MON_UOW Start/end times, completion status
TIMESTAMP DFT_MON_TIMESTAMP Timestamps

To observe the locks and execution statements in snapshots, you can set the LOCK and STATEMENT options to ON, or enable other switches as appropriate. For example: db2 update monitor switches using lock on statement on

4. View snapshot Information
-View snapshot information at the database manager level
Db2 get snapshot for dbm
-View database-level snapshot Information
Db2 get snapshot for database on dbname
-View application-level snapshot Information
Db2 get snapshot for application agentid appl-handler
Note: appl-handler can be obtained from the output of list applicaitions.
-View table-level snapshot Information
Db2 get snapshot for tables on dbname
Note: The tables snapshot switch must be set to ON.
-View lock snapshot Information
Db2 get snapshot for locks on dbname
Or
Db2 get snapshot for locks on for application agentid appl-handler
-View snapshot information of dynamic SQL statements
Db2 get snapshot for dynamic SQL on dbname

5. Use Event Viewer
You can use the time viewer to collect lock events and SQL statement events to analyze the cause of the lock.

Event Type
To use the event monitor, you must first select the event type of interest. DB2 has many event types. The following three types can be used for lock analysis:

DEADLOCKS
DEADLOCKS WITH DETAILS
STATEMENTS

Steps:
-Create an event monitor
Create event monitor evmname for eventtype write to file 'directory'
Example: create event monitor mymonitor for deadlocks, statements
Write to file 'C: \ temp'

-Enable the event monitor.
Example:
Set event monitor mymonitor state 1
Note: 1 is enabled, 0 is disabled
The event monitor starts to work. When all applications are disconnected, the event is recorded.
-View event details
Db2evmon-path 'C: \ temp'

Note reference Description: For details about the event monitor and snapshot output, see the redbook related to the DB2 Information Center and IBM website.
Recommended books: system monitor guide and reference