Query the locked process information of MySQL in Linux.

Query the locked process information of MySQL in Linux.
1. Problem Description

When using the database, tables may be locked, causing timeout when other processes access the same table. Specifically, use the following command in MySQL:

SHOW PROCESSLIST

The result is

The Status column indicates the query Status of the statement. If the value is Locked, the query is Locked by other queries. Host indicates the Host information that sends the query statement. The locked query is performed on the TCP connection issued by port 38292 of Host 192.168.1.152. The information of the locked statement is provided in the Info column.

Based on this information, how can we find the information about the process where the locked SQL statement is located to help DEBUG?

2. Solution a. view the process PID based on the TCP port number. Method 1: lsof

Lsof-Pnl + M-i4 | grep 38292

Method 2: netstat

Netstat-anp | grep 38292

Run the preceding statement on the host 192.168.1.152, as shown in. The process PID corresponding to port 38292 obtained by the two methods is 11882.
B. view the process information based on the PID and run the following command on host 192.168.1.152 to view the process information.

Pss aux | grep 11882

As shown in, you can view basic information such as the commands started by the process.
Based on the above information, you can troubleshoot the MySQL query lock table and provide the required information for further debugging.