Full record of DB2 deadlock resolution process

DB2 is mainly used in large-scale application systems. It has good scalability and can support everything from mainframe to single-user environments. DB2 provides high-level data utilization, integrity, security, and recoverability. This article mainly introduces the full record of the DB2 deadlock resolution process. The cause of the deadlock in this article is the select statement. The processing process is quite difficult. Friends in need can refer to

in the production environment. The database used is DB2. However, a strange deadlock phenomenon has appeared frequently recently: a certain select sql statement always deadlocks.

According to past experience, deadlock problems usually occur in update sql statements such as update/delete. Moreover, this select sql statement is a very ordinary sql, without any processing of large amounts of data.

Analyzing this deadlock, there are many things that are difficult to deal with.

1. Due to the large amount of data in the production environment, we cannot import the data of the association table in the production environment into the test environment. That is, the amount of data cannot be simulated.
2. There is no log output. Because the log output level of the production environment is ERROR.
3. It cannot be tested in the production environment because the customer does not allow it.
4. The database in the production environment cannot enable snapshots and other functions. Because it will affect performance.

As you can imagine, without features such as snapshots, deadlock analysis can only rely on code analysis. But this process is very complicated, and there is no clue just by analyzing the code.

Stage 1: We suspect it is due to the amount of data

Due to the extremely large amount of data in the production environment, this process also involves the processing of many other tables. So we wonder whether the large amount of data caused the system to be overloaded, leading to deadlock?
So we obtained the load information of the CPU, hard disk, network, etc. when the deadlock occurred. No clues were found.

Phase 2: Make a test program and use multi-threading to simulate multiple users in the test environment to do this processing.

In order to reproduce this deadlock in the development environment, we made a multi-threaded test program to simulate multi-user operation. Unfortunately, it still has not been reproduced.

Phase 3: Analyze the difference between the test environment database and the product environment database

At this time we suspect that the problem is still caused by the amount of data. So we tried our best to have as much data in the development environment as in the production environment.
After running the test, it still did not reproduce.

Stage 4: Analyze the user's operation log

With no other solution, we have to analyze the user's operation log, hoping to find some clues. Hard work pays off, and we found that when two people perform this operation at the same time, a deadlock will basically occur. Therefore, we judge that the problem is caused by two people operating at the same time. However, why does the development environment simulate the operations of many people but no deadlock occurs?


Phase 5: Discovering database setting problems

We modified the test program to increase the number of simulated users, but unfortunately, the problem still did not reproduce. At this time we noticed: Are the database settings of the development environment different from the database settings of the production environment? We compared the settings of the two databases and found that many parameters were different. But we only focused on the settings related to the lock, that is, the settings containing the LOCK keyword.


Stage 6: Keep the settings of the test environment database and the production environment database consistent

We have changed all lock-related settings to be consistent with the production environment. But still the deadlock is not reproduced. Finally, a person discovered that the "cur_commit" setting was
different. So I queried the documentation and discovered the characteristics of cur_commit. When cur_commit = false, the following situations will cause a deadlock:
Thread 1 inserts data A, and then thread 2 inserts data B.
Before thread 2 has submitted the transaction, thread 1 queries data A, which will cause a deadlock.
In the development environment, cur_commit = true, so we have never been able to simulate this phenomenon.
So, we changed cur_commit to false.


Phase 7: Use the test program to simulate

We modified the test program to simulate the operations of the above two threads and successfully reproduced the deadlock. The error log information is also consistent with the production environment.

Stage 8: Use screen operations to simulate

Then we modified the program, used screen operations, and successfully reproduced the deadlock.
Solution:

The solution is very simple, that is, add the conditions in the query statement as indexes, so that deadlock will not occur.
Since the data volume of this table is not large, there is almost no impact on performance.

Related recommendations:

Detailed explanation of the python module ibm_db method for offline installation of db2

Connecting DB2 database with Python

Five ways to use PHP to operate DB2 Express C (1)_PHP tutorial

The above is the detailed content of Full record of DB2 deadlock resolution process. For more information, please follow other related articles on the PHP Chinese website!