事务持续执行之谜：怎样找出对行记录上锁的 SQL？

1. 故障背景

在数据库运行过程中，部分事务语句无法正常提交，相应的会话状态会在很长时间内保持活跃。然而，当我们使用 show processlist
 命令进行检查时，往往难以获取到导致事务无法提交的异常会话 SQL 语句，这给故障排查和处理带来了极大的困难。

2. 故障复现

2.1 模拟两个会话操作

以下是两个会话的操作示例，用于模拟事务锁等待的情况：

-- 会话 1
mysql> begin;
mysql> delete from  db02.order_info whereidin(12,13);

-- 会话 2
mysql> begin;
mysql> update db02.order_info set create_time='2025-02-10 10:00:00' whereid=12;
-- 执行完处于夯住状态，超过 innodb_lock_wait_timeout 参数设定值，会超时回滚。
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 设置会话级别锁等待超时参数，便于测试
mysql> set session innodb_lock_wait_timeout=3600;
mysql> update db02.order_info set create_time='2025-02-10 10:00:00' whereid=12;

2.2 检索 show processlist

使用以下 SQL 语句查询正在执行的 SQL 语句：

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)

从查询结果中，我们并未找到导致 UPDATE 操作等待的事务语句。

3. 排查思路

3.1 查看未提交的事务

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   -- 刚刚运行的第二个语句事务 ID
        trx_state: LOCKWAIT-- 处于锁等待状态
      trx_started: 2025-04-0214:20:34
trx_tables_locked: 1   -- 锁了 1 张表
  trx_rows_locked: 1   -- 锁了 1 行
*************************** 2.row ***************************
           trx_id: 3600069-- 刚刚运行的第一个语句事务 ID
        trx_state: RUNNING -- 获得锁的状态
      trx_started: 2025-04-0214:18:18
trx_tables_locked: 1
  trx_rows_locked: 2
2 rows in set (0.00 sec)

3.2 查看等待锁的事务信息

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  -- 等待锁开始的时间
                locked_table: `db02`.`order_info`  -- 被锁定的表名（格式为数据库名.表名）
              waiting_trx_id: 3600172 -- 正在等待锁的事务 ID
             blocking_trx_id: 3600069 -- 持有锁从而阻塞其他事务的事务 ID
sql_kill_blocking_connection: KILL 376283 -- 用于终止持有锁的连接的 SQL 语句
1 row in set (0.01 sec)

3.3 查询持有锁连接对应的 SQL 语句

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 limit1                |
|    376455 | begin                                           |
|    376455 | deletefrom  db02.order_info whereidin(12,13) |
+-----------+-------------------------------------------------+
3 rows in set (0.00 sec)

通过以上查询，我们找到了 delete from db02.order_info where id in(12,13)
 语句。在与业务侧确认该语句是否合理后，如果没问题可以使用 KILL
 命令终止相应的连接。

4. 解决方案

杀掉锁源 SQL 对应的连接线程。

KILL 376283

5. 总结

5.1 锁相关表

在排查数据库事务锁等待问题时，主要涉及以下几个关键系统表：

5.2 排查 SQL

考虑到以上排查步骤较为繁琐，在生产故障紧急情况下，我们可以使用以下 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;

执行该 SQL 语句后，得到如下结果：

+-----------+---------------------------------------------------------------------------+----------------+------------------------+
| THREAD_ID | SQL_TEXT                                                                  | PROCESSLIST_ID | transaction_start_time |
+-----------+---------------------------------------------------------------------------+----------------+------------------------+
|    376457 | select @@version_comment limit 1                                          |         376285 | 2025-04-02 14:20:34    |
|    376457 | begin                                                                     |         376285 | 2025-04-02 14:20:34    |
|    376457 | update db02.order_info set create_time='2025-02-10 10:00:00'  where id=12 |         376285 | 2025-04-02 14:20:34    |
|    376457 | set session innodb_lock_wait_timeout=3600                                 |         376285 | 2025-04-02 14:20:34    |
|    376455 | select @@version_comment limit 1                                          |         376283 | 2025-04-02 14:18:18    |
|    376455 | begin                                                                     |         376283 | 2025-04-02 14:18:18    |
|    376455 | delete from  db02.order_info where id in(12,13)                           |         376283 | 2025-04-02 14:18:18    |
+-----------+---------------------------------------------------------------------------+----------------+------------------------+
7 rows in set (0.00 sec)

结果集中各字段含义如下：

• THREAD_ID
  ：MySQL 数据库内线程 ID。
• SQL_TEXT
  ：当前线程正在执行的 SQL 语句的文本内容。
• PROCESSLIST_ID
  ：数据库会话 ID，主要用于客户端连接的线程管理，例如可以使用 KILL
   命令结合 PROCESSLIST_ID
   终止某个客户端连接。
• transaction_start_time
  ：事务开始时间。

5.3 相关参数

针对 InnoDB 存储引擎，可以通过调整 innodb_lock_wait_timeout
 参数来处理锁等待超时报错问题，从而提升数据库并发性能。以下是 lock_wait_timeout
 和 innodb_lock_wait_timeout
 两个参数的详细对比：