有时识别哪个事务阻塞另一个事务是有帮助的。包含有关
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
, thread6
) 和会话 C (trx idA5
, thread7
) 都在等待会话 A (trx idA3
, thread5
)。会话 C 正在等待会话 B 和会话 A。
您可以在表
INNODB_TRX
、
INNODB_LOCKS
和
中查看基础数据INNODB_LOCK_WAITS
。
下表显示了一些示例内容
INFORMATION_SCHEMA.INNODB_TRX
。
交易编号 | 发送状态 | trx 开始 | trx 请求的锁 ID | trx 等待开始 | 卡车重量 | trx mysql线程id | 交易查询 |
---|---|---|---|---|---|---|---|
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 |
下表显示了一些示例内容
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
。
InnoDB
有时将内部锁定信息与 MySQL 维护的会话级信息
关联起来很有用
。例如,对于给定的InnoDB
事务 ID,您可能想知道相应的 MySQL 会话 ID 和可能持有锁并因此阻塞其他事务的会话的名称。
表中的以下输出
INFORMATION_SCHEMA
取自负载稍大的系统。可以看出,有几个事务在运行。
下表INNODB_LOCKS
和
INNODB_LOCK_WAITS
表格表明:
INFORMATION_SCHEMA
PROCESSLIST
和
INNODB_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 |
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 … |
下表显示了
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 |