mysql如何排查慢查询锁等待

要排查MySQL慢查询中的锁等待，需先定位正在等待和持有锁的会话。通过SHOW PROCESSLIST查看线程状态，若出现Waiting for row lock或metadata lock等状态，表明存在锁等待。结合SHOW ENGINE INNODB STATUS分析事务信息，重点关注TRANSACTIONS部分中LOCK WAIT详情，识别出被阻塞与阻塞者的事务ID及SQL语句。利用information_schema.INNODB_LOCKS和INNODB_LOCK_WAITS视图可结构化查询锁类型、模式及等待关系，判断是行锁（RECORD）、表锁（TABLE）还是意向锁（IS/IX）。Performance Schema中的data_locks、data_lock_waits和events_waits_current等视图提供更细粒度监控，能精准追踪锁等待事件、耗时及关联线程。常见原因包括缺失索引导致全表扫描加锁、大事务长时间未提交、DDL操作引发表锁、热点数据争用及隔离级别设置不当。最终结合EXPLAIN分析执行计划，优化索引设计、缩短事务长度、调整并发策略以解决根本问题。

MySQL慢查询如果涉及到锁等待，通常意味着你的系统遇到了并发瓶颈，某个操作被其他事务阻塞了。这不单单是SQL本身执行效率的问题，更是资源争抢的信号。要排查这类问题，我们得像侦探一样，从多个角度收集线索，找出到底是谁在“霸占”资源，又是什么操作在“苦苦等待”。

在处理这类问题时，我通常会从“正在发生什么”和“历史发生了什么”两个维度入手，结合实时监控和日志分析。

解决方案

要排查MySQL慢查询中的锁等待，核心思路是先找出当前正在等待的会话和持有锁的会话，然后分析它们正在执行的SQL以及事务上下文。

1. 实时查看当前活动会话： 最直接的方式就是 SHOW PROCESSLIST; 或 SHOW FULL PROCESSLIST;。关注 State 列，如果看到 Waiting for table metadata lock、Waiting for row lock、Locked 等状态，那就说明有锁等待。记下这些会话的 Id。

2. 深入探查InnoDB锁情况： 对于InnoDB引擎，SHOW ENGINE INNODB STATUS; 是个宝藏。输出内容很长，需要重点关注 LATEST DETECTED DEADLOCK（如果有死锁）、TRANSACTIONS 部分。在 TRANSACTIONS 部分，你会看到当前活跃的事务，哪些事务正在等待锁 (LOCKED WAIT)，哪些事务持有锁。它会清晰地告诉你 TRANSACTION (ID)、undo log entries、ISOLATION LEVEL，以及最重要的 LOCK WAIT 详情，比如哪个事务在等待哪个锁，哪个事务持有这个锁。

   -- 示例输出片段，你需要手动解析
   ---TRANSACTION 12345678, ACTIVE 12 sec
   --waiting for row lock
   --mysql tables in use 1, locked 1
   --...
   --LOCK WAIT for L_ROW_EX_REC_NOT_GAP on table `mydb`.`mytable` index `PRIMARY`
   --held by TRANSACTION 87654321, ACTIVE 30 sec
   --...

   登录后复制

3. 利用 information_schema 视图：information_schema.INNODB_LOCKS 和 information_schema.INNODB_LOCK_WAITS 提供了更结构化的锁信息，非常适合编写查询来定位问题。

   • INNODB_LOCKS：显示当前所有被持有的锁。
   • INNODB_LOCK_WAITS：显示当前所有的锁等待关系，谁在等谁。

   一个经典的查询组合，用于找出等待者和持有者：

   SELECT
       r.trx_id AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread_id,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread_id,
       b.trx_query AS blocking_query,
       lw.requesting_engine_lock_id AS waiting_lock_id,
       lw.blocking_engine_lock_id AS blocking_lock_id,
       l.lock_mode,
       l.lock_type,
       l.lock_table,
       l.lock_index
   FROM
       information_schema.innodb_lock_waits lw
   JOIN
       information_schema.innodb_trx r ON lw.requesting_trx_id = r.trx_id
   JOIN
       information_schema.innodb_trx b ON lw.blocking_trx_id = b.trx_id
   JOIN
       information_schema.innodb_locks l ON lw.requesting_engine_lock_id = l.lock_id;

   登录后复制

   这个查询能清晰地展示哪个事务（waiting_trx_id）因为哪个锁（waiting_lock_id）被哪个事务（blocking_trx_id）阻塞了，以及它们分别在执行什么查询。

