How to Identify SQL Statements Causing Row-Level Locks in MySQL Transactions

1. Issue Background

During database operations, some transaction statements may fail to commit, leaving their sessions active for an extended period. However, when using the SHOW PROCESSLIST command, it is often challenging to identify the SQL statements causing transaction failures, complicating troubleshooting and resolution.

2. Reproducing the Issue

2.1 Simulating Two Sessions

Below is an example of operations in two sessions to simulate transaction lock waits:

-- Session 1
mysql> BEGIN;
mysql> DELETE FROM db02.order_info WHERE id IN (12, 13);

-- Session 2
mysql> BEGIN;
mysql> UPDATE db02.order_info SET create_time = '2025-02-10 10:00:00' WHERE id = 12;
-- Execution hangs due to lock wait timeout, causing a rollback after exceeding the `innodb_lock_wait_timeout` parameter.
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- Set session-level lock wait timeout for testing
mysql> SET SESSION innodb_lock_wait_timeout = 3600;
mysql> UPDATE db02.order_info SET create_time = '2025-02-10 10:00:00' WHERE id = 12;

‍

2.2 Querying SHOW PROCESSLIST

Use the following SQL to query currently executing SQL statements:

mysql> SELECT * FROM information_schema.processlist WHERE COMMAND <> 'Sleep';
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------------+
| ID     | USER            | HOST                | DB   | COMMAND          | TIME    | STATE                                                         | INFO                                                                      |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------------+
|     57 | repl            | 10.186.63.118:36624 | NULL | Binlog Dump GTID | 2862216 | Master has sent all binlog to slave; waiting for more updates | NULL                                                                      |
|      5 | event_scheduler | localhost           | NULL | Daemon           | 3011932 | Waiting on empty queue                                        | NULL                                                                      |
| 376285 | root            | localhost           | NULL | Query            |      67 | updating                                                      | UPDATE db02.order_info SET create_time = '2025-02-10 10:00:00' WHERE id = 12 |
| 376271 | root            | localhost           | NULL | Query            |       0 | executing                                                     | SELECT * FROM information_schema.processlist WHERE COMMAND <> 'Sleep'     |
+--------+-----------------+---------------------+------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------------------------+
4 rows in set (0.01 sec)

‍

The query results do not reveal the SQL statement causing the UPDATE operation to wait.

3. Troubleshooting Approach

3.1 Checking Uncommitted Transactions

mysql> SELECT trx_id, trx_state, trx_started, trx_tables_locked, trx_rows_locked FROM information_schema.innodb_trx\G;
*************************** 1. row ***************************
           trx_id: 3600172   -- Transaction ID of the second statement
        trx_state: LOCK WAIT -- Transaction is in lock wait state
      trx_started: 2025-04-02 14:20:34
trx_tables_locked: 1   -- Number of tables locked
  trx_rows_locked: 1   -- Number of rows locked
*************************** 2. row ***************************
           trx_id: 3600069 -- Transaction ID of the first statement
        trx_state: RUNNING -- Transaction is running and holds the lock
      trx_started: 2025-04-02 14:18:18
trx_tables_locked: 1
  trx_rows_locked: 2
2 rows in set (0.00 sec)

‍

3.2 Checking Lock-Waiting Transaction Information

mysql> SELECT wait_started, locked_table, waiting_trx_id, blocking_trx_id, sql_kill_blocking_connection FROM sys.innodb_lock_waits\G;
*************************** 1. row ***************************
                wait_started: 2025-04-02 14:20:34  -- Time when lock wait started
                locked_table: `db02`.`order_info`  -- Locked table name (format: database_name.table_name)
              waiting_trx_id: 3600172 -- Transaction ID waiting for the lock
             blocking_trx_id: 3600069 -- Transaction ID holding the lock and blocking others
sql_kill_blocking_connection: KILL 376283 -- SQL to terminate the blocking connection
1 row in set (0.01 sec)

‍

3.3 Querying the SQL Statement of the Blocking Connection

mysql> SELECT a.thread_id, a.sql_text FROM performance_schema.events_statements_history a WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads b WHERE b.PROCESSLIST_ID = 376283);
+-----------+-------------------------------------------------+
| thread_id | sql_text                                        |
+-----------+-------------------------------------------------+
|    376455 | SELECT @@version_comment LIMIT 1                |
|    376455 | BEGIN                                           |
|    376455 | DELETE FROM db02.order_info WHERE id IN (12, 13) |
+-----------+-------------------------------------------------+
3 rows in set (0.00 sec)

‍

From the query, we identified the statement DELETE FROM db02.order_info WHERE id IN (12, 13). After confirming with the business team that the statement is valid, you can terminate the connection using the KILL command.

4. Solution

Terminate the connection thread causing the lock:

KILL 376283

5. Summary

5.1 Key Tables for Lock-Related Issues

When troubleshooting transaction lock waits in MySQL, the following system tables are critical:

‍

5.2 Quick Troubleshooting SQL

To simplify the troubleshooting process in urgent production scenarios, use the following SQL:

SELECT 
    a.THREAD_ID,
    a.SQL_TEXT,
    b.PROCESSLIST_ID,
    DATE_FORMAT(c.trx_started, '%Y-%m-%d %H:%i:%s') AS transaction_start_time
FROM
    performance_schema.events_statements_history a
JOIN
    performance_schema.threads b ON a.THREAD_ID = b.THREAD_ID
JOIN
    information_schema.innodb_trx c ON b.PROCESSLIST_ID = c.trx_mysql_thread_id;

‍

The result includes:

THREAD_ID: MySQL thread ID.

SQL_TEXT: SQL statement being executed by the thread.

PROCESSLIST_ID: Session ID for thread management (e.g., terminating connections with KILL).

transaction_start_time: Transaction start time.

5.3 Related Parameters

For InnoDB, adjust the innodb_lock_wait_timeout parameter to handle lock wait timeouts and improve concurrency:

‍

‍