请选择 进入手机版 | 继续访问电脑版
查看: 60|回复: 0

[资料分享] 基于show engine innodb status查看锁信息方法解析

[复制链接]

签到天数: 193 天

[LV.7]化身百千

发表于 2019-3-12 22:32:08 | 显示全部楼层 |阅读模式
本帖最后由 微风→寒 于 2019-3-12 22:46 编辑

1.通过show engine innodb status来查看,其中的transactions片段可以看到事务,其中包括锁等待。

以下是没有激活任何事务的信息:

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 2856
Purge done for trx's n   o < 2856 undo n   o < 0 state: running
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421383739060216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421383739059200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421383739057168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
三个"---TRANSACTION"表示当前开启了3个mysql会话,但这3个会话都没有任何事务。

以下是某会话开启一个事务,但没有任何锁等待的事务信息:

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 2857
Purge done for trx's   < 2856 und    < 0 state: running
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421383739060216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421383739057168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2856, ACTIVE 10 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 39, OS thread handle 139909209945856, query id 1814112 localhost root Reset for next command
不难看出,这个事务是一个需要写日志的DML事务。

以下是有锁等待的事务信息:

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 14915
Purge done for trx's    < 14912 undo n    o < 0 state: running but idle
History list length 896
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 14909, not started
MySQL thread id 36, OS thread handle 0x7f5d57e4b700, query id 961 localhost root init
show engine innodb status
---TRANSACTION 14914, ACTIVE 465 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 34, OS thread handle 0x7f5d57e8c700, query id 959 localhost root updating
update tt set b= 'x' where a=1
------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 184 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`tt` trx id 14914 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 6; hex 000000000601; asc       ;;
1: len 6; hex 000000003a41; asc     :A;;
2: len 7; hex 2f000001580feb; asc /   X  ;;
3: len 4; hex 80000001; asc     ;;
4: len 5; hex 6820202020; asc h    ;;

------------------
---TRANSACTION 14913, ACTIVE 490 sec
2 lock struct(s), heap size 360, 6 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f5d57f4f700, query id 900 localhost root
从上面的结果可以看到锁等待的信息。

"TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED"表示该事务申请锁已经等待了13秒。

"RECORD LOCKS space id 184 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`tt` trx id 14914 lock_mode X waiting"表示test.tt表上的记录要申请的行锁(recode lock)是独占锁并且正在waiting,并且标明了该行记录所在表数据文件中的物理位置:表空间id为184,页码为3。

关于这些信息的详细解释,后文会逐渐说明。


您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /2 下一条

返回顶部