Full record of DB2 deadlock resolution process

DB2 is primarily used in large-scale applications, with better scalability and support from mainframe to single-user environments. DB2 provides high-level data utilization, integrity, security, recoverability. This article mainly introduces the DB2 deadlock resolution process full record, this article caused the deadlock is the SELECT statement, the processing process is very difficult, the need for friends can refer to the next

The database used in the production environment is DB2. But a strange deadlock has occurred recently: a select SQL statement will always deadlock.

In the past experience, it is common to have deadlock problems with update SQL statements such as Update/delete. And this select SQL statement is a very common SQL, without any large amount of data processing.

Analysis of this deadlock, there are many difficult to deal with the place.

1, because the production environment data is large, we can not import the data of related tables in the production environment into the test environment. In other words, the amount of data cannot be simulated.
2, no log output. Because the log output level of the production environment is error.
3, can not be tested in the production environment, because the customer is not allowed.
4, the production environment database can not open snapshots and other functions. Because it affects performance.

As you can imagine, in the absence of snapshots and other functions, the analysis of deadlocks can only rely on the analysis of code. But this process is very complex, and the analysis code alone has no clue.

Phase 1: The reason we suspect the amount of data

Due to the extremely large volume of data in the production environment, there are many other tables to handle. So we wonder if the big data volume is causing the system to load too much, causing a deadlock?
So we made the load information such as CPU, hard disk, network and so on when the deadlock occurred. No clues were found.

Phase 2: Do a test program, in the test environment with multi-threaded simulation multiuser to do this processing.

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

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

At this point we suspect that the data volume is causing the problem. As far as possible, we make the development environment as much data as the product environment.
After the test is run, it is still not reproduced.

Phase 4: Analyze user's action log

Without any way, we had to analyze the user's operation log, hoping to find a clue. Kung Fu is not a conscientious, we found that when two people at the same time
When you do this, the basic deadlock will occur. So, we judge whether the problem is caused by the simultaneous operation of two people. However, why the development environment simulates the
Many people's operations, but there is no deadlock?

Phase 5: Issues with discovering Database settings

We have also modified the test program to increase the number of simulated users, but unfortunately, it still does not reproduce the problem. At this point we notice: is not the development environment
Database settings differ from the database settings for the product environment? We compared the settings of two databases: We found a lot of different parameters. But we're only concerned with locks.
, which is the setting that contains the lock keyword.

Phase 6: Align the settings of the test environment database and the production environment database

We have changed all the settings related to lock and the product environment has been. But the deadlock is still not reproduced. Finally, a person found that "cur_commit" this setting
Different. Then query the document, found the characteristics of cur_commit.
When cur_commit = False, the following conditions cause a deadlock:
Thread 1 Inserts data A, and thread 2 inserts data B.
Thread 2 does not commit a thing before it is committed, threads 1 queries data A, causing a deadlock.
In the development environment, Cur_commit = True, so we have not been simulating this phenomenon.
So, we also changed the Cur_commit to False.

Phase 7: Using a test program to simulate

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

Phase 8: Use the screen operation to simulate

Then we modified the program, using the screen to operate, but also successfully reproduced the deadlock.

Solution:

The solution is simply to add the criteria in the query statement to the index, so there is no deadlock.
Because of the small amount of data in this table, performance has little effect.

Related recommendations:

Detailed ibm_db method for offline installation of DB2 Python module

Python Connection DB2 Database

Five ways to operate DB2 Express C using PHP (1) _php tutorial