4. 分析慢查询日志： 慢查询日志（slow_query_log）如果配置了 log_queries_not_using_indexes 和 long_query_time，会记录执行时间超过阈值的查询。虽然它不直接告诉你锁等待，但如果一个查询因为锁等待而变慢，它就会被记录下来。结合 pt-query-digest 这样的工具来分析慢查询日志，可以找出那些经常变慢的查询模式，然后针对性地分析它们是否涉及锁。

5. Performance Schema： MySQL 5.6+ 的 Performance Schema 提供了更细粒度的监控能力。performance_schema.events_waits_current、performance_schema.data_locks 和 performance_schema.data_lock_waits 等视图可以提供非常详细的锁事件信息，包括等待的类型、持续时间、涉及的对象等。这对于长期监控和分析锁问题非常有帮助。

   -- 查看当前正在等待的锁事件
   SELECT
       event_id,
       event_name,
       object_schema,
       object_name,
       index_name,
       lock_type,
       lock_mode,
       lock_status,
       trx_id,
       thread_id,
       processlist_id,
       timer_wait / 1000000 AS wait_ms
   FROM
       performance_schema.data_lock_waits
   WHERE
       lock_status = 'WAITING';

   登录后复制

定位到问题事务和SQL后，下一步就是分析其背后的业务逻辑、索引使用情况、事务隔离级别以及并发访问模式，从而找到根本原因并优化。

慢查询中的锁等待，通常是哪些“疑犯”造成的？

说实话，每次遇到慢查询伴随锁等待，我都会觉得这问题有点“高级”，因为它不仅仅是SQL写得好不好，更是系统架构和并发控制的体现。常见的“疑犯”大致有这么几类：

1. 缺少索引或索引失效： 这是最常见的元凶。一个本该走索引的查询，因为没有合适的索引或者查询条件导致索引失效，不得不进行全表扫描。如果这个全表扫描又恰好在一个事务里，并且更新了某些行，那么它就会持有这些行的锁，直到事务结束。如果扫描的行数巨大，或者扫描过程中碰到了其他事务要操作的行，那锁等待就来了。我见过最离谱的情况是，一个简单的 UPDATE 语句，因为 WHERE 条件没有索引，直接锁住了整个表，导致所有相关操作都挂起。

2. 大事务： 顾名思义，就是执行时间超长、涉及行数超多、或者包含了复杂业务逻辑的事务。一个事务如果需要更新大量数据，或者执行了很长时间才提交，那么它在执行期间持有的锁就会长时间不释放。其他需要操作这些数据的事务就只能干等着。尤其是那些批处理任务，如果设计不当，很容易成为“锁霸”。

3. DDL操作： 像 ALTER TABLE 这种数据定义语言操作，在某些MySQL版本或操作模式下，可能会对表加表级锁，或者需要很长时间才能完成，期间会阻塞其他对该表的读写操作。虽然现在有Inplace DDL和Online DDL，但也不是万能的，某些复杂操作依然可能导致长时间的锁。

4. 热点行/热点数据： 某些业务场景下，特定行或一小部分数据会被高频访问和更新。比如计数器、订单状态、库存数量等。当多个并发事务同时尝试修改这些“热点”数据时，就会形成严重的行锁竞争，导致大量的锁等待。这就像大家都在抢一个稀缺资源，自然就得排队。

5. 事务隔离级别不当： 不同的事务隔离级别对锁的持有和释放策略有影响。例如，REPEATABLE READ 隔离级别会持有读取到的行的共享锁（在某些情况下，特别是SELECT ... FOR UPDATE），直到事务结束，这可能比 READ COMMITTED 导致更长的锁持有时间。

   

   蓝心千询

   蓝心千询是vivo推出的一个多功能AI智能助手

   34

   查看详情

