MySQL Transaction--使用SHOW INNODB STATUS 查看未提交事务

当MySQL服务器出现性能问题时，应该优先排查未提交事务，除可以查询相关系统表外，还可以观察SHOW INNODB STATUS的输出结果来确认未提交事务。

首先查看InnoDB事务的History list length，如果该值就大，则很有可能存在未提交事务：

------------
TRANSACTIONS
------------
Trx id counter 38369270947
Purge done for trx's n:o < 38369109483 undo n:o < 0 state: running but idle
History list length 57841
LIST OF TRANSACTIONS FOR EACH SESSION:

对于未开始的事务，其描述为：

---TRANSACTION 421545153693904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

对于正在运行SQL的事务，其描述为：

---TRANSACTION 38369244845, ACTIVE 23 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 356639411, OS thread handle 139819271595776, query id 932203139853 xxx.xxx.xxx.xxx xxx_rw1 updating
update IB_XXX_M
SET XXX = 2,
UPDATE_TIME = now(),
BEGIN_TIME = '2019-08-26 17:00:00',
END_TIME = '2019-08-26 17:30:00' 
WHERE C1 = '103734548'
and  C2=1
and yn=0
Trx read view will not see trx with id >= 38369244848, sees < 38369109472

 

对于处于锁等待的事务，其描述为：

------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2501 page no 6079 n bits 552 index IDX_xxx of table `db_xxx`.`tb_xxxx` trx id 38369244845 lock_mode X locks rec but not gap waiting
Record lock, heap no 448 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 9; hex 313033373334353438; asc 103734548;;
 1: len 8; hex 800000000003ef8f; asc         ;;

 

对于长期未提交事务，其描述为：

---TRANSACTION 38369109472, ACTIVE 134 sec
59 lock struct(s), heap size 8400, 675 row lock(s), undo log entries 99
MySQL thread id 356638438, OS thread handle 139822406596352, query id 932203795801 172.28.232.111 wms_rw1
Trx read view will not see trx with id >= 38369109479, sees < 38369109434