Using SHOW FULL PROCESSLIST to Diagnose and Resolve MySQL Table Locks

During a data‑synchronization test the network dropped, leaving a MySQL table inaccessible after reconnection. The table size was 112 192 KB, and attempts to drop, truncate, or delete it all failed, prompting a deeper investigation.

The key tool for troubleshooting is the SHOW FULL PROCESSLIST command, which provides a real‑time snapshot of all MySQL connections, their states, execution times, and the SQL they are running. This information helps identify long‑running or stuck queries that may need to be killed.

SHOW FULL PROCESSLIST returns a live snapshot of MySQL connections, making it useful for handling sudden incidents.

There are three common ways to view the process list:

1. Directly in the MySQL command line (append \G to print each column on a separate line).

mysql> show full processlist;
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| Id     | User | Host                 | db    | Command | Time | State    | Info                  |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
| 449000 | root | 127.123.213.11:59828 | stark | Sleep   | 1270 |          | NULL                  |
| ...    | ...  | ...                  | ...   | ...     | ...  | ...      | ...                   |
+--------+------+----------------------+-------+---------+------+----------+-----------------------+
11 rows in set (0.01 sec)
mysql> show full processlist\G;
*************************** 1. row ***************************
Id: 449000
User: root
Host: 127.123.213.11:59828
Db: stark
Command: Sleep
Time: 1283
State:
Info: NULL
... (additional rows) ...

2. Querying the INFORMATION_SCHEMA.PROCESSLIST table.

SELECT id, db, USER, HOST, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

3. Using Navicat’s "Tools → Server Monitor" feature, which provides a sortable UI.

The columns returned have the following meanings:

Id : Unique identifier of the MySQL thread; can be killed.

User : User that opened the connection.

Host : IP and port of the client.

db : Database the thread is using (NULL if none).

Command : Current command (Sleep, Query, Connect, etc.).

Time : Seconds the thread has been in its current state.

State : Detailed execution state (e.g., copying to tmp table, Sorting result, Sending data).

Info : The SQL statement being executed (NULL if none).

Once a problematic thread is identified, you can terminate it:

kill 449000;

Or kill all threads that have been running for more than three minutes:

-- Generate kill statements for threads running longer than 3 minutes
SELECT CONCAT('kill ', id, ';')
FROM information_schema.processlist
WHERE command != 'Sleep'
  AND time > 3*60
ORDER BY time DESC;

If the issue involves a metadata lock (e.g., during ALTER TABLE), you can inspect uncommitted InnoDB transactions:

SELECT trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx\G

The fields indicate transaction state, start time, associated MySQL thread ID (used for killing), and the SQL query inside the transaction. Killing those threads usually releases the metadata lock.

Another remedy is to adjust the lock wait timeout, which defaults to one year (31536000 seconds). Setting it to a shorter period, such as 30 minutes, makes the lock fail faster:

SET SESSION lock_wait_timeout = 1800;

SET GLOBAL lock_wait_timeout = 1800;

After applying these steps, the table can be repaired or altered without being blocked by a metadata lock.

All done.