14.16.2.1 使用 InnoDB 事务和锁定信息

识别阻止的 Transaction

识别哪个 Transaction 会阻止另一个 Transaction 有时会很有帮助。包含有关InnoDB事务和数据锁的信息的 table 使您能够确定哪个事务正在 await 另一个事务以及正在请求哪个资源。 (有关这些 table 的说明，请参见第 14.16.2 节“ InnoDB INFORMATION_SCHEMA 事务和锁定信息”。)

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

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;

在这种情况下，使用以下查询来查看哪些事务正在 await，哪些事务正在阻止它们：

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       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_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 值，请参见在发布会话变为空闲之后识别阻塞查询。

awaittrx ID	waiting thread	waiting query	阻止 trx ID	blocking thread	blocking query
`A4`	`6`	`SELECT b FROM t FOR UPDATE`	`A3`	`5`	`SELECT SLEEP(100)`
`A5`	`7`	`SELECT c FROM t FOR UPDATE`	`A3`	`5`	`SELECT SLEEP(100)`
`A5`	`7`	`SELECT c FROM t FOR UPDATE`	`A4`	`6`	`SELECT b FROM t FOR UPDATE`

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

会话 B(trx id A4，线程6)和会话 C(trx id A5，线程7)都在 await 会话 A(trx id A3，线程5)。
会话 C 正在 await 会话 B 和会话 A。

您可以在 tableINNODB_TRX，INNODB_LOCKS和INNODB_LOCK_WAITS中查看基础数据。

下 table 显示了INFORMATION_SCHEMA.INNODB_TRX的一些示例内容。

trx id	trx state	trx started	trx 请求的锁定 ID	trxawait 开始	trx weight	trx mysql 线程 ID	trx query
`A3`	`RUNNING`	`2008-01-15 16:44:54`	`NULL`	`NULL`	`2`	`5`	`SELECT SLEEP(100)`
`A4`	`LOCK WAIT`	`2008-01-15 16:45:09`	`A4:1:3:2`	`2008-01-15 16:45:09`	`2`	`6`	`SELECT b FROM t FOR UPDATE`
`A5`	`LOCK WAIT`	`2008-01-15 16:45:14`	`A5:1:3:2`	`2008-01-15 16:45:14`	`2`	`7`	`SELECT c FROM t FOR UPDATE`

下 table 显示了INFORMATION_SCHEMA.INNODB_LOCKS的一些示例内容。

lock id	锁定 trx ID	lock mode	lock type	lock table	lock index	lock data
`A3:1:3:2`	`A3`	`X`	`RECORD`	`test.t`	`PRIMARY`	`0x0200`
`A4:1:3:2`	`A4`	`X`	`RECORD`	`test.t`	`PRIMARY`	`0x0200`
`A5:1:3:2`	`A5`	`X`	`RECORD`	`test.t`	`PRIMARY`	`0x0200`

下 table 显示了INFORMATION_SCHEMA.INNODB_LOCK_WAITS的一些示例内容。

请求 trx ID	请求的锁 ID	阻止 trx ID	阻止锁 ID
`A4`	`A4:1:3:2`	`A3`	`A3:1:3:2`
`A5`	`A5:1:3:2`	`A3`	`A3:1:3:2`
`A5`	`A5:1:3:2`	`A4`	`A4:1:3:2`

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

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

标识阻止事务的进程列 tableID。在sys.innodb_lock_waitstable 中，阻止事务的流程列 tableID 为blocking_pid值。
使用blocking_pid，查询 MySQL Performance Schema threadstable，以确定阻塞事务的THREAD_ID。例如，如果blocking_pid为 6，则发出以下查询：

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

使用THREAD_ID，查询 Performance Schema events_statements_currenttable，以确定该线程执行的最后一个查询。例如，如果THREAD_ID为 28，则发出以下查询：

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

如果线程执行的最后一个查询不足以确定锁定原因，则可以查询 Performance Schema events_statements_historytable 以查看线程执行的最后 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_SCHEMAtable 的以下输出取自某种程度上已加载的系统。可以看出，有几个事务正在运行。

以下INNODB_LOCKS和INNODB_LOCK_WAITStable 显示：

事务77F(正在执行INSERT)正在 await 事务77E，77D和77B提交。
事务77E(正在执行INSERT)正在 await 事务77D和77B提交。
事务77D(正在执行INSERT)正在 await 事务77B提交。
事务77B(正在执行INSERT)正在 await 事务77A提交。
事务77A正在运行，当前正在执行SELECT。
事务E56(正在执行INSERT)正在 await 事务E55提交。
事务E55(正在执行INSERT)正在 await 事务19C提交。
事务19C正在运行，当前正在执行INSERT。

Note

INFORMATION_SCHEMA PROCESSLIST和INNODB_TRXtable 中显示的查询之间可能存在不一致。有关说明，请参见第 14.16.2.3 节“ InnoDB 事务和锁定信息的持久性和一致性”。

