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
------------------------
160128  1:51:53  #这里显示了最近一次发生死锁的日期和时间
*** (1) TRANSACTION:
TRANSACTION D20847, ACTIVE 141 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 20027, OS thread handle 0x7f0a4c0f8700, query id 1818124 localhost root statistics
select * from test_deadlock where id=2 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20847 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 000000d20808; asc       ;;
 2: len 7; hex ad000001ab011d; asc        ;;
 3: len 4; hex 00000002; asc     ;;
 
*** (2) TRANSACTION:
TRANSACTION D20853, ACTIVE 119 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 20081, OS thread handle 0x7f0a0f020700, query id 1818204 localhost root statistics
select * from test_deadlock where id=1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20853 lock_mode X locks rec but not gap
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 000000d20808; asc       ;;
 2: len 7; hex ad000001ab011d; asc        ;;
 3: len 4; hex 00000002; asc     ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20853 lock_mode X locks rec but not gap waiting
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 000000d20808; asc       ;;
 2: len 7; hex ad000001ab0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;
 
*** WE ROLL BACK TRANSACTION (2)
 
这部分内容比较多，下面分段逐一进行解释：
5.2.1 下面这部分显示的是死锁的第一个事务的信息：
*** (1) TRANSACTION:
TRANSACTION D20847, ACTIVE 141 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 20027, OS thread handle 0x7f0a4c0f8700, query id 1818124 localhost root statistics
select * from test_deadlock where id=2 for update
TRANSACTION D20847, ACTIVE 141 sec starting index read：这行表示事务D20847，ACTIVE 141 sec表示事务处于活跃状态141s，starting index read表示正在使用索引读取数据行
mysql tables in use 1, locked 1#这行表示事务D20847正在使用1个表，且涉及锁的表有1个
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s) #这行表示在等待3把锁，占用内存376字节，涉及2行记录，如果事务已经锁定了几行数据，这里将会有一行信息显示出锁定结构的数目（注意，这跟行锁是两回事）和堆大小，堆的大小指的是为了持有这些行锁而占用的内存大小，Innodb是用一种特殊的位图表来实现行锁的，从理论上讲，它可将每一个锁定的行表示为一个比特，经测试显示，每个锁通常不超过4比特
MySQL thread id 20027, OS thread handle 0x7f0a4c0f8700, query id 1818124 localhost root statistics #这行表示该事务的线程ID信息，操作系统句柄信息，连接来源、用户
select * from test_deadlock where id=2 for update #这行表示事务涉及的SQL
 
5.2.2 下面这一部分显示的是当死锁发生时，第一个事务正在等待的锁等信息：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #这行信息表示第一个事务正在等待锁被授予
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20847 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 000000d20808; asc       ;;
 2: len 7; hex ad000001ab011d; asc        ;;
 3: len 4; hex 00000002; asc     ;;
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20847 lock_mode X locks rec but not gap waiting#这行信息表示等待的锁是一个record lock，空间id是441，页编号为3，大概位置在页的72位处，锁发生在表xiaoboluo.test_deadlock的主键上，是一个X锁，但是不是gap lock。 waiting表示正在等待锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 #这行表示record lock的heap no 位置
#这部分剩下的内容只对调试才有用。
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 000000d20808; asc       ;;
 2: len 7; hex ad000001ab011d; asc        ;;
 3: len 4; hex 00000002; asc     ;;
 
5.2.3 下面这部分是事务二的状态：
*** (2) TRANSACTION:
TRANSACTION D20853, ACTIVE 119 sec starting index read #事务2处于活跃状态119s
mysql tables in use 1, locked 1 #正在使用1个表，涉及锁的表有1个
3 lock struct(s), heap size 1248, 2 row lock(s) #涉及3把锁，2行记录
MySQL thread id 20081, OS thread handle 0x7f0a0f020700, query id 1818204 localhost root statistics
select * from test_deadlock where id=1 for update #第二个事务的SQL
5.2.4 下面这部分是事务二的持有锁信息：
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20853 lock_mode X locks rec but not gap
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 000000d20808; asc       ;;
 2: len 7; hex ad000001ab011d; asc        ;;
 3: len 4; hex 00000002; asc     ;;

RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20853 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 #从这两行持有锁信息计息后面几行调试信息上看，就是事务1正在等待的锁。
 
5.2.5 下面这部分是事务二正在等待的锁，从下面的信息上看，等待的是同一个表，同一个索引，同一个page上的record lock X锁，但是heap no位置不同，即不同的行上的锁：
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20853 lock_mode X locks rec but not gap waiting
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 000000d20808; asc       ;;
 2: len 7; hex ad000001ab0110; asc        ;;
 3: len 4; hex 00000001; asc     ;;
*** WE ROLL BACK TRANSACTION (2) #这个表示事务2被回滚，因为两个事务的回滚开销一样，所以选择了后提交的事务进行回滚，如果两个事务回滚的开销不同(undo 数量不同)，那么就回滚开销最小的那个事务。

本文参考：
MYSQL官方说明文档
小萝卜博客-mysql之show engine innodb status解读