MySql 的事务和死锁查询

-- 查看那些表锁到了
SHOW OPEN TABLES WHERE In_use > 0;
-- 查看进程号
select * FROM INFORMATION_SCHEMA.`PROCESSLIST`;
-- 查询 正在执行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查询死锁日志
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 = '表模式名'
and CONSTRAINT_NAME like '外键名模糊搜索'
and TABLE_NAME = '表名';
-- 查询数据库所有约束类型信息
SELECT * FROM information_schema.key_column_usage;
SELECT * FROM information_schema.table_constraints;