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_idInnoDB存储引擎内部唯一的事务IDtrx_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_idMysql中的线程ID，show processlist显示的结果trx_query事务运行的sql语句trx_operation_state事务当操作的类型 如updating or deleting，starting index read等trx_tables_in_use查询用到的表的数量trx_tables_locked查询加行锁的表的数量trx_lock_structsThe number of locks reserved by the transactiontrx_lock_memory_bytes锁在内存占用的空间大小trx_rows_locked事务锁住的行数（不是准确数字）trx_rows_modified事务插入或者修改的行数trx_concurrency_ticketsA 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_errorDetailed error message for last FK error, or NULL.trx_adaptive_hash_latchedWhether 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_timeoutWhether 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锁IDlock_trx_id事务ID, 可以连INNODB_TRX表查事务详情lock_mode锁的模式： S, X, IS, IX, S_GAP, X_GAP, IS_GAP, IX_GAP, or AUTO_INClock_type锁的类型，行级锁 或者表级锁lock_table加锁的表lock_index如果是lock_type='RECORD' 行级锁 ,为锁住的索引，如果是表锁为nulllock_space如果是lock_type='RECORD' 行级锁 ,为锁住对象的Tablespace ID，如果是表锁为nulllock_page如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为nulllock_rec如果是lock_type='RECORD' 行级锁 ,为锁住页号，如果是表锁为nulllock_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申请锁资源的事务IDrequesting_lock_id申请的锁的IDblocking_trx_id租塞的事务IDblocking_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;