本节描述由性能架构公开的锁定信息data_locks
而且data_lock_waits
表,取代INFORMATION_SCHEMA
INNODB_LOCKS
而且INNODB_LOCK_WAITS
表在MySQL 8.0。对于类似的讨论,写在更老的术语INFORMATION_SCHEMA
表,请参阅使用InnoDB事务和锁定信息,在MySQL 5.7参考手册.
有时,识别哪个事务阻塞了另一个事务是很有帮助的。包含关于的信息的表InnoDB
事务和数据锁使您能够确定哪个事务正在等待另一个事务,以及正在请求哪些资源。(有关这些表的描述,请参见第15.15.2节“InnoDB的事务和锁定信息”)。
假设有三个会话同时运行。每个会话对应一个MySQL线程,并一个接一个地执行事务。当这些会话发出了以下语句,但没有一个会话提交事务时,考虑系统的状态:
会话:
开始;SELECT a FROM t FOR UPDATE选择睡眠(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_waiting w INNER JOIN information_schema。innodb_trx b ON b.trx_id = w.b blocking_engine_transaction_id INNER JOIN信息模式。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_waiting;
如果阻塞查询报告了NULL值,请参见在发出的会话变为空闲后识别阻塞查询.
等待trx id | 等待线程 | 等待查询 | 阻塞trx id | 阻塞线程 | 阻塞查询 |
---|---|---|---|---|---|
A4 |
6 |
从t中选择b进行更新 |
A3 |
5 |
选择睡眠(100) |
A5 |
7 |
从t中选择c进行更新 |
A3 |
5 |
选择睡眠(100) |
A5 |
7 |
从t中选择c进行更新 |
A4 |
6 |
从t中选择b进行更新 |
在上表中,可以通过”等待查询”或”阻塞查询”列。如你所见:
会话B (trx id
A4
,线程6
)和会话C (trx idA5
,线程7
)都在等待会话A (trx idA3
,线程5
).会话C在等待会话A的同时也在等待会话B。
可以看到底层数据INFORMATION_SCHEMA
INNODB_TRX
表和性能方案data_locks
而且data_lock_waits
表。
的一些示例内容INNODB_TRX
表格
trx id | 硫氧还蛋白的状态 | 硫氧还蛋白开始 | TRX请求锁id | 硫氧还蛋白等开始 | 硫氧还蛋白重量 | mysql线程id | 硫氧还蛋白查询 |
---|---|---|---|---|---|---|---|
A3 |
运行 |
2008-01-15 16:44:54 |
零 |
零 |
2 |
5 |
选择睡眠(100) |
A4 |
锁等待 |
2008-01-15 16:45:09 |
A4:1:3:2 |
2008-01-15 16:45:09 |
2 |
6 |
从t中选择b进行更新 |
A5 |
锁等待 |
2008-01-15 16:45:14 |
A5:1:3:2 |
2008-01-15 16:45:14 |
2 |
7 |
从t中选择c进行更新 |
的一些示例内容data_locks
表格
锁id | 锁trx id | 锁定模式 | 锁的类型 | 锁模式 | 锁表 | 锁指数 | 锁定数据 |
---|---|---|---|---|---|---|---|
A3:1:3:2 |
A3 |
X |
记录 |
测验 |
t |
主要的 |
0 x0200 |
A4:1:3:2 |
A4 |
X |
记录 |
测验 |
t |
主要的 |
0 x0200 |
A5:1:3:2 |
A5 |
X |
记录 |
测验 |
t |
主要的 |
0 x0200 |
的一些示例内容data_lock_waits
表格
在识别阻塞事务时,如果发出查询的会话已经空闲,则为阻塞查询报告一个NULL值。在这种情况下,使用以下步骤来确定阻塞查询:
标识阻塞事务的进程列表ID。在
sys.innodb_lock_waits
表中,阻塞事务的processlist ID为blocking_pid
价值。使用
blocking_pid
,查询“MySQL性能架构”线程
表来确定THREAD_ID
阻塞事务的。例如,如果blocking_pid
是6,发出这个查询:SELECT THREAD_ID FROM performance_schema线程WHERE PROCESSLIST_ID = 6;
使用
THREAD_ID
,查询“性能架构”events_statements_current
表确定线程执行的最后一个查询。例如,如果THREAD_ID
是28,发出这个查询:SELECT THREAD_ID, SQL_TEXT FROM performance_schema。events_statements_current WHERE THREAD_ID = 28\G
如果线程执行的最后一个查询没有足够的信息来确定锁被持有的原因,那么可以查询Performance Schema
events_statements_history
表查看线程执行的最近10条语句。SELECT THREAD_ID, SQL_TEXT FROM performance_schema。events_statements_historyWHERE THREAD_ID = 28 ORDER BY EVENT_ID;
有时,内部关联是有用的InnoDB
锁定信息和MySQL维护的会话级信息。例如,你可能想知道,对于一个给定的InnoDB
事务ID,对应的MySQL会话ID和可能持有锁的会话的名称,从而阻塞其他事务。
的以下输出INFORMATION_SCHEMA
INNODB_TRX
表和性能方案data_locks
而且data_lock_waits
表是从一个有些加载的系统中取出的。可以看到,有几个事务正在运行。
以下data_locks
而且data_lock_waits
表显示:
中显示的查询之间可能存在不一致INFORMATION_SCHEMA
PROCESSLIST
而且INNODB_TRX
表。有关解释,请参见第15.15.2.3节“InnoDB事务和锁定信息的持久性和一致性”.
的内容如下表所示PROCESSLIST
表为系统运行重工作负载.
ID | 用户 | 宿主 | DB | 命令 | 时间 | 状态 | 信息 |
---|---|---|---|---|---|---|---|
384 |
根 |
本地主机 |
测验 |
查询 |
10 |
更新 |
INSERT INTO t2 VALUES… |
257 |
根 |
本地主机 |
测验 |
查询 |
3. |
更新 |
INSERT INTO t2 VALUES… |
130 |
根 |
本地主机 |
测验 |
查询 |
0 |
更新 |
INSERT INTO t2 VALUES… |
61 |
根 |
本地主机 |
测验 |
查询 |
1 |
更新 |
INSERT INTO t2 VALUES… |
8 |
根 |
本地主机 |
测验 |
查询 |
1 |
更新 |
INSERT INTO t2 VALUES… |
4 |
根 |
本地主机 |
测验 |
查询 |
0 |
准备 |
从processlist中选择* |
2 |
根 |
本地主机 |
测验 |
睡眠 |
566 |
|
零 |
的内容如下表所示INNODB_TRX
表为系统运行重工作负载.
trx id | 硫氧还蛋白的状态 | 硫氧还蛋白开始 | TRX请求锁id | 硫氧还蛋白等开始 | 硫氧还蛋白重量 | mysql线程id | 硫氧还蛋白查询 |
---|---|---|---|---|---|---|---|
华氏77度 |
锁等待 |
2008-01-15 13:10:16 |
华氏77度 |
2008-01-15 13:10:16 |
1 |
876 |
INSERT INTO t09 (D, B, C) VALUES… |
77 e |
锁等待 |
2008-01-15 13:10:16 |
77 e |
2008-01-15 13:10:16 |
1 |
875 |
INSERT INTO t09 (D, B, C) VALUES… |
77 d |
锁等待 |
2008-01-15 13:10:16 |
77 d |
2008-01-15 13:10:16 |
1 |
874 |
INSERT INTO t09 (D, B, C) VALUES… |
77 b |
锁等待 |
2008-01-15 13:10:16 |
77 b: 733:12:1 |
2008-01-15 13:10:16 |
4 |
873 |
INSERT INTO t09 (D, B, C) VALUES… |
77年,一个 |
运行 |
2008-01-15 13:10:16 |
零 |
零 |
4 |
872 |
SELECT b, c FROM… |
E56 |
锁等待 |
2008-01-15 13:10:06 |
E56:743:6:2 |
2008-01-15 13:10:06 |
5 |
384 |
INSERT INTO t2 VALUES… |
E55 |
锁等待 |
2008-01-15 13:10:06 |
E55:743:38:2 |
2008-01-15 13:10:13 |
965 |
257 |
INSERT INTO t2 VALUES… |
19摄氏度 |
运行 |
2008-01-15 13:09:10 |
零 |
零 |
2900 |
130 |
INSERT INTO t2 VALUES… |
E15 |
运行 |
2008-01-15 13:08:59 |
零 |
零 |
5395 |
61 |
INSERT INTO t2 VALUES… |
51 d |
运行 |
2008-01-15 13:08:47 |
零 |
零 |
9807 |
8 |
INSERT INTO t2 VALUES… |
的内容如下表所示data_lock_waits
表为系统运行重工作负载.
请求trx id | 请求锁的id | 阻塞trx id | 阻塞锁id |
---|---|---|---|
华氏77度 |
77 f: 806 |
77 e |
77 e: 806 |
华氏77度 |
77 f: 806 |
77 d |
77 d: 806 |
华氏77度 |
77 f: 806 |
77 b |
77 b: 806 |
77 e |
77 e: 806 |
77 d |
77 d: 806 |
77 e |
77 e: 806 |
77 b |
77 b: 806 |
77 d |
77 d: 806 |
77 b |
77 b: 806 |
77 b |
77 b: 733:12:1 |
77年,一个 |
77年:733:12:1 |
E56 |
E56:743:6:2 |
E55 |
E55:743:6:2 |
E55 |
E55:743:38:2 |
19摄氏度 |
19 c: 743:38:2 |
的内容如下表所示data_locks
表为系统运行重工作负载.
锁id | 锁trx id | 锁定模式 | 锁的类型 | 锁模式 | 锁表 | 锁指数 | 锁定数据 |
---|---|---|---|---|---|---|---|
77 f: 806 |
华氏77度 |
AUTO_INC |
表格 |
测验 |
t09 |
零 |
零 |
77 e: 806 |
77 e |
AUTO_INC |
表格 |
测验 |
t09 |
零 |
零 |
77 d: 806 |
77 d |
AUTO_INC |
表格 |
测验 |
t09 |
零 |
零 |
77 b: 806 |
77 b |
AUTO_INC |
表格 |
测验 |
t09 |
零 |
零 |
77 b: 733:12:1 |
77 b |
X |
记录 |
测验 |
t09 |
主要的 |
上确界pseudo-record |
77年:733:12:1 |
77年,一个 |
X |
记录 |
测验 |
t09 |
主要的 |
上确界pseudo-record |
E56:743:6:2 |
E56 |
年代 |
记录 |
测验 |
t2 |
主要的 |
0,0 |
E55:743:6:2 |
E55 |
X |
记录 |
测验 |
t2 |
主要的 |
0,0 |
E55:743:38:2 |
E55 |
年代 |
记录 |
测验 |
t2 |
主要的 |
1922年,1922年 |
19 c: 743:38:2 |
19摄氏度 |
X |
记录 |
测验 |
t2 |
主要的 |
1922年,1922年 |