SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time, r.trx_query AS waiting_query, l.lock_table AS waiting_table_lock, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, SUBSTRING(p.host,1,INSTR(p.host, ':') -1 ) AS blocking_host, SUBSTRING(p.host, INSTR(p.host, ':') +1 ) AS block_port, IF(p.command="Sleep",p.time,0) AS idle_in_trx, b.trx_query AS blcoking_query from information_schema.innodb_lock_waits AS w INNER JOIN information_schema.innodb_trx AS b ON b.trx_id=w.blocking_trx_id INNER JOIN information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id INNER JOIN information_schema.innodb_locks AS l ON w.requested_lock_id = l.lock_id LEFT JOIN information_schema.processlist AS p ON p.id = b.trx_mysql_thread_id ORDER BY wait_time DESC;
SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ',p.host) AS who_blocks, IF (p.command = "Sleep",p.time, 0) AS idle_in_trx, MAX(TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())) AS max_wait_time, COUNT(*) AS num_waiters FROM information_schema.innodb_lock_waits as w inner join information_schema.innodb_trx as b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id left join information_schema.processlist as p on p.id = b.trx_mysql_thread_id group by who_blocks order by num_waiters desc;
select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,(select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p where timestampdiff(second, i.trx_started, now()) > p.time and i.trx_mysql_thread_id not in (connection_id(),p.id);
如果上面两个检查没有发现，或者事务过多，建议使用下面的查询将相关库上的会话终止
-- RDS for MySQL 5.6
select concat('kill ', a.owner_thread_id, ';') from information_schema.metadata_locks a left join (select b.owner_thread_id from information_schema.metadata_locks b, information_schema.metadata_locks c where b.owner_thread_id = c.owner_thread_id and b.lock_status = 'granted' and c.lock_status = 'pending') d ON a.owner_thread_id = d.owner_thread_id
where a.lock_status = 'granted' and d.owner_thread_id is null;
select concat('kill ', p1.id, ';') from information_schema.processlist p1,(select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2
where p1.time >= p2.time and p1.command in ('Sleep' , 'Query') and p1.id not in (connection_id() , p2.id); -- RDS for MySQL 5.5 语句请根据具体的 DDL 语句情况修改查询的条件； -- 如果导致阻塞的语句的用户与当前用户不同，请使用导致阻塞的语句的用户登录来终止会话
FROM information_schema.innodb_lock_waits t1,information_schema.innodb_trx t2,information_schema.innodb_trx t3 WHERE t1.blocking_trx_id = t2.trx_id AND t1.requesting_trx_id = t3.trx_id;
