InnoDB INFORMATION_SCHEMA事务和锁定信息

注意

本节描述了性能模式data_locks和data_lock_waits表所公开的锁定信息，这些信息取代了MySQL 8.0中的INFORMATION_SCHEMAINNODB_LOCKS和INNODB_LOCK_WAITS表。对于用旧的条款类似的讨论INFORMATION_SCHEMA桌，看到的InnoDB INFORMATION_SCHEMA交易及锁定信息中的MySQL 5.7参考手册。

一个INFORMATION_SCHEMA表和两个Performance Schema表使您可以监视InnoDB事务并诊断潜在的锁定问题：

• INNODB_TRX：此INFORMATION_SCHEMA表提供有关内部当前正在执行的每个事务的信息InnoDB，包括事务状态（例如，它是正在运行还是正在等待锁），事务何时开始以及事务正在执行的特定SQL语句。

• data_locks：此性能模式表中的每个保留锁行和每个被锁定等待释放保留锁的锁请求都包含一行：

  • 有一行各举行了锁，无论是持有锁的事务的状态（INNODB_TRX.TRX_STATE是RUNNING，LOCK WAIT，ROLLING BACK或COMMITTING）。

  • InnoDB中等待另一个事务释放锁（INNODB_TRX.TRX_STATEis LOCK WAIT）的每个事务都恰好被一个阻塞锁定请求阻塞。该阻塞锁请求是针对另一个事务以不兼容模式持有的行锁或表锁。锁定请求始终具有与阻止请求的保持锁定模式不兼容的模式（读与写，共享与独占）。

    在其他事务提交或回滚之前，被阻止的事务无法继续进行，从而释放请求的锁。对于每个被阻止的事务，data_locks包含一行，描述该事务已请求的每个锁以及正在等待的锁。

• data_lock_waits：此性能模式表指示哪些事务正在等待给定的锁，或者给定事务正在等待的锁。该表为每个被阻止的事务包含一个或多个行，指示它已请求的锁以及阻止该请求的所有锁。该REQUESTING_ENGINE_LOCK_ID值表示一个事务请求的锁，该BLOCKING_ENGINE_LOCK_ID值表示阻止第一个事务继续进行的锁（由另一个事务持有）。对于任何给定的阻止交易，中的所有行都data_lock_waits具有的相同值REQUESTING_ENGINE_LOCK_ID和的不同值BLOCKING_ENGINE_LOCK_ID。

有关上述表的更多信息，请参见“ INFORMATION_SCHEMA INNODB_TRX表”，“ data_locks表”和“ data_lock_waits表”。

使用InnoDB事务和锁定信息

注意

本节描述了性能模式data_locks和data_lock_waits表所公开的锁定信息，这些信息取代了MySQL 8.0中的INFORMATION_SCHEMAINNODB_LOCKS和INNODB_LOCK_WAITS表。对于用旧的条款类似的讨论INFORMATION_SCHEMA表，请参见使用InnoDB的事务和锁定信息中的MySQL 5.7参考手册。

识别阻塞交易

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

假设三个会话正在同时运行。每个会话都对应一个MySQL线程，并依次执行一个事务。这些会话发出以下语句时，请考虑系统状态，但没有一个提交事务：

• 分会A：

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

• B场：

  SELECT b FROM t FOR UPDATE;
  

• 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值，请参阅在发布会话变为空闲后识别阻塞查询。

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

• 会话B（trx id A4，thread 6）和会话C（trx id A5，thread 7）都在等待会话A（trx id A3，thread 5）。
• 会话C正在等待会话B和会话A。

您可以在INFORMATION_SCHEMAINNODB_TRX表以及性能模式data_locks和data_lock_waits表中看到基础数据。

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

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

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

在发布会话变为空闲后识别阻塞查询

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

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

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

   SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
   

3. 使用THREAD_ID，查询“性能模式”events_statements_current表以确定该线程执行的最后一个查询。例如，如果的THREAD_ID值为28，则发出以下查询：

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

4. 如果线程执行的最后一个查询没有足够的信息确定锁定原因，则可以查询“性能模式”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会话相关联

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

该INFORMATION_SCHEMAINNODB_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_SCHEMAPROCESSLIST和INNODB_TRX表中显示的查询之间可能存在不一致。有关说明，请参见“ InnoDB事务和锁定信息的持久性和一致性”。

下表显示了PROCESSLIST运行大量工作负载的系统的表内容。

下表显示了INNODB_TRX运行大量工作负载的系统的表内容。

下表显示了data_lock_waits运行大量工作负载的系统的表内容。

下表显示了data_locks运行大量工作负载的系统的表内容。

注意

本节描述了性能模式data_locks和data_lock_waits表所公开的锁定信息，这些信息取代了MySQL 8.0中的INFORMATION_SCHEMAINNODB_LOCKS和INNODB_LOCK_WAITS表。对于用旧的条款类似的讨论INFORMATION_SCHEMA桌，看到 InnoDB的锁锁等待信息中的MySQL 5.7参考手册。

