pt-online-schema-change添加字段Waiting for table metadata lock等待事件处理

【环境介绍】

CentOS Linux release 7.7 + 5.7.26-log MySQL Community Server (GPL) 实际环境腾讯云MySQL+5.7.18-txsql-log

【背景描述】

使用工单系统对数据库进行表结构变更操作，调用OSC，出现Waiting for table metadata lock等待事件，无法正常执行

【测试】

持有MDL锁（Waiting for table metadata lock 未提交）

创建表：

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL,
`x` char(3) DEFAULT NULL,
`c` char(32) DEFAULT 'dummy_test',
PRIMARY KEY (`id`),
KEY `x` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

insert into test values(1,'tu1');

insert into test values(2,'tu2');

会话1：

开启会话查询事物，不提交操作

会话2：

OSC进行添加字段操作

查看信息：

会话出现Waiting for table metadata lock

定位持有Waiting for table metadata lock锁的信息

前提：需要设置：performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'或者UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

参考：https://www.percona.com/blog/2016/12/28/quickly-troubleshooting-metadata-locks-mysql-5-7/使用存储过程定位

USE test;
DROP PROCEDURE IF EXISTS procShowMetadataLockSummary;
delimiter //
CREATE PROCEDURE procShowMetadataLockSummary()
BEGIN
DECLARE table_schema VARCHAR(64);
DECLARE table_name VARCHAR(64);
DECLARE id bigint;
DECLARE time bigint;
DECLARE info longtext;
DECLARE curMdlCount INT DEFAULT 0;
DECLARE curMdlCtr INT DEFAULT 0;
DECLARE curMdl CURSOR FOR SELECT * FROM tmp_blocked_metadata;
DROP TEMPORARY TABLE IF EXISTS tmp_blocked_metadata;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_blocked_metadata (
table_schema varchar(64),
table_name varchar(64),
id bigint,
time bigint,
info longtext,
PRIMARY KEY(table_schema, table_name)
);
REPLACE tmp_blocked_metadata(table_schema,table_name,id,time,info) SELECT mdl.OBJECT_SCHEMA, mdl.OBJECT_NAME, t.PROCESSLIST_ID, t.PROCESSLIST_TIME, t.PROCESSLIST_INFO FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON mdl.OWNER_THREAD_ID = t.THREAD_ID WHERE mdl.LOCK_STATUS='PENDING' and mdl.LOCK_TYPE='EXCLUSIVE' ORDER BY mdl.OBJECT_SCHEMA,mdl.OBJECT_NAME,t.PROCESSLIST_TIME ASC;
OPEN curMdl;
SET curMdlCount = (SELECT FOUND_ROWS());
WHILE (curMdlCtr < curMdlCount)
DO
FETCH curMdl INTO table_schema, table_name, id, time, info;
SELECT CONCAT_WS(' ','PID',t.PROCESSLIST_ID,'has metadata lock on', CONCAT(mdl.OBJECT_SCHEMA,'.',mdl.OBJECT_NAME), 'with current state', CONCAT_WS('','[',t.PROCESSLIST_STATE,']'), 'for', t.PROCESSLIST_TIME, 'seconds and is currently running', CONCAT_WS('',"[",t.PROCESSLIST_INFO,"]")) AS 'Process(es) that have the metadata lock' FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID WHERE mdl.LOCK_STATUS='GRANTED' AND mdl.OBJECT_SCHEMA = table_schema and mdl.OBJECT_NAME = table_name AND mdl.OWNER_THREAD_ID NOT IN(SELECT mdl2.OWNER_THREAD_ID FROM performance_schema.metadata_locks mdl2 WHERE mdl2.LOCK_STATUS='PENDING' AND mdl.OBJECT_SCHEMA = mdl2.OBJECT_SCHEMA and mdl.OBJECT_NAME = mdl2.OBJECT_NAME);
SELECT CONCAT_WS(' ','PID', id, 'has been waiting for metadata lock on',CONCAT(table_schema,'.', table_name),'for', time, 'seconds to execute', CONCAT_WS('','[',info,']')) AS 'Oldest process waiting for metadata lock';
SET curMdlCtr = curMdlCtr + 1;
SELECT CONCAT_WS(' ','PID', t.PROCESSLIST_ID, 'has been waiting for metadata lock on',CONCAT(table_schema,'.', table_name),'for', t.PROCESSLIST_TIME, 'seconds to execute', CONCAT_WS('','[',t.PROCESSLIST_INFO,']')) AS 'Other queries waiting for metadata lock' FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID WHERE mdl.LOCK_STATUS='PENDING' AND mdl.OBJECT_SCHEMA = table_schema and mdl.OBJECT_NAME = table_name AND mdl.OWNER_THREAD_ID AND t.PROCESSLIST_ID <> id ;
END WHILE;
CLOSE curMdl;
END//