show engine innodb status输出解读（一）

        MySQL的 show engine innodb status 能够显示InnoDB Monitor的输出，包括在途事务、锁、缓冲池和日志文件等。解析 show engine innodb status 的输出可以帮助数据库管理员（DBA）了解数据库的运行情况以及诊断数据库性能问题。

以下使用 mysql8.0.35 版本做解读。

innodb status 完整输出

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2024-02-20 16:05:35 139918630254336 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 387 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7
OS WAIT ARRAY INFO: signal count 7
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2024-02-20 16:02:29 139918630254336 Transaction:
TRANSACTION 2833, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 102, OS thread handle 139918630254336, query id 14 localhost root updating
delete from parent
Foreign key constraint fails for table `pigoss`.`child`:
,
  CONSTRAINT `i_child` FOREIGN KEY (`child_id`) REFERENCES `parent` (`parent_id`)
Trying to delete in parent table, in index PRIMARY tuple:
DATA TUPLE: 3 fields;
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000b11; asc       ;;
 2: len 7; hex 02000001060347; asc       G;;

But in child table `pigoss`.`child`, in index child_id, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000200; asc       ;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-02-20 16:05:27 139918639703808
*** (1) TRANSACTION:
TRANSACTION 2858, ACTIVE 27 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 102, OS thread handle 139918630254336, query id 36 localhost root statistics
select * from test_deadlock where id=2 for update

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `pigoss`.`test_deadlock` trx id 2858 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000000b25; asc      %;;
 2: len 7; hex 820000010c0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `pigoss`.`test_deadlock` trx id 2858 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 000000000b25; asc      %;;
 2: len 7; hex 820000010c011d; asc        ;;
 3: len 4; hex 00000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2859, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 149, OS thread handle 139918493710080, query id 37 localhost root statistics
select * from test_deadlock where id=1 for update

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5 page no 4 n bits 72 index PRIMARY of table `pigoss`.`test_deadlock`