How To Troubleshoot Metadata Locks in MySQL 5.7

July 08, 2024

How To Troubleshoot Metadata Locks in MySQL 5.7

performance_schema.metadata_locks, which is available in MySQL 5.7 and exposes metadata lock details. Given this information, here’s a quick way to troubleshoot metadata locks by creating a stored procedure that can:

Find out which thread(s) have the metadata lock
Determine which thread has been waiting for it the longest
Find other threads waiting for the metadata lock

Setting up instrumentation

First, you need to enable instrumentation for metadata locks:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl';

+----------------------------+---------+-------+

| NAME | ENABLED | TIMED |

+----------------------------+---------+-------+

| wait/lock/metadata/sql/mdl | YES | NO |

+----------------------------+---------+-------+

1 row in set (0.01 sec)

Second, you need to add this stored procedure:

CREATE DEFINER=`username`@`%` PROCEDURE `sp_procShowMetadataLockSummary`()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

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)

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

Now, let’s call the procedure to see if there are threads waiting for metadata locks: