【ACDU 翻译】MySQL 15.15.2.1 使用 InnoDB 事务和锁定信息

笔记

本节描述了由性能模式data_locks和 data_lock_waits表公开的锁定信息，它们取代了MySQL 8.0 中的INFORMATION_SCHEMA INNODB_LOCKS和 INNODB_LOCK_WAITS表。有关根据旧INFORMATION_SCHEMA表编写的类似讨论，请参阅 MySQL 5.7 参考手册中的Using InnoDB Transaction and Locking Information。

识别阻塞事务

有时识别哪个事务阻塞了另一个事务是有帮助的。包含有关 InnoDB事务和数据锁的信息的表使您能够确定哪个事务正在等待另一个事务，以及正在请求哪个资源。（有关这些表的描述，请参阅 第 15.15.2 节，“InnoDB INFORMATION_SCHEMA 事务和锁定信息”。）

假设三个session同时运行。每个session对应一个 MySQL 线程，一个接一个地执行事务。当这些session发出以下语句但还没有提交其事务时，请考虑系统的状态：

• session A：

  BEGIN;
  SELECT a FROM t FOR UPDATE;
  SELECT SLEEP(100);
  

• session B：

  SELECT b FROM t FOR UPDATE;
  

• session C：

  SELECT c FROM t FOR UPDATE;
  

在这种情况下，使用以下查询来查看哪些事务正在等待以及哪些事务正在阻塞它们：

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

或者，更简单地说，使用sys模式 innodb_lock_waits视图：

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

如果为阻塞查询报告了 NULL 值，请参阅 在发出session变为空闲后识别阻塞查询。

在上表中，您可以通过 “等待查询”或“阻塞查询” 列来识别session。如你看到的：

• session B (trx id A4, thread 6) 和session C (trx id A5, thread 7) 都在等待session A (trx id A3, thread 5)。
• session C 正在等待session B 以及session A。

INFORMATION_SCHEMA INNODB_TRX您可以在表和 Performance Schemadata_locks和 表 中看到基础数据 data_lock_waits。

下表显示了该表的一些示例内容 INNODB_TRX。

下表显示了该表的一些示例内容 data_locks。

下表显示了该表的一些示例内容 data_lock_waits。

在发出session变为空闲后识别阻塞查询

识别阻塞事务时，如果发出查询的session已空闲，则为阻塞查询报告 NULL 值。在这种情况下，请使用以下步骤来确定阻塞查询：

1. 识别阻塞事务的进程列表 ID。在sys.innodb_lock_waits 表中，阻塞事务的进程列表 ID 是blocking_pid值。

2. 使用blocking_pid，查询 MySQL Performance Schemathreads 表以确定THREAD_ID阻塞事务的。例如，如果 blocking_pid是 6，则发出以下查询：

   SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
   

3. 使用THREAD_ID，查询 Performance Schema events_statements_current 表以确定线程执行的最后一个查询。例如，如果THREAD_ID是 28，请发出以下查询：

   SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
   WHERE THREAD_ID = 28\G
   

4. 如果线程执行的最后一个查询没有足够的信息来确定持有锁的原因，您可以查询 Performance Schema events_statements_history 表以查看线程执行的最后 10 条语句。

   SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
   WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
   

将 InnoDB 事务与 MySQL session相关联

InnoDB有时将内部锁定信息与 MySQL 维护的session级信息 相关联很有用 。例如，对于给定的InnoDB 事务 ID，您可能想知道相应的 MySQL session ID 和可能持有锁并因此阻塞其他事务的session的名称。

INFORMATION_SCHEMA INNODB_TRX表和性能模式data_locks和 表 的以下输出 data_lock_waits取自一个有些负载的系统。可以看出，有几个事务正在运行。

以下data_locks和 data_lock_waits表格显示：

• 事务77F（执行 INSERT）正在等待事务77E、 77D和77B提交。
• 事务77E（执行一个 INSERT）正在等待事务77D并 77B提交。
• 事务77D（执行一个 INSERT）正在等待事务77B提交。
• 事务77B（执行一个 INSERT）正在等待事务77A提交。
• 事务77A正在运行，当前正在执行SELECT。
• 事务E56（执行一个 INSERT）正在等待事务E55提交。
• 事务E55（执行一个 INSERT）正在等待事务19C提交。
• 事务19C正在运行，当前正在执行一个INSERT.

笔记

INFORMATION_SCHEMA PROCESSLIST和 INNODB_TRX表 中显示的查询之间可能存在不一致 。有关解释，请参阅 第 15.15.2.3 节，“InnoDB 事务和锁定信息的持久性和一致性”。

下表显示了 PROCESSLIST运行繁重工作负载的系统的表格内容。

下表显示了 INNODB_TRX运行繁重工作负载的系统的表格内容。

下表显示了 data_lock_waits运行繁重 工作负载的系统的表格内容。

下表显示了 data_locks运行繁重工作负载的系统的表格内容。