mysql死锁查看

查看事务等待状况

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;

查看更具体的事务等待状况

select
  b.trx_state,
  e.state,
  e.time,
  d.state as block_state,
  d.time as block_time,
  a.requesting_trx_id,
  a.requested_lock_id,
  b.trx_query,
  b.trx_mysql_thread_id,
  a.blocking_trx_id,
  a.blocking_lock_id,
  c.trx_query as block_trx_query,
  c.trx_mysql_thread_id as block_trx_mysql_tread_id
from
  information_schema.innodb_lock_waits a
left join information_schema.innodb_trx b on a.requesting_trx_id = b.trx_id
left join information_schema.innodb_trx c on a.blocking_trx_id = c.trx_id
left join information_schema.processlist d on c.trx_mysql_thread_id = d.id
left join information_schema.processlist e on b.trx_mysql_thread_id = e.id
order by
  a.requesting_trx_id;

查看未关闭的事务

mysql 5.5/mysql5.7

    select  
     a.trx_id,  
     a.trx_state,  
     a.trx_started,  
     a.trx_query,  
     b.id,  
     b. user,  
     b. host,  
     b.db,  
     b.command,  
     b.time,  
     b.state,  
     b.info  
     from  
          information_schema.innodb_trx a  
     left join information_schema.processlist b on      a.trx_mysql_thread_id = b.id  
     where  
          b.command = 'sleep'; 

mysql 5.6

select  
     a.trx_id,  
     a.trx_state,  
     a.trx_started,  
     a.trx_query,  
     b.id,  
     b.user,  
     b.db,  
     b.command,  
     b.time,  
     b.state,  
     b.info,  
     c.processlist_user,  
     c.processlist_host,  
     c.processlist_db,  
     d.sql_text  
     from  
          information_schema.innodb_trx a  
     left join information_schema.processlist b on      a.trx_mysql_thread_id = b.id  
     and b.command = 'sleep'  
     left join performance_schema.threads c on b.id =      c.processlist_id  
     left join performance_schema.events_statements_current d on d.thread_id = c.thread_id; 

未关闭事务信息

