【环境介绍】

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//

delimiter ;

查看信息PID会话59堵塞PID60操作，PID60正是使用OSC进行添加字段的操作

 

 

查看PID59操作信息：

查看线程为85，且查看到SQL语句为查询操作

注意：(这里信息并不一定可靠，因为该表中对于每个线程只能记录当前正在执行和最近一次执行完成的语句事件信息，一旦这个线程执行新的语句，信息就会被覆盖)

 

 

【处理方法】

确认好信息，kill堵塞的会话：

 

 查看OSC执行操作正常：

 

 

【后续维护】
在业务低峰期执行相关场景操作，例如创建索引、删除索引等。
开启事务自动提交autocommit，事物及时提交，减少长事务。
设置参数lock_wait_timeout为较小值。