mysql查询死锁信息

 

INNODB_LOCKS表

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

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

 

INNODB_TRX 表

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

 

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_isolation_level	隔离级别
trx_unique_checks	唯一键检测 是否开启
trx_foreign_key_checks	外键检测 是否开启

 

 

 

3、INNODB_LOCK_WAITS表

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

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

 

4、查询死锁详情

SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,
TIMESTAMPADD(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) wait_time,
r.trx_query waiting_query,
l.lock_table waiting_table_lock,
b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,
SUBSTRING(p.`HOST`,1,INSTR(p.`HOST`,':')-1) blocking_host,
SUBSTRING(p.`HOST`,INSTR(p.`HOST`,':')+1) blocking_port,
IF(p.COMMAND ='Sleep',p.TIME,0) idle_in_trx,
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_lock_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.INNODB_LOCKS l ON l.lock_id = w.requested_lock_id
LEFT JOIN information_schema.`PROCESSLIST` p ON p.id = b.trx_mysql_thread_id

ORDER BY wait_time desc;

 

5、有多少查询被哪些线程阻塞

SELECT CONCAT('thread',b.trx_mysql_thread_id,' from ',p.`HOST`) who_blocks,

IF(p.COMMAND ='Sleep',p.TIME,0) idle_in_trx,
MAX(TIMESTAMPADD(SECOND,r.trx_wait_started,NOW())) max_wait_time,
COUNT(1) num_waiters
FROM information_schema.INNODB_LOCK_WAITS w 
INNER JOIN information_schema.INNODB_TRX b on b.trx_id = w.blocking_lock_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
LEFT JOIN information_schema.`PROCESSLIST` p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks ORDER BY num_waiters desc;