MYSQL查询数据库死锁和事务的语句

查看进程信息

SHOW FULL PROCESSLIST ;

查询事务信息

SELECT * FROM information_schema.INNODB_TRX t -- WHERE t.trx_query LIKE '%select%';

查询表打开情况

SHOW OPEN TABLES WHERE In_use > 0;

查询锁情况列表

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查询锁等待信息,blocking_lock_id是当前事务在等待的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS w;

查询死锁日志

SHOW ENGINE INNODB STATUS;

查询锁等待时间

SHOW STATUS LIKE '%lock%';

查询源头锁

SELECT DISTINCT b.trx_id blocking_trx_id,
                     b.trx_mysql_thread_id 源头锁thread_id,
                     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) idel_in_trx,
                     b.trx_query blocking_query,
                     r.trx_id waiting_trx_id,
                     r.trx_mysql_thread_id waiting_thread,
                     TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) wait_time,
                     r.trx_query waiting_query,
                     l.lock_table waiting_table_lock
       FROM information_schema.INNODB_LOCKS l
       LEFT JOIN information_schema.INNODB_LOCK_WAITS w
         ON w.requested_lock_id = l.lock_id
       LEFT JOIN information_schema.INNODB_TRX b
         ON b.trx_id = w.blocking_trx_id
       LEFT 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
     /*JOIN (SELECT blocking_trx_id -- 查找最源头的trx_id
           FROM information_schema.INNODB_LOCK_WAITS ilw
          WHERE blocking_trx_id NOT IN
                (SELECT requesting_trx_id
                   FROM information_schema.INNODB_LOCK_WAITS)) c
     ON c.blocking_trx_id = b.trx_id */
     ORDER BY wait_time DESC;

批量生产删除数据库外键的SQL

SELECT CONCAT('alter table ',table_name,' DROP FOREIGN KEY ',constraint_name,';')
FROM information_schema.table_constraints
WHERE constraint_type='FOREIGN KEY'
AND table_schema='dawanka';

查询数据库所有约束类型信息

SELECT * FROM information_schema.key_column_usage;
或者
SELECT * FROM information_schema.table_constraints;