Mysql - InnoDB 锁等待，死锁的排查语句

导航

• 1.SHOW OPEN TABLES where In_use
• 2.SHOW FULL PROCESSLIST
• 3.SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
• 4. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
• 5.SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS
• 6.show engine innodb status

---

1. SHOW OPEN TABLES where In_use > 0

这条语句主要用于查看表的锁竞争情况，where 条件限制了只有存在锁竞争才会显示数据。
显示字段如下：

• Database
  数据库名称

• Table
  表名

• In_use
  表锁会使这个值+1，每个请求行锁而阻塞的线程也会使这个值+1

• Name_locked
  表名是否被锁定。名称锁定用于诸如删除或重命名表的操作

2. SHOW FULL PROCESSLIST

这条语句显示当前正在运行的线程连接，如果你的权限足够，那么你可以看到全部的用户的线程，否则只能看到你当前用户的线程。
显示字段如下：

• Id
  线程id，可以使用 kill id 来杀死这条线程。

• Host
  创建连接的主机地址

• db
  使用的数据库

• Command
  线程正在执行的命令的类型。
  具体信息查看 Mysql官网Commom描述

• Time
  线程处于当前状态的时间（以秒未单位）

• State
  线程正在执行的操作，事件或者状态
  大多数状态对应于非常快速的操作。如果线程在给定状态下停留许多秒钟，则可能存在需要调查的问题。
  具体信息查看Mysql官网State描述

• Info
  线程当前正在执行的语句

3.SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

这条语句显示InnoDb中正在开启的非只读事务的相关信息。
这条语句我们重点关注它是否正在等待锁，以及事务的开始时间。
显示字段如下：

• TRX_ID
  事务Id

• TRX_WEIGHT
  事务的权重，反映（未必是准确的）已更改的行和该事务锁定的行数。出现死锁时，InnoDb通常会回滚权重较小的事务。

• TRX_STATE
  事务执行状态。允许值是 RUNNING，LOCK WAIT， ROLLING BACK，和 COMMITTING。

• TRX_STARTED
  事务的开始时间

• TRX_REQUESTED_LOCK_ID
  事务当前正在等待的锁的ID

• TRX_WAIT_STARTED
  事务等待锁的时间

• TRX_MYSQL_THREAD_ID
  Mysql的线程id

• TRX_QUERY
  事务正在执行的SQL语句

• TRX_OPERATION_STATE
  事务当前的操作状态

• TRX_TABLES_IN_USE
  事务使用到的表的数量

• TRX_TABLES_LOCKED
  事务从多少个表中获取了锁

• TRX_LOCK_STRUCTS
  事务保留的锁数。

• TRX_LOCK_MEMORY_BYTES
  内存中此事务的锁结构占用的总大小。

• TRX_ROWS_LOCKED
  此事务的锁定行数

• TRX_ROWS_MODIFIED
  此事务中已修改和已插入的行数。

• TRX_CONCURRENCY_TICKETS
  由innodb_concurrency_tickets 系统变量指定的值，该值指示当前事务在被换出之前可以完成多少工作

• TRX_ISOLATION_LEVEL
  当前事务的隔离级别。

• TRX_UNIQUE_CHECKS
  当前事务是打开还是关闭唯一检查。例如，它们可能在批量数据加载期间关闭。

• TRX_FOREIGN_KEY_CHECKS
  当前事务是打开还是关闭唯一检查。例如，它们可能在批量数据加载期间关闭。

• TRX_LAST_FOREIGN_KEY_ERROR
  最后一个外键错误的详细错误消息（如果有）；否则NULL。

• TRX_ADAPTIVE_HASH_LATCHED
  自适应哈希索引是否被当前事务锁定。（一次仅一个事务可以修改自适应哈希索引。）

• TRX_ADAPTIVE_HASH_TIMEOUT
  是立即放弃搜索锁存器以获取自适应哈希索引，还是在MySQL的调用之间保留它。当没有自适应哈希索引争用时，此值保持为零，并且语句保留闩锁，直到它们完成为止。在争用期间，它递减计数为零，并且语句在每次查询行后立即释放锁存器。

• TRX_IS_READ_ONLY
  值为1表示事务是只读的。

• TRX_AUTOCOMMIT_NON_LOCKING
  值为1表示事务是 SELECT不使用 FOR UPDATE 或者LOCK IN SHARED MODE子句的语句，并且正在autocommit启用状态下执行， 因此事务将仅包含此一条语句。当此列和TRX_IS_READ_ONLY均为1时，InnoDB优化事务以减少与更改表数据的事务相关的开销。

4. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

这条语句显示 InnoDB 中产生锁等待的事务信息。
一个锁等待至少会产生两行的数据，分别是等待锁的事务，以及持有锁的事务。
显示字段如下：

• LOCK_ID
  内部唯一的锁ID号

• LOCK_TRX_ID
  请求或者持有锁的事务的ID

• LOCK_MODE
  请求或者持有的锁。允许值S，X， IS，IX， GAP，AUTO_INC，和 UNKNOWN

• LOCK_TYPE
  锁的类型。允许的值 RECORD用于行级锁定， TABLE表级锁定

• LOCK_TABLE
  已锁定或包含锁定记录的表的名称

• LOCK_INDEX
  索引的名称，如果LOCK_TYPE 为 RECORD 则显示；否则NULL

• LOCK_SPACE
  锁定记录的表空间ID，如果LOCK_TYPE 为 RECORD 则显示；否则NULL

• LOCK_PAGE
  锁定记录的页码，如果LOCK_TYPE 为 RECORD 则显示；否则NULL

• LOCK_REC
  页面内锁定记录的堆号，如果LOCK_TYPE 为 RECORD 则显示；否则NULL

• LOCK_DATA
  与锁关联的数据（如果有）。如果LOCK_TYPE为RECORD，则显示一个值，否则为NULL。
  如果锁定的是唯一索引，则显示的是二级索引的值。
  如果显示的是二级索引，则显示二级索引值并附加主键值。

5.SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

这条语句显示锁等待的事务的对应信息。
显示字段如下：

• REQUESTING_TRX_ID
  请求锁的事务id

• REQUESTED_LOCK_ID
  请求的锁id

• BLOCKING_TRX_ID
  持有锁的事务id

• BLOCKING_LOCK_ID
  持有的锁id

6.SHOW ENGINE INNODB STATUS

这条语句显示的是Innodb过去一段时间的运行日志（这里是每一段时间刷新一次，因此未必是最新的）。
排除死锁的时候，我们重点关注 LATEST DETECTED DEADLOCK 这部分内容就好，这部分内容记录的是最近一次死锁的信息。

这部分的内容解析如下，如果需要更详细的 innodb status 内容的解析，可以查看这篇博客

------------------------
LATEST DETECTED DEADLOCK
------------------------
16