Description of two cases of DB2 deadlock and lock wait (1)

The following articles describe the two situations of DB2 deadlock and lock wait. A lock is a database that generally introduces an operation mechanism to control the integrity of concurrent data, locks are not terrible in concurrent applications. locks are usually divided into two types: 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. For example, in the network environment, the complex application environment.

In an environment with low real-time requirements, you can increase the lock wait time. In some cases, you must decrease the lock wait time. The lock wait is different from the DB2 deadlock. the deadlock is caused by 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 DB2 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, the value of 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.