排查时，我通常会结合 EXPLAIN 分析SQL的执行计划，看看是不是有全表扫描或者索引使用不当。然后，我会尝试复现问题，或者在生产环境使用上面提到的监控工具，实时观察锁等待的发生。

如何区分是表锁、行锁还是意向锁导致的等待？

要区分具体是哪种锁导致的等待，确实需要对MySQL的锁机制有点了解，并且知道怎么从诊断信息中提取线索。这就像医生看病，症状不同，病因就不同。

1. 表锁 (Table Locks)：

   • 特征： 当一个事务对整个表加锁时，其他所有对该表的读写操作都会被阻塞。在 SHOW PROCESSLIST 中，你可能会看到 Waiting for table metadata lock 或者 Locked 状态，并且 Command 列显示 Query 或 Execute。
   • 诊断： SHOW OPEN TABLES WHERE In_use > 0; 可以看到哪些表正在被使用。SHOW ENGINE INNODB STATUS 在 SEMAPHORES 部分可能会提到 waiting for table lock。information_schema.INNODB_LOCKS 中 lock_type 会是 TABLE。
   • 常见场景： DDL操作（如 ALTER TABLE、DROP TABLE）、LOCK TABLES ... WRITE 命令，或者某些特定情况下的全表扫描更新。

2. 行锁 (Row Locks)：

   • 特征： 这是InnoDB引擎最常用的锁类型，它只锁定受影响的行，理论上并发性最高。但如果多个事务争抢同一行，就会出现等待。在 SHOW PROCESSLIST 中，你可能会看到 Waiting for row lock 状态。
   • 诊断： SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 部分会明确指出 waiting for row lock。information_schema.INNODB_LOCKS 中，lock_type 会是 RECORD，并且会显示 lock_table 和 lock_index。通过 lock_index 和 lock_data，你可以精确地知道是哪张表的哪个索引上的哪条记录被锁了。
   • 常见场景： UPDATE、DELETE、INSERT 操作，或者 SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE 语句，当它们作用于同一行数据时。

3. 意向锁 (Intention Locks)：

   • 特征： 意向锁是表级锁，但它不是用来阻塞其他操作的，而是用来表明一个事务打算在表中的某些行上加行锁（共享意向锁 IS）或排他行锁（排他意向锁 IX）。它的主要目的是为了兼容性，让表级锁和行级锁能够共存。意向锁本身通常不会导致等待，它更多是一个信号。如果看到意向锁，那通常意味着有行锁正在被持有或请求。
   • 诊断： 在 information_schema.INNODB_LOCKS 中，lock_type 会是 TABLE，但 lock_mode 会是 IS (Intention Shared) 或 IX (Intention Exclusive)。
   • 常见场景： 任何对行加锁的操作，都会先在表上加一个对应的意向锁。例如，UPDATE 一行会先在表上加 IX 锁，然后对该行加 X 锁。

实际排查时，我们主要关注表锁和行锁。意向锁虽然是表级锁，但它通常不是直接导致慢查询锁等待的元凶，而是行锁的前置信号。通过 information_schema.INNODB_LOCKS 和 INNODB_LOCK_WAITS 视图的 lock_type 和 lock_mode 字段，你可以非常清晰地判断是哪种锁在作祟。比如，如果 lock_type 是 TABLE 且 lock_mode 是 X，那基本就是表级排他锁了；如果是 RECORD 且 lock_mode 是 X 或 S，那就是行级锁。

诊断锁等待时，哪些Performance Schema视图最有帮助？

Performance Schema 在排查锁等待问题上，提供了比 SHOW ENGINE INNODB STATUS 更强大、更灵活、也更适合编程分析的能力。它能让你以结构化的方式查询锁事件，而不是解析一大段文本。我个人觉得以下几个视图在诊断锁等待时特别有用：

