How to Find Hidden MySQL Locks When They Do Not Appear in SHOW PROCESSLIST

Problem : Sometimes a SQL statement is locked, yet it cannot be found with SHOW PROCESSLIST . The article shows how to investigate such cases.

Prerequisite

performance_schema = on;

Experiment Steps

1. Create a test table and insert three rows.

mysql> use test1;
Database changed
mysql> create table action1(id int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into action1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)

mysql> select * from action1;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.00 sec)

2. Start a transaction and delete one row without committing.

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from action1 where id = 3;
Query OK, 1 row affected (0.00 sec)

3. In a separate session, try to update the same row; the statement becomes blocked.

mysql> update action1 set id = 7 where id = 3;

4. SHOW PROCESSLIST only shows the update statement and a sleeping thread, not the delete that holds the lock.

mysql> show processlist;
| 22188 | root | localhost | test1 | Sleep | 483 | NULL | NULL |
| 22218 | root | localhost | NULL   | Query |   0 | starting | show processlist |
| 22226 | root | localhost | test1 | Query |   3 | updating | update action1 set id = 7 where id = 3 |

5. Query the InnoDB lock tables to see the actual lock information.

mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id| requested_lock_id| blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 5978292           | 5978292:542:3:2   | 5976374        | 5976374:542:3:2   |
+-------------------+-------------------+-----------------+------------------+

mysql> select * from INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
+-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+
| 5978292:542:3:2| 5978292     | X         | RECORD    | `test1`.`action1` | GEN_CLUST_INDEX | 542        | 3         | 2        | 0x00000029D504 |
| 5976374:542:3 2| 5976374     | X         | RECORD    | `test1`.`action1` | GEN_CLUST_INDEX | 542        | 3         | 2        | 0x00000029D504 |
+-----------------+-------------+-----------+-----------+-------------------+-----------------+------------+-----------+----------+----------------+

mysql> select trx_id,trx_started,trx_requested_lock_id,trx_query,trx_mysql_thread_id from INNODB_TRX;
+---------+---------------------+-----------------------+----------------------------------------+---------------------+
| trx_id  | trx_started         | trx_requested_lock_id | trx_query                              | trx_mysql_thread_id |
+---------+---------------------+-----------------------+----------------------------------------+---------------------+
| 5978292 | 2020-07-26 22:55:33 | 5978292:542:3:2       | update action1 set id = 7 where id = 3| 22226                |
| 5976374 | 2020-07-26 22:47:33 | NULL                  | NULL                                   | 22188                |
+---------+---------------------+-----------------------+----------------------------------------+---------------------+

6. The row with processlist_id = 22188 is the sleeping thread that actually holds the lock, but it does not appear as an active query.

7. Use performance_schema.threads to map the processlist_id to the internal THREAD_ID .

mysql> select * from performance_schema.threads where processlist_ID = 22188\G
*************************** 1. row ***************************
THREAD_ID: 22225
PROCESSLIST_ID: 22188
... (other columns omitted)

8. With the THREAD_ID , query events_statements_current to retrieve the exact SQL text that is holding the lock.

mysql> select * from events_statements_current where THREAD_ID = 22225\G
*************************** 1. row ***************************
THREAD_ID: 22225
EVENT_NAME: statement/sql/delete
SQL_TEXT: delete from action1 where id = 3
... (other columns omitted)

9. The result shows that a DELETE statement is blocking the subsequent UPDATE . In production, you can now decide whether to kill the blocking transaction after confirming with the application owner.

By following these steps—enabling performance_schema , inspecting InnoDB lock tables, locating the thread via performance_schema.threads , and extracting the SQL from events_statements_current —you can reliably identify hidden MySQL locks that are invisible to SHOW PROCESSLIST .