mysql数据库死锁排查相关sql

show  processlist;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKs;
SELECT * FROM information_schema.INNODB_LOCK_waits;
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
show global variables like 'autocommit';
show engine innodb status;
SHOW OPEN TABLES;
show variables like 'tx_isolation';

truncate table dba_ecis_space.ecis_tcis_circle_directory; 
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
SET GLOBAL innodb_lock_wait_timeout=30;

select  a.id,a.user,a.host,b.trx_started,b.trx_query from information_schema.processlist a right outer join information_schema.INNODB_TRX b on a.ID =b.trx_mysql_thread_id ;
select 
    a.trx_id 事务id ,
    a.trx_mysql_thread_id 事务线程id,
    a.trx_query 事务sql 
from 
    INFORMATION_SCHEMA.INNODB_LOCKS b,
    INFORMATION_SCHEMA.innodb_trx a 
where 
    b.lock_trx_id=a.trx_id;