information_schema中Innodb相关表用于分析sql查询锁的使用情况介绍

MySQL中information_schema 简略的介绍了Mysql中元数据信息库的各个表的作用，从这篇wiki中可以大致了解各个表的作用。这里主要介绍下Innodb事务锁相关的三个表：INNODB_TRX表、INNODB_LOCKS表、INNODB_LOCK_WAITS表。通过查看这三个表可以事务加锁的情况以及事务锁等待的情况，从而可以更简单地监控当前事务并分析可能存在的锁问题，例如分析死锁。

下面先分别介绍下三个表的作用以及各个字段的意义。

1、INNODB_TRX 表

The INNODB_TRX table contains information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the SQL statement the transaction is executing, if any.

INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务

INNODB_TRX表的各个字段

Column name

Description

trx_id	InnoDB存储引擎内部唯一的事务ID
trx_state	当前事务的状态： RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
trx_started	事务的开始时间
trx_requested_lock_id	事务等待的锁的ID（如果事务状态不是LOCK WAIT，这个字段是NULL），详细的锁的信息可以连查INNODB_LOCKS表
trx_wait_started	事务等待开始的时间 （如果事务状态不是LOCK WAIT，这个字段是NULL）
trx_weight	事务的权重，反映了一个事务修改和锁住的行数。当发生死锁回滚的时候，优先选择该值最小的进行回滚
trx_mysql_thread_id	Mysql中的线程ID，show processlist显示的结果
trx_query	事务运行的sql语句
trx_operation_state	事务当操作的类型 如updating or deleting，starting index read等
trx_tables_in_use	查询用到的表的数量
trx_tables_locked	查询加行锁的表的数量
trx_lock_structs	The number of locks reserved by the transaction
trx_lock_memory_bytes	锁在内存占用的空间大小
trx_rows_locked	事务锁住的行数（不是准确数字）
trx_rows_modified	事务插入或者修改的行数
trx_concurrency_tickets	A value indicating how much work the current transaction can do before being swapped out, as specified by the innodb_concurrency_ticketsoption.
trx_isolation_level	隔离级别
trx_unique_checks	唯一键检测 是否开启
trx_foreign_key_checks	外键检测 是否开启
trx_last_foreign_key_error	Detailed error message for last FK error, or NULL.
trx_adaptive_hash_latched	Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.)
trx_adaptive_hash_timeout	Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup.

 

2、INNODB_LOCKS表

The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction.

INNODB_LOCKS表主要包含了InnoDB事务锁的具体情况，包括事务正在申请加的锁和事务加上的锁。

INNODB_LOCKS表的各个字段 

Column name

Description

lock_id	锁ID
lock_trx_id	事务ID, 可以连INNODB_TRX表查事务详情
lock_mode	锁的模式： S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INC
lock_type	锁的类型，行级锁 或者表级锁
lock_table	加锁的表
lock_index	如果是lock_type='RECORD' 行级锁 ,为锁住的索引，如果是表锁为null
lock_space	如果是lock_type='RECORD' 行级锁 ,为锁住对象的Tablespace ID，如果是表锁为null
lock_page	如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为null
lock_rec	如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为null
lock_data	事务锁住的主键值，若是表锁，则该值为null

3、INNODB_LOCK_WAITS表

The INNODB_LOCK_WAITS table contains one or more rows for each blocked InnoDB transaction, indicating the lock it has requested and any locks that are blocking that request.

INNODB_LOCK_WAITS表包含了blocked的事务的锁等待的状态

Column name

Description

requesting_trx_id	申请锁资源的事务ID
requesting_lock_id	申请的锁的ID
blocking_trx_id	租塞的事务ID
blocking_lock_id	租塞的锁的ID

 

insert into test(test_id) values(5);

 select * from test where test_id =5  for update;

 

select 

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

from information_schema.innodb_lock_waits w

inner join information_schema.innodb_trx b

on b.trx_id = w.blocking_trx_id

inner join information_schema.innodb_trx r

on r.trx_id = w.requesting_trx_id \G;