「xifan」mysql查询死锁信息

xifan

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的事务的锁等待的状态

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;