MySQL SHOW ENGINE INNODB STATUS 结果解释解读(事务部分)
学习笔记 + 分享
我们都知道,通过show engine innodb status\G
这条命令可以查看当前事务内锁的状态。
但是呈现出来的参数是什么意思?本文将简单介绍,如果错误,欢迎广大网友指出。
如果作者本人不太清楚的地方也做了标记,表示是从网络上查看别人的文章截取的,针对这一部分内容大家最好自己核对一下。
版本 & 参数
-
版本:mysql-5.7.31
-
参数:
innodb_status_output_locks=1
:开启此参数 show engine innodb stauts 中才能打印行锁信息(这里不是指死锁信息)transaction_isolation=REPEATABLE-READ
一、准备数据
mysql> desc lock_t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | | # 主键,聚簇索引列
| a | int(11) | YES | MUL | NULL | |
| b | int(11) | YES | | 12 | |
| c | int(11) | YES | | 9 | |
| d | varchar(10) | YES | | yjx | |
| e | int(11) | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
mysql> select * from lock_t2;
+----+------+------+------+------+------+
| id | a | b | c | d | e |
+----+------+------+------+------+------+
| 1 | 10 | 2 | 100 | a | 1 |
| 3 | 11 | 5 | 120 | b | 8 |
| 7 | 13 | 10 | 130 | c | 14 |
| 10 | 20 | 12 | 170 | d | 20 |
| 99 | 99 | 99 | 99 | z | 99 |
+----+------+------+------+------+------+
二、模拟上锁
我们执行以下语句,对id=1,3,7
行上锁(RR
隔离级别下,针对聚簇索引列的非等值查询会加next-key lock
)
-- 开启一个窗口A
begin;
select * from lock_t2 where id <=3 for update;
-- 执行到这即可
-- 执行这条命令会给id=1,id=3,id=7这条记录上锁
-- 开启一个新窗口B
show engine innodb status\G
三、查看事务部分的输出结果
其余结果不看,先笼统看,下面会有拆解解释
TRANSACTIONS
------------
Trx id counter 2062
Purge done for trx's n:o < 2054 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421184796888688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421184796887776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421184796886864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2061, ACTIVE 47 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 18, OS thread handle 139709516281600, query id 6850 localhost root
Trx read view will not see trx with id >= 2061, sees < 2061
TABLE LOCK table `lock_db`.`lock_t2` trx id 2061 lock mode IX
RECORD LOCKS space id 34 page no 3 n bits 72 index PRIMARY of table `lock_db`.`lock_t2` trx id 2061 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000804; asc ;;
2: len 7; hex 370000013d0896; asc 7 = ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000002; asc ;;
5: len 4; hex 80000064; asc d;;
6: len 1; hex 61; asc a;;
7: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000000804; asc ;;
2: len 7; hex 370000013d08b7; asc 7 = ;;
3: len 4; hex 8000000b; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000078; asc x;;
6: len 1; hex 62; asc b;;
7: len 4; hex 80000008; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000000000804; asc ;;
2: len 7; hex 370000013d08d8; asc 7 = ;;
3: len 4; hex 8000000d; asc ;;
4: len 4; hex 8000000a; asc ;;
5: len 4; hex 80000082; asc ;;
6: len 1; hex 63; asc c;;
7: len 4; hex 8000000e; asc ;;
从上往下依次拆解解释
TRANSACTIONS
------------
Trx id counter 2062
# 事务ID计数器,每新增一个事务,就+1
Purge done for trx's n:o < 2054 undo n:o < 0 state: running but idle
# 前半句表示id小于2054的事务已经没有UNDO了
# 后半句表示进程正在使用的撤销日志编号(这句话网上截取的,暂时不太很明白),为0 0时说明清理进程处于空闲状态。
History list length 0
# 记录了当前undo space中未清理掉的事务个数
LIST OF TRANSACTIONS FOR EACH SESSION:
# 翻译:每个会话的事务状态,会话的个数与show full processlist;命令结果连接数相同
---TRANSACTION 421184796888688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421184796887776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421184796886864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
# 上方这三个会话没有发起影响事务的语句,休息中(not started)
---TRANSACTION 2061, ACTIVE 47 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
# 上方这一个会话就是我们执行select * from lock_t2 where id <=3 for update;的会话
# 2 lock struct(s):涉及两把锁
# heap size 1136:内存结构体占用的堆内存大小(这句话网上截取的)
# 3 row lock(s):锁住了几行记录,这里锁住了3行记录
重要部分
---TRANSACTION 2061, ACTIVE 47 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 18, OS thread handle 139709516281600, query id 6850 localhost root
MySQL thread id 18
:线程id号,这个id与show full processlist
结果中显示的id相同OS thread handle 139709516281600
:暂不明query id 6850
:localhost root
:
Trx read view will not see trx with id >= 2061, sees < 2061
- 暂不明,但看英文大概能猜到?
TABLE LOCK table `lock_db`.`lock_t2` trx id 2061 lock mode IX
- 表示有锁的表是哪一张,此处有锁的表是
lock_db
.lock_t2
,锁类型是IX
,有一个意向锁
RECORD LOCKS space id 34 page no 3 n bits 72 index PRIMARY of table `lock_db`.`lock_t2` trx id 2061 lock_mode X
-
Record Locks
:表示行锁 -
space id 34
:所对应的表空间ID是多少,34对应的是lock_db
.lock_t2
,id
与表的对应关系可以通过查询information_schema.innodb_sys_datafiles
得到。 -
page no 3
:page号 -
bits 72
:暂不明 -
index PRIMARY of table lock_db.lock_t2
:表示锁住的是lock_db.lock_t2
上的主键索引 -
trx id 2061
:事务id=2061
-
lock_mode X
:表示锁类型是Next-key lock
(也就是行锁+间隙锁)
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000000804; asc ;;
2: len 7; hex 370000013d0896; asc 7 = ;;
3: len 4; hex 8000000a; asc ;;
4: len 4; hex 80000002; asc ;;
5: len 4; hex 80000064; asc d;;
6: len 1; hex 61; asc a;;
7: len 4; hex 80000001; asc ;;
# 对应的记录其实就是
+----+------+------+------+------+------+
| id | a | b | c | d | e |
+----+------+------+------+------+------+
| 1 | 10 | 2 | 100 | a | 1 |
+----+------+------+------+------+------+
-
Record lock
:表示上锁的是一行记录 -
heap no 2 PHYSICAL RECORD: n_fields 8
: 表示锁住记录的heap no 为2的物理记录,8应该指的是下面有8行信息(百度复制) -
compact format
: 表示这条记录存储的格式 -
info bits : 0
:表示这条记录没有被删除; 非0 -- 表示被修改或者被删除 -
0: len 4; hex 80000001; asc ;;
:聚簇索引列的值,16进制,最后01表示表中的id
列的值=1 -
1: len 6; hex 000000000804; asc ;;
:上次修改这条数据的事务ID -
2: len 7; hex 370000013d0896; asc 7 = ;;
:undo回滚段的指针 -
3: len 4; hex 8000000a; asc ;;
:排除聚簇索引列后,从左往右数的第1列的值,针对id=1
这一行记录,也就是a
列=10
-
4: len 4; hex 80000002; asc ;;
:排除聚簇索引列后,从左往右数的第2列的值,针对id=1
这一行记录,也就是b
列=2
-
5: len 4; hex 80000064; asc d;;
:排除聚簇索引列后,从左往右数的第3列的值,针对id=1
这一行记录,也就是c
列=100(十六进制的100=64)
-
6: len 1; hex 61; asc a;;
:排除聚簇索引列后,从左往右数的第4列的值,针对id=1
这一行记录,也就是d
列=a(十六进制的a=0x61)
-
7: len 4; hex 80000001; asc ;;
:排除聚簇索引列后,从左往右数的第5列的值,针对id=1
这一行记录,也就是e
列=1
剩下的3行大家可以自行验证,只是笔记分享,有不正确的地方欢迎大家指出!