DB2 deadlock and lock wait: Two Basic Situations: Correct parsing (1)

The following articles describe the two basic cases of comprehensive analysis of DB2 deadlock and lock wait. If you are interested in the two basic situations of DB2 deadlock and lock wait, you can click to view the following articles. Locks are introduced by databases 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, 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 DB2 deadlock or lock wait, the database has the corresponding Parameter Adjustable, there are corresponding tools to capture and analyze, the following is the general method of lock processing.

· View and modify lock-related key database Parameters

· View current concurrent applications

· View and change snapshot Parameters

· Getting snapshots

· 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, if a DB2 deadlock is not found, an error is returned because the lock wait times out.

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.