14.16.2.1 使用 InnoDB 事务和锁定信息

识别阻止的 Transaction

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

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

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

SELECT b FROM t FOR UPDATE;

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 查询”或“阻止查询”列来标识会话。如你看到的：

您可以在 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 值。在这种情况下，请使用以下步骤确定阻止查询：

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

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

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 显示：

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
首页