当一个事务在一个表中更新了一行，或将其锁定SELECT FOR UPDATE，InnoDB建立在该行锁的列表或队列。同样，InnoDB为表级锁维护表上的锁列表。如果第二个事务想要以不兼容的模式更新行或锁定已经由先前事务锁定的表InnoDB，则将对该行的锁定请求添加到相应的队列中。为了使事务获取锁，必须删除先前输入到该行或表的锁队列中的所有不兼容锁请求（当持有或请求这些锁的事务提交或回滚时会发生这种情况）。

事务可以具有针对不同的行或表的任意数量的锁定请求。在任何给定的时间，一个事务可以请求另一个事务持有的锁，在这种情况下，该事务被另一个事务阻止。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务不等待锁，则它处于RUNNING状态。如果事务正在等待锁，则它处于LOCK WAIT状态。（该INFORMATION_SCHEMAINNODB_TRX表指示事务状态值。）

性能架构data_locks表为每个LOCK WAIT事务保存一个或多个行，指示任何阻止其进度的锁定请求。该表还包含一行，描述给定行或表的未决锁队列中的每个锁。性能模式data_lock_waits表显示了事务已持有的哪些锁正在阻止其他事务请求的锁。

InnoDB锁定和锁定等待信息

注意

本节描述了性能模式data_locks和data_lock_waits表所公开的锁定信息，这些信息取代了MySQL 8.0中的INFORMATION_SCHEMAINNODB_LOCKS和INNODB_LOCK_WAITS表。对于用旧的条款类似的讨论INFORMATION_SCHEMA桌，看到 InnoDB的锁锁等待信息中的MySQL 5.7参考手册。

当一个事务在一个表中更新了一行，或将其锁定SELECT FOR UPDATE，InnoDB建立在该行锁的列表或队列。同样，InnoDB为表级锁维护表上的锁列表。如果第二个事务想要以不兼容的模式更新行或锁定已经由先前事务锁定的表InnoDB，则将对该行的锁定请求添加到相应的队列中。为了使事务获取锁，必须删除先前输入到该行或表的锁队列中的所有不兼容锁请求（当持有或请求这些锁的事务提交或回滚时会发生这种情况）。

事务可以具有针对不同的行或表的任意数量的锁定请求。在任何给定的时间，一个事务可以请求另一个事务持有的锁，在这种情况下，该事务被另一个事务阻止。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务不等待锁，则它处于RUNNING状态。如果事务正在等待锁，则它处于LOCK WAIT状态。（该INFORMATION_SCHEMAINNODB_TRX表指示事务状态值。）

性能架构data_locks表为每个LOCK WAIT事务保存一个或多个行，指示任何阻止其进度的锁定请求。该表还包含一行，描述给定行或表的未决锁队列中的每个锁。性能模式data_lock_waits表显示了事务已持有的哪些锁正在阻止其他事务请求的锁。

InnoDB事务和锁定信息的持久性和一致性

注意

本节描述了性能模式data_locks和data_lock_waits表所公开的锁定信息，这些信息取代了MySQL 8.0中的INFORMATION_SCHEMAINNODB_LOCKS和INNODB_LOCK_WAITS表。对于用旧的条款类似的讨论INFORMATION_SCHEMA表，请参见持久性和InnoDB事务和锁定信息的一致性中的MySQL 5.7参考手册。

事务表和锁定表（INFORMATION_SCHEMAINNODB_TRX表，性能模式data_locks和data_lock_waits表）公开的数据代表对快速变化的数据的一瞥。这与用户表不同，用户表仅在应用程序启动的更新发生时才更改数据。基础数据是内部系统管理的数据，并且可以快速更改：

• 数据可能不是之间是一致的INNODB_TRX，data_locks和data_lock_waits表。

  在data_locks与data_lock_waits表暴露从实时数据InnoDB存储引擎，提供有关的交易锁定inormation INNODB_TRX表。从锁表检索的数据在SELECT执行时存在，但在客户端使用查询结果时可能已消失或更改。

  data_locks与联接data_lock_waits可以显示中的行data_lock_waits，该行标识data_locks不再存在或尚不存在的父行。

• 事务表和锁定表中的数据可能与INFORMATION_SCHEMAPROCESSLIST表或性能模式threads表中的数据不一致。

  例如，在比较InnoDB事务表和锁定表中的数据与表中的数据时，应格外小心PROCESSLIST。即使您发出单个SELECT（例如，join INNODB_TRX和PROCESSLIST），这些表的内容也通常不一致。可能INNODB_TRX会引用其中不存在的行，PROCESSLIST或者所引用的事务中当前正在执行的SQL查询与中的INNODB_TRX.TRX_QUERY行不同PROCESSLIST.INFO。