排查

查询是否锁表

show open tables where in_use > 0;  

查询进程

show processlist;

查询再锁的事务

select * from information_schema.innodb_locks;

查询等待锁的事务

select * from information_schema.innodb_lock_waits;

杀掉进程

kill {进程号}

查询死锁详情

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;