MySQL中怎么找到对行记录上锁的SQL？

bisal的个人杂货铺 2025-04-22

127

点击标题下「蓝色微信名」可快速关注

数据库中的并发操作很可能产生锁等待的情况，重要的是怎么能找到"根源"，技术社群的这篇文章《事务持续执行之谜：怎样找出对行记录上锁的 SQL？》很实际地给我们讲解了怎么找到导致锁等待的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                | D    | 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 锁相关表

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

表名	作用
`information_schema.processlist`	查看当前数据库中正在执行的 SQL 语句和会话状态信息
`information_schema.innodb_trx`	存储 InnoDB 存储引擎中未提交事务的详细信息，如事务 ID、事务状态、事务开始时间、锁定的表数量和行数量等
`sys.innodb_lock_waits`	记录等待锁的事务信息，包括等待开始时间、被锁定的表名、等待锁的事务 ID、持有锁的事务 ID 以及用于终止持有锁连接的 SQL 语句
`performance_schema.events_statements_current`	记录当前正在执行的 SQL 语句的相关信息
`performance_schema.events_statements_history`	存储线程执行的 SQL 语句历史记录
`performance_schema.threads`	包含线程的相关信息，可用于关联 `PROCESSLIST_ID` 和 `THREAD_ID`

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
两个参数的详细对比：

对比项	lock_wait_timeout	innodb_lock_wait_timeout
适用范围	对所有存储引擎都适用，主要用于表级锁等待	仅针对 InnoDB 存储引擎内部锁等待
默认值	31536000 秒（即 1 年）	通常为 50 秒
作用机制	非 InnoDB 存储引擎操作请求锁被占用时进入等待，超设定值操作终止并报错	InnoDB 事务获取锁被占用时进入等待，超设定值事务自动回滚、释放部分锁资源并报错