1. performance_schema.data_locks： 这个视图列出了当前所有被InnoDB事务持有的锁。它非常详细，包括锁的类型（表锁、行锁）、模式（共享S、排他X、意向IS、IX）、锁定的对象（数据库、表、索引、记录）、以及持有该锁的事务ID。

   -- 查看当前所有被持有的锁
   SELECT
       ENGINE_LOCK_ID,
       ENGINE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       INDEX_NAME,
       LOCK_TYPE,
       LOCK_MODE,
       LOCK_STATUS, -- 'GRANTED' 表示已获得，'WAITING' 表示正在等待
       TRX_ID,
       TRX_MYSQL_THREAD_ID,
       TRX_QUERY
   FROM
       performance_schema.data_locks;

   登录后复制

   通过 LOCK_STATUS 字段，你可以快速识别出哪些锁处于等待状态，哪些锁已经被授予。

2. performance_schema.data_lock_waits： 这是 data_locks 的一个补充，它直接展示了锁等待的关系：哪个事务在等待哪个事务持有的哪个锁。这就像一张等待链条，清晰地告诉你谁是“受害者”，谁是“加害者”。

   -- 查看当前的锁等待关系
   SELECT
       REQUESTING_ENGINE_LOCK_ID,
       REQUESTING_TRX_ID,
       REQUESTING_TRX_MYSQL_THREAD_ID,
       REQUESTING_TRX_QUERY,
       BLOCKING_ENGINE_LOCK_ID,
       BLOCKING_TRX_ID,
       BLOCKING_TRX_MYSQL_THREAD_ID,
       BLOCKING_TRX_QUERY
   FROM
       performance_schema.data_lock_waits;

   登录后复制

   这个视图的强大之处在于它直接把等待者和阻塞者关联起来了，省去了我们手动匹配 TRX_ID 的麻烦。结合 data_locks，你就能知道具体是哪个表、哪个索引上的哪条记录被锁了。

3. performance_schema.events_waits_current 和 events_waits_history： 这两个视图记录了线程当前正在等待的事件（_current）和最近完成的事件（_history）。锁等待也是一种等待事件。你可以通过过滤 EVENT_NAME 字段来关注锁相关的等待。

   -- 查看当前正在进行的锁等待事件
   SELECT
       THREAD_ID,
       EVENT_ID,
       EVENT_NAME,
       TIMER_WAIT / 1000000 AS WAIT_MS,
       SOURCE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       INDEX_NAME
   FROM
       performance_schema.events_waits_current
   WHERE
       EVENT_NAME LIKE 'wait/lock/table/%' OR EVENT_NAME LIKE 'wait/lock/metadata/%' OR EVENT_NAME LIKE 'wait/lock/rwlock/%';

   登录后复制

   这些视图能让你看到某个线程因为锁等待而花费了多少时间，以及具体是哪种类型的锁等待。_history 版本可以帮助你回顾过去发生过的锁等待，对于分析偶发性问题非常有价值。

4. performance_schema.threads： 这个视图可以帮助你把 Performance Schema 中的 THREAD_ID 映射到 SHOW PROCESSLIST 中的 Id (即 PROCESSLIST_ID)，这样就能把 Performance Schema 的详细信息和熟悉的 PROCESSLIST 对应起来。

   SELECT
       THREAD_ID,
       PROCESSLIST_ID,
       PROCESSLIST_USER,
       PROCESSLIST_HOST,
       PROCESSLIST_DB,
       PROCESSLIST_COMMAND,
       PROCESSLIST_STATE,
       PROCESSLIST_INFO
   FROM
       performance_schema.threads
   WHERE
       PROCESSLIST_ID IS NOT NULL;

   登录后复制

利用这些 Performance Schema 视图，你可以构建出非常强大的实时监控和历史分析脚本，而不再仅仅依赖于手动执行命令和肉眼解析日志。这对于自动化运维和快速定位复杂锁问题来说，简直是神器。当然，开启 Performance Schema 会带来一定的性能开销，所以在生产环境使用时需要权衡。

以上就是mysql如何排查慢查询锁等待的详细内容，更多请关注php中文网其它相关文章！