下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.PROCESSLIST的内容。

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
`384`	`root`	`localhost`	`test`	`Query`	`10`	`update`	`INSERT INTO t2 VALUES …`
`257`	`root`	`localhost`	`test`	`Query`	`3`	`update`	`INSERT INTO t2 VALUES …`
`130`	`root`	`localhost`	`test`	`Query`	`0`	`update`	`INSERT INTO t2 VALUES …`
`61`	`root`	`localhost`	`test`	`Query`	`1`	`update`	`INSERT INTO t2 VALUES …`
`8`	`root`	`localhost`	`test`	`Query`	`1`	`update`	`INSERT INTO t2 VALUES …`
`4`	`root`	`localhost`	`test`	`Query`	`0`	`preparing`	`SELECT * FROM PROCESSLIST`
`2`	`root`	`localhost`	`test`	`Sleep`	`566`	``	`NULL`

下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.INNODB_TRX的内容。

trx id	trx state	trx started	trx 请求的锁定 ID	trxawait 开始	trx weight	trx mysql 线程 ID	trx query
`77F`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77F`	`2008-01-15 13:10:16`	`1`	`876`	`INSERT INTO t09 (D, B, C) VALUES …`
`77E`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77E`	`2008-01-15 13:10:16`	`1`	`875`	`INSERT INTO t09 (D, B, C) VALUES …`
`77D`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77D`	`2008-01-15 13:10:16`	`1`	`874`	`INSERT INTO t09 (D, B, C) VALUES …`
`77B`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77B:733:12:1`	`2008-01-15 13:10:16`	`4`	`873`	`INSERT INTO t09 (D, B, C) VALUES …`
`77A`	`RUNNING`	`2008-01-15 13:10:16`	`NULL`	`NULL`	`4`	`872`	`SELECT b, c FROM t09 WHERE …`
`E56`	`LOCK WAIT`	`2008-01-15 13:10:06`	`E56:743:6:2`	`2008-01-15 13:10:06`	`5`	`384`	`INSERT INTO t2 VALUES …`
`E55`	`LOCK WAIT`	`2008-01-15 13:10:06`	`E55:743:38:2`	`2008-01-15 13:10:13`	`965`	`257`	`INSERT INTO t2 VALUES …`
`19C`	`RUNNING`	`2008-01-15 13:09:10`	`NULL`	`NULL`	`2900`	`130`	`INSERT INTO t2 VALUES …`
`E15`	`RUNNING`	`2008-01-15 13:08:59`	`NULL`	`NULL`	`5395`	`61`	`INSERT INTO t2 VALUES …`
`51D`	`RUNNING`	`2008-01-15 13:08:47`	`NULL`	`NULL`	`9807`	`8`	`INSERT INTO t2 VALUES …`

下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.INNODB_LOCK_WAITS的内容。

请求 trx ID	请求的锁 ID	阻止 trx ID	阻止锁 ID
`77F`	`77F:806`	`77E`	`77E:806`
`77F`	`77F:806`	`77D`	`77D:806`
`77F`	`77F:806`	`77B`	`77B:806`
`77E`	`77E:806`	`77D`	`77D:806`
`77E`	`77E:806`	`77B`	`77B:806`
`77D`	`77D:806`	`77B`	`77B:806`
`77B`	`77B:733:12:1`	`77A`	`77A:733:12:1`
`E56`	`E56:743:6:2`	`E55`	`E55:743:6:2`
`E55`	`E55:743:38:2`	`19C`	`19C:743:38:2`

下 table 显示了运行重workload的系统的INFORMATION_SCHEMA.INNODB_LOCKS的内容。

lock id	锁定 trx ID	lock mode	lock type	lock table	lock index	lock data
`77F:806`	`77F`	`AUTO_INC`	`TABLE`	`test.t09`	`NULL`	`NULL`
`77E:806`	`77E`	`AUTO_INC`	`TABLE`	`test.t09`	`NULL`	`NULL`
`77D:806`	`77D`	`AUTO_INC`	`TABLE`	`test.t09`	`NULL`	`NULL`
`77B:806`	`77B`	`AUTO_INC`	`TABLE`	`test.t09`	`NULL`	`NULL`
`77B:733:12:1`	`77B`	`X`	`RECORD`	`test.t09`	`PRIMARY`	`supremum pseudo-record`
`77A:733:12:1`	`77A`	`X`	`RECORD`	`test.t09`	`PRIMARY`	`supremum pseudo-record`
`E56:743:6:2`	`E56`	`S`	`RECORD`	`test.t2`	`PRIMARY`	`0, 0`
`E55:743:6:2`	`E55`	`X`	`RECORD`	`test.t2`	`PRIMARY`	`0, 0`
`E55:743:38:2`	`E55`	`S`	`RECORD`	`test.t2`	`PRIMARY`	`1922, 1922`
`19C:743:38:2`	`19C`	`X`	`RECORD`	`test.t2`	`PRIMARY`	`1922, 1922`

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35