mysql 锁情况分析思想

最近出现了两次死锁的情况

1.查看innodb的情况
show engine innodb status
查看事物相关的部分

2.show full processlist 
想要更好的筛选信息可以查询系统表PROCESSLIST，主要关注
select ID,COMMAND,TIME,STATE,INFO from PROCESSLIST;

找出一些进程的所有ID,拼凑成一个字符串
select DB,GROUP_CONCAT(ID) from PROCESSLIST  where (需要的查询条件)\G
然后kill 掉相关的线程

select Id,user,command,time,state,info from information_schema.processlist where user='3jianhao' and db='OSS' order by time desc limit 100;

select concat('KILL ',id,';') from information_schema.processlist where user='3jianhao' and db='OSS'  ----查看
select concat('KILL ',id,';') from information_schema.processlist where user='3jianhao' and db='OSS' into outfile '/tmp/killall.txt';

source /tmp/killall.txt;

3.查看锁的相关信息
主要关注三个表
innodb_lock_waits 
innodb_trx 
innodb_locks

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;