查看DDL操作对应的状态
show processlist;
select * from information_schema.innodb_trx;
information_schema.innodb_trx.trx_mysql_thread_id是线程id,再结合information_schema.processlist进一步缩小范围,定位出执行时间早于DDL的事务,生成kill会话语句
SELECT concat('kill ', i.trx_mysql_thread_id, ';') FROM information_schema.innodb_trx i, ( SELECT MAX(time) AS max_time FROM information_schema.processlist WHERE state = 'Waiting for table metadata lock' AND (info LIKE 'alter%' OR info LIKE 'create%' OR info LIKE 'drop%' OR info LIKE 'truncate%' OR info LIKE 'rename%' )) p WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;
select * from sys.schema_table_lock_waits;
重点关注列:blocking_lock_type
定位出需要Kill的会话。
SELECT sql_kill_blocking_connection FROM sys.schema_table_lock_waits WHERE blocking_lock_type <> 'SHARED_UPGRADABLE' AND waiting_query = 'alter table xxx';
不过,sys.schema_table_lock_waits表是MySQL 5.7引入的,会显示MDL的相关信息,包括作用对象、锁的类型及锁的状态等。必须首先开启MDL相关的instrument。但在MySQL 5.7中,默认关闭;MySQL 8.0才默认开启。 查看是否开启:
mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl';
临时开启:
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
永久生效:
[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
https://mp.weixin.qq.com/s/0KbenLqQKHR5Aj2kFS_Dcg