select
t1.trx_id,
t1.trx_started,
t1.trx_mysql_thread_id,
t3.event_id,
t3.end_event_id,
t3.sql_text,
concat('mysql --login-path=3306 -e ''kill ',t1.trx_mysql_thread_id,'''')
from 
	information_schema.innodb_trx t1 
left join `performance_schema`.threads t2
on t1.trx_mysql_thread_id=t2.processlist_id
left join `performance_schema`.events_statements_history t3
on t2.thread_id=t3.thread_id
where
	t1.trx_started < date_sub(now(), interval 1 minute)
and t1.trx_operation_state is null
and t1.trx_query is null
order by event_id desc 

查看某段时间以来未关闭事务

select
trx_id,
trx_started,
trx_mysql_thread_id
from
	information_schema.innodb_trx
where
	trx_started < date_sub(now(), interval 1     minute)
and trx_operation_state is null
and trx_query is null;

最后一次执行的sql语句，可以用于查询未结束的事务

select
	*
from
	`performance_schema`.events_statements_current
where
	thread_id in (
		select
			thread_id
		from
			`performance_schema`.threads
		where
			processlist_id = 108
	)

查看历史会话

select
	*
from
	`performance_schema`.events_statements_history
where
	thread_id in (
		select
			thread_id
		from
			`performance_schema`.threads
		where
			processlist_id = 84
	)

查看哪些用户占用了大量的资源

    mysql> select * from user_summary\g  
    *************************** 1. row ***************************  
                      user: root  
                statements: 3331        //用户执行的sql语句总数量  
         statement_latency: 2.86 s        //该用户执行sql总的等待时间，即累计的sql执行时间  
     statement_avg_latency: 857.30 us    //单次sql的平均执行时间  
               table_scans: 67    //全表扫描次数  
                  file_ios: 23626    //io次数  
           file_io_latency: 1.72 s    //io消耗的总时间  
       current_connections: 1    //该用户的当前连接会话数  
         total_connections: 9    //该用户连接会话总数  
              unique_hosts: 1    //用户发起的连接中唯一主机的数量，即从几个主机发起过数据库连接  
            current_memory: 0 bytes    //当前被分配的内存  
    total_memory_allocated: 0 bytes    //总共被分配的内存  
    *************************** 2. row ***************************  
                      user: background  
                statements: 0  
         statement_latency: 0 ps  
     statement_avg_latency: 0 ps  
               table_scans: 0  
                  file_ios: 2127  
           file_io_latency: 161.74 ms  
       current_connections: 26  
         total_connections: 30  
              unique_hosts: 0  
            current_memory: 0 bytes  
    total_memory_allocated: 0 bytes  
    2 rows in set (0.02 sec)  
        通过上述查询，可以简单看到每个用户占用的连接、内存、io等资源  
    如果想要进一步查询某项指标的话，可以查看对应的视图  
    查看哪些用户占用的详细指标  
    mysql> show tables like 'user%';  
    +-----------------------------------+  
    | tables_in_sys (user%)             |  
    +-----------------------------------+  
    | user_summary                      |  
    | user_summary_by_file_io           |  
    | user_summary_by_file_io_type      |  
    | user_summary_by_stages            |  
    | user_summary_by_statement_latency |  
    | user_summary_by_statement_type    
    mysql> select * from user_summary_by_statement_latency\g  
      这个视图概述了每个用户执行sql的大体统计信息，本人简单分析如下：  
    user    用户名  
    total    该用户总共执行的sql语句的数量  
    total_latency    执行sql总共的等待时间  
    max_latency    单次执行的最长等待时间  
    lock_latency    锁等待累计时间  
    rows_sent    该用户执行sql累积返回的行数  
    rows_examined    未执行用户sql存储引擎读取的总行数  
    rows_affected    被用户sql语句影响的总行数  
    full_scans    该用户执行的sql中造成全表扫描的总次数  
    如果想了解各个用户占用的io带宽，可查询如下视图  
    mysql> select * from user_summary_by_file_io\g  
    *************************** 1. row ***************************  
          user: root  
           ios: 23655  
    io_latency: 1.72 s  
    *************************** 2. row ***************************  
          user: background  
           ios: 2144  
    io_latency: 162.43 ms  
    2 rows in set (0.01 sec)  
      这个视图相对比较简单，列举的是每个用户下的io事件次数，以及io等待的累计时间  
    除此之外，我们还可以根据waits_by_user_by_latency视图查看每个用户的等待事件  
    mysql> desc waits_by_user_by_latency;  
      
    通过sys schema查询innodb的锁信息  
    innodb_lock_waits  
        mysql 5.7.7开始，sys schema中提供了innodb_lock_waits视图，通过这个视图，我们可以更直观清晰地查看当前事务间的阻塞关系。在此列举几个受关注的列，如下：  
    waiting_trx_id    等待事务的id  
    waiting_trx_age    等待的时间  
    waiting_query    等待锁资源的sql  
    waiting_lock_mode    请求锁的模式   
    blocking_trx_id    阻塞事务的id  
    blocking_trx_age    阻塞事务被执行的时间  
    blocking_query    阻塞事务正在执行的sql  
    blocking_lock_mode    阻塞的锁模式  
    locked_table    被加锁的表  
    locked_index    被加锁的索引  
      
    schema_table_lock_waits   
      5.7.9中新增的视图，通过这个视图可以查询阻塞会话的metadata lock信息  

解除正在死锁的状态有两种方法：

第一种：  
1.查询是否锁表  
show open tables where in_use > 0;  
2.查询进程（如果您有super权限，您可以看到所有线程。否则，您只能看到您自己的线程）  
        show processlist  
3.杀死进程id（就是上面命令的id列）  
kill id  
  
第二种：  
1.查看下在锁的事务   
select * from information_schema.innodb_trx;  
2.杀死进程id（就是上面命令的trx_mysql_thread_id列）  
kill 线程id  
例子：  
查出死锁进程：show processlist  
杀掉进程          kill 420821;  
其它关于查看死锁的命令：  
1：查看当前的事务  
select * from information_schema.innodb_trx;  
2：查看当前锁定的事务  
select * from information_schema.innodb_locks;  
3：查看当前等锁的事务  
select * from information_schema.innodb_lock_waits;