MySQL——SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS介绍

SHOW ENGINE INNODB STATUS是SHOW STATUS的一种特殊形式，它能够显示InnoDB Monitor的输出。

InnoDB Monitor提供了关于InnoDB内部状态的信息，对于我们诊断InnoDB的问题有很大帮助。

SHOW ENGINE INNODB STATUS显示的内容有：

• STATUS：输出时间戳、InnoDB Monitor的名字、秒数，或显示InnoDB Monitor到当前时间经过了多少时间。
• BACKGROUND THREAD：srv_master_thread展示了后台主线程所执行的工作。
• SEMAPHORES：正在等待信号量的线程和该线程在互斥信号量或读写锁信号量上需要旋转和等待的次数。如果这个线程数量过大了，可能是由于存在I/O或争用的问题，可以尝试减小系统变量innodb_thread_concurrency。Spin rounds per wait展示了每个操作系统等待互斥信号量的自旋锁轮数。
• LATEST FOREIGN KEY ERROR：显示外键错误，输出发生错误的语句、相关的外键和表的信息。
• LATEST DETECTED DEADLOCK：显示死锁信息。输出死锁涉及到的事务、正在执行、保持和要求锁定的语句，以及回滚的事务。
• TRANSACTIONS：帮助确定锁争用以及造成死锁的原因。
• FILE I/O：InnoDB的线程信息以及挂起的IO操作和IO性能统计信息。
• INSERT BUFFER AND ADAPTIVE HASH INDEX：InnoDB insert buffer和Adaptive hash index的状态信息包括每种操作的执行次数以及Adaptive hash index的性能信息。
• LOG：InnoDB的日志信息，包括当前日志的序列号和刷新到磁盘上的日志的距离、上一次InnoDB建立checkpoint的位置、挂起的写操作以及写操作的性能统计信息。
• BUFFER POOL AND MEMORY：缓存池页面的读写信息，可以查看查询所执行的I/O操作的次数。
• ROW OPERATIONS：主线程相关的信息，包括各种类型的行操作的数量和性能水平。

死锁分析

接下来我们尝试模拟出一个死锁场景并使用SHOW ENGINE INNODB STATUS进行分析。

首先建立表customer：

CREATE TABLE customer (
    rec_id INT(11),
    customer_name VARCHAR(144),
    customer_nickname VARCHAR(144),
    PRIMARY KEY (rec_id),
    KEY IX_customer_name (customer_name)
) ENGINE=INNODB CHARSET=utf8;

插入一些数据：

开启两个会话，执行以下两个事务：

显然，以上两个事务中，事务1持有rec_id=1,请求rec_id=2，事务2持有rec_id=2,请求rec_id=1，形成了死锁，我们尝试执行一下SHOW ENGINE INNODB STATUS，以下是SHOW ENGINE INNODB STATUS中LATEST DETECTED DEADLOCK的输出：

输出信息很清晰地给出了死锁发生的时间和涉及的两个事务在当时的状态：

事务（1）正在等待执行

select * from customer where customer.rec_id=2 for update;

事务（2）正在等待执行

select * from customer where customer.rec_id=1 for update;

事务下面的输出给出来的是未获取到锁的记录的位置，最后一句，WE ROLL BACK TRANSACTION(2)表明，为解决死锁，InnoDB将第二个事务进行了回滚。于是我们就利用SHOW ENGINE INNODB STATUS还原出了死锁发生的原因。