Documentation Home

14.15.2.1 使用 InnoDB 事务和锁定信息

识别阻塞交易

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

假设三个会话同时运行。每个session对应一个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       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;
等待 trx id 等待线程 等待查询 阻塞 trx id 阻塞线程 阻塞查询
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

在上表中,您可以通过 等待查询阻塞查询 列来识别会话。如你看到的:

  • 会话 B (trx id A4, thread 6) 和会话 C (trx id A5, thread 7) 都在等待会话 A (trx id A3, thread 5)。

  • 会话 C 正在等待会话 B 和会话 A。

您可以在表 INNODB_TRXINNODB_LOCKS和 中查看基础数据INNODB_LOCK_WAITS

下表显示了一些示例内容 INFORMATION_SCHEMA.INNODB_TRX

交易编号 发送状态 trx 开始 trx 请求的锁 ID trx 等待开始 卡车重量 trx mysql线程id 交易查询
A3 RUN­NING 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

下表显示了一些示例内容 INFORMATION_SCHEMA.INNODB_LOCKS

锁ID 锁定trx id 锁定模式 锁型 锁表 锁索引 锁定数据
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

下表显示了一些示例内容 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
将 InnoDB 事务与 MySQL 会话相关联

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

表中的以下输出 INFORMATION_SCHEMA取自负载稍大的系统。可以看出,有几个事务在运行。

下表INNODB_LOCKSINNODB_LOCK_WAITS表格表明:

  • 事务77F(执行 INSERT)正在等待事务77E77D77B提交。

  • 事务77E(执行一个 INSERT)正在等待事务77D77B提交。

  • 事务77D(正在执行 INSERT)正在等待事务77B提交。

  • 事务77B(正在执行 INSERT)正在等待事务77A提交。

  • 事务77A正在运行,当前正在执行SELECT

  • 事务E56(正在执行 INSERT)正在等待事务E55提交。

  • 事务E55(正在执行 INSERT)正在等待事务19C提交。

  • 事务19C正在运行,当前正在执行一个INSERT.

笔记

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

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

ID 用户 主持人 D B 命令 时间 状态 信息
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

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

交易编号 发送状态 trx 开始 trx 请求的锁 ID trx 等待开始 卡车重量 trx mysql线程id 交易查询
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 RUN­NING 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 RUN­NING 2008-01-15 13:09:10 NULL NULL 2900 130 INSERT INTO t2 VALUES …
E15 RUN­NING 2008-01-15 13:08:59 NULL NULL 5395 61 INSERT INTO t2 VALUES …
51D RUN­NING 2008-01-15 13:08:47 NULL NULL 9807 8 INSERT INTO t2 VALUES …

下表显示了 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

下表显示了 INFORMATION_SCHEMA.INNODB_LOCKS 运行繁重 工作负载的系统的内容。

锁ID 锁定trx id 锁定模式 锁型 锁表 锁索引 锁定数据
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