mysql 定位问题常用sql

select *,now() from information_schema.INNODB_TRX; -- 查看当前正在被锁定的事务
select *,now() from information_schema.INNODB_LOCKS; -- 查看当前正在被锁定的行
select *,now() from information_schema.INNODB_LOCK_WAITS; -- 查看当前等待锁的事务
SHOW OPEN TABLES WHERE In_Use > 0; -- 查看当前正在被锁定的表

show PROCESSLIST 

select * from  information_schema.processlist
where 1=1
AND COMMAND != 'Sleep'   
-- and info LIKE '%t_biz_xxx%'
#AND time > 500 
ORDER BY Time desc;

select 10855/60 -- 18:43

-- 查看表被那些事务锁了（不确定）
SELECT 
    p.ID, 
    p.USER, 
    l.lock_table, 
    l.lock_index, 
    l.lock_type, 
    l.lock_mode 
FROM 
    INFORMATION_SCHEMA.INNODB_LOCKS l 
    JOIN 
 INFORMATION_SCHEMA.PROCESSLIST p ON l.lock_trx_id = p.ID 
WHERE 
    l.lock_table = 'pre_rallyrequirement_linkobjects';

-- INFORMATION_SCHEMA.PROCESSLIST id 关联		 information_schema.INNODB_TRX trx_mysql_thread_id
SELECT 
    p.ID, 
    p.USER, 
    l.* 
FROM 
    INFORMATION_SCHEMA.INNODB_TRX l 
    JOIN 
INFORMATION_SCHEMA.PROCESSLIST p ON l.trx_mysql_thread_id = p.ID 

-- kill 11256256

SHOW mysql.table WHERE id IN (SELECT user_id FROM mysql.user WHERE username = 'xxx');