本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
事务与并发（ACID / 隔离级别 / 锁 / MVCC） · 并发问题排查

线上发现某个 SQL 一直 hang 住，如何排查？(SHOW PROCESSLIST、innodb_locks、innodb_lock_waits)

A SQL query is stuck in production, how to troubleshoot? (SHOW PROCESSLIST, innodb_locks

答案语言

考察要点

这道题面试官想看你是否具备线上数据库问题排查的系统性思维和实战能力。核心考察点包括：

  1. 诊断流程：能否清晰、有序地阐述从发现问题到定位根因的完整排查步骤。
  2. 工具熟练度：是否熟练掌握 MySQL 内置的诊断工具，如 SHOW PROCESSLIST、information_schema 下的事务与锁相关表，以及更现代的 performance_schema。
  3. 锁理论知识：是否深刻理解 InnoDB 的锁机制，能准确区分元数据锁（MDL）和行锁（Record Lock），并能分析锁等待（Lock Wait）的成因。

核心概念

排查 hang 住的 SQL，本质上是诊断一个线程为何长时间处于非空闲状态。最常见的原因是等待锁，但也可能是其他资源瓶颈。核心是找到阻塞者（Blocker）和被阻塞者（Waiter）。

1. SHOW [FULL] PROCESSLIST

这是最基础、最快捷的诊断入口，用于查看当前 MySQL Server 正在执行的线程。

  • 关键列说明:
    • Id: 连接的唯一标识，用于 KILL 操作。
    • User/Host: 连接来源。
    • db: 当前连接的数据库。
    • Command: 线程当前状态，Sleep 表示空闲，Query 表示正在执行查询。
    • Time: 线程处于当前 Command 状态的秒数。Time 值很大通常是首要排查目标。
    • State: 线程状态的进一步描述。
      • Locked: 注意，这通常表示线程在等待 MyISAM 的表锁或 MySQL 层级的元数据锁（MDL Lock），而不是 InnoDB 的行锁。例如，一个会话在对表 t 执行 DDL 时，另一个会话对 t 的查询就会显示 State: Locked。
      • sending data: 可能在传输大量数据，或在执行一个扫描大量行的查询。
      • Creating sort index: 正在进行排序操作。
    • Info: 正在执行的 SQL 语句。使用 SHOW FULL PROCESSLIST 可以看到完整的 SQL。

2. information_schema (MySQL 5.7 及以上常用)

information_schema 提供了更结构化、更详细的事务和锁信息，是定位 InnoDB 锁问题的关键。

  • INNODB_TRX: 存储当前所有活动事务（包括正在执行和锁等待的）的信息。

    • trx_id: 事务唯一 ID。
    • trx_state: 事务状态，关键值是 RUNNING 和 LOCK WAIT。
    • trx_started: 事务开始时间。
    • trx_mysql_thread_id: 对应 SHOW PROCESSLIST 中的 Id，是关联两者的桥梁。

  • INNODB_LOCKS: 存储当前被持有的锁。

    • lock_trx_id:持有该锁的事务 ID。
    • lock_mode: 锁的模式（S, X, IS, IX）。
    • lock_type: 锁的类型（RECORD, GAP）。
    • lock_table: 被锁的表。

  • INNODB_LOCK_WAITS: 这是定位锁等待关系最直接的表。

    • requesting_trx_id: 正在请求锁（被阻塞）的事务 ID。
    • blocking_trx_id: 持有锁（阻塞者）的事务 ID。

关联关系图示:

mermaid
 1graph TD
 2    A[SHOW PROCESSLIST] -- Id --> B(INNODB_TRX.trx_mysql_thread_id);
 3    B -- trx_id --> C{INNODB_LOCK_WAITS};
 4    C -- requesting_trx_id --> D[被阻塞的事务];
 5    C -- blocking_trx_id --> E[阻塞的事务];
 6    E -- trx_id --> F(INNODB_TRX);
 7    F -- trx_mysql_thread_id --> G[阻塞的SQL (在PROCESSLIST中)];

3. performance_schema (MySQL 8.0+)

performance_schema 是更现代、性能开销更低、信息更丰富的诊断工具，正在逐步取代 information_schema 的部分功能。

  • performance_schema.data_locks: 类似 INNODB_LOCKS，显示持有的锁。
  • performance_schema.data_lock_waits: 类似 INNODB_LOCK_WAITS，显示锁等待关系，且信息更全，例如包含锁的索引名和具体锁定的数据（主键值）。

排查步骤（实战方法论）

当线上发现 SQL hang 住时，应遵循以下结构化步骤进行排查：

步骤 1：快速定位问题线程

执行 SHOW FULL PROCESSLIST;，重点关注 Time 字段值异常大的、且 Command 不为 Sleep 的线程。记录下它的 Id 和 Info (SQL 语句)。

sql
 1-- 示例：发现 Id=123 的线程执行一个 UPDATE 已经 300 秒
 2SHOW FULL PROCESSLIST;
IdUserHostdbCommandTimeStateInfo
123app10.0.1.10mydbQuery300UPDATE users SET age=30 WHERE id=1;
456app10.0.1.11mydbSleep500NULL

步骤 2：分析锁等待关系

如果 State 列为 Locked，大概率是 MDL 锁，检查是否有针对该表的 DDL 或其他表级锁操作。如果 State 列为空或其它值，大概率是 InnoDB 行锁等待。此时，使用 information_schema 或 performance_schema 来确认。

方法 A：使用 information_schema (通用)

  1. 通过 hang 住线程的 Id (123) 找到其事务 ID。

    sql
     1SELECT trx_id, trx_state, trx_started FROM information_schema.INNODB_TRX
     2WHERE trx_mysql_thread_id = 123;

    假设查到 trx_id 为 ABCDE，trx_state 为 LOCK WAIT。

  2. 使用该事务 ID (ABCDE) 在 INNODB_LOCK_WAITS 中查找阻塞它的事务。

    sql
     1SELECT * FROM information_schema.INNODB_LOCK_WAITS
     2WHERE requesting_trx_id = 'ABCDE';

    假设查到 blocking_trx_id 为 FGHIJ。

  3. 根据阻塞事务 ID (FGHIJ)，反查其线程信息和 SQL。

    sql
     1-- 找到阻塞线程的 Id
     2SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX
     3WHERE trx_id = 'FGHIJ';
     4-- 假设查到 trx_mysql_thread_id 为 789
     5

     6-- 在 PROCESSLIST 中查看该线程在做什么
     7-- SHOW PROCESSLIST; (找到 Id=789 的行)

    此时可能会发现，线程 789 正在执行一个大的查询，或者更常见的是，它处于 Sleep 状态，但有一个未提交的事务。

方法 B：使用 performance_schema (MySQL 8.0+)

MySQL 8.0 提供了更简洁的视图 sys.innodb_lock_waits，它已经将上述 JOIN 操作封装好了。

sql
 1-- 一条 SQL 直接找出阻塞链
 2SELECT
 3    waiting_pid,
 4    waiting_query,
 5    blocking_pid,
 6    blocking_query,
 7    wait_age,
 8    sql_kill_blocking_query
 9FROM sys.innodb_lock_waits;

步骤 3：分析并解决阻塞源

通过步骤 2，我们已经定位到阻塞的源头（Blocker）。

  • Case 1: Blocker 是一个长时间运行的 SQL

    • 分析：EXPLAIN 该 SQL，检查其执行计划是否合理，是否存在未使用索引导致的全表扫描。
    • 解决：
      • 短期：如果业务影响巨大，在确认无风险后，可 KILL <blocking_pid>;。注意：KILL 会导致该事务回滚，回滚本身也可能耗时。
      • 长期：优化该 SQL（加索引、改写逻辑）或调整业务逻辑。

  • Case 2: Blocker 处于 Sleep 状态

    • 分析：这是最常见的情况。应用代码在 BEGIN 事务后，执行了 DML 操作（INSERT/UPDATE/DELETE），但没有 COMMIT 或 ROLLBACK，然后连接被归还到连接池。这个持有锁的连接就成了“僵尸”连接。
    • 解决：
      • 短期：KILL <blocking_pid>; 立即释放锁。
      • 长期：必须修复应用代码。排查代码中所有事务块，确保它们都有明确的 COMMIT 或 ROLLBACK，并用 try-catch-finally 保证在异常时也能正确关闭事务和连接。

步骤 4：根因复盘与预防

  • 代码层面：确保事务尽可能短小，遵循“快进快出”原则。避免在事务中包含 RPC 调用或等待用户输入等耗时操作。
  • 数据库层面：为查询涉及的列建立合适的索引，特别是 WHERE、JOIN、ORDER BY 的列。
  • 监控层面：配置监控告警，对长事务（如超过 5 秒）、活跃线程数、锁等待数量等设置阈值。可以使用 Percona Toolkit 的 pt-deadlock-logger 和 pt-kill 等工具进行自动化监控和处理。

常见低分回答

  1. 只会 SHOW PROCESSLIST：只说 SHOW PROCESSLIST，看到 Time 长就 KILL，没有进一步探究是 MDL 锁还是行锁，也没有使用 information_schema 去定位阻塞源。这表明排查思路单一，深度不够。
  2. 混淆 State: Locked 和 InnoDB 行锁：错误地认为 State: Locked 就是 InnoDB 行锁等待，这暴露了对 MySQL 锁体系基础知识的欠缺。
  3. 滥用 KILL：将 KILL 作为首选方案，而不是分析和解决根因的最后手段。没有评估 KILL 之后事务回滚的成本和风险。
  4. 未考虑 Sleep 状态的事务：只关注 Command: Query 的线程，忽略了 Command: Sleep 的线程也可能因为持有未提交的事务而成为阻塞源。这是线上最常见的锁问题场景之一。
  5. 不知道 information_schema 或 performance_schema：对于 InnoDB 锁问题，如果不知道这些元数据表，基本无法进行深入、精确的定位。

工程实践与优化

  • 执行计划分析：定位到慢 SQL 或阻塞 SQL 后，EXPLAIN 是第一步。在 MySQL 8.0.18+ 中，使用 EXPLAIN ANALYZE 可以实际执行并返回真实的执行耗时和行数，比 EXPLAIN 更准确。
  • 工具化：在生产环境中，手动排查往往太慢。应使用监控系统（如 Prometheus + Grafana）监控 information_schema.INNODB_TRX 中事务的持续时间，并设置告警。Percona Toolkit 的 pt-kill 可以配置规则，自动 KILL 掉满足特定条件（如执行时间过长、来自特定主机等）的查询。
  • 锁超时设置：合理配置 innodb_lock_wait_timeout（默认 50s）。虽然不能解决根本问题，但可以防止一个查询因等待锁而无限期 hang 住，使之快速失败，避免雪崩。
  • 事务隔离级别：检查业务是否可以在 READ COMMITTED 隔离级别下运行。相比 REPEATABLE READ，RC 级别下 Gap Lock 更少，并发性能通常更好，但需要评估是否会引入不可重复读的业务问题。

常见追问与延伸题目

  1. 追问：如果阻塞的那个会话（Session）Command 是 Sleep，这是什么情况，怎么处理？

    • 回答要点：这通常是应用层的 bug。代码开启了事务（BEGIN 或 START TRANSACTION），执行了写操作获取了行锁，但后续逻辑没有执行 COMMIT 或 ROLLBACK 就将数据库连接释放回连接池。这个连接对 MySQL 来说是 Sleep 状态，但其事务和持有的锁依然有效。
    • 处理：短期内，KILL 这个 Sleep 的会话 ID 以释放锁。长期来看，必须审查应用代码，确保所有事务路径（包括正常和异常分支）都有 COMMIT 或 ROLLBACK，推荐使用 try-finally 或 try-with-resources 结构来保证资源释放。

  2. 追问：除了锁等待，还有哪些原因可能导致 SQL 看起来 hang 住了？

    • 回答要点：
      • CPU 资源耗尽：查询可能包含复杂的计算、排序、GROUP BY，导致 MySQL 服务器 CPU 100%。通过 top / htop 等系统命令确认。
      • I/O 瓶颈：查询需要扫描大量数据，但磁盘 I/O 达到瓶颈。通过 iostat、iotop 等工具查看磁盘读写情况。可能是因为查询没有走索引导致全表扫描，或实例的 IOPS 不足。
      • 网络延迟：查询本身执行很快，但结果集非常大，sending data 状态会持续很长时间，看起来像 hang 住。
      • 元数据锁（MDL Lock）等待：当有会话在对表进行 DDL 操作（如 ALTER TABLE）时，其他所有对该表的查询和 DML 都会被阻塞，SHOW PROCESSLIST 中 State 会显示为 Waiting for table metadata lock。

  3. 追问：如何精确知道是哪一行或哪些行被锁住了？

    • 回答要点：
      • MySQL 8.0+：这是 performance_schema 的强项。查询 performance_schema.data_locks 表，它的 LOCK_DATA 字段会显示被锁记录的主键值。这提供了最精确的信息。
      • MySQL 5.7 及以下：没有直接的方法。需要结合阻塞 SQL 的 WHERE 条件、INNODB_LOCKS 表中的 lock_index（被锁的索引）以及表结构进行人工推断。例如，如果 lock_index 是 PRIMARY，WHERE 条件是 id = 10，那么很可能就是主键为 10 的行被锁了。如果 WHERE 是一个范围，情况会更复杂，可能涉及记录锁和间隙锁。
← Previous
什么是表锁、自增锁（AUTO-INC Lock）？innodb_autoinc_lock_mode 三个值的差异？
Next →
什么是长事务？长事务为什么对数据库有严重危害？