When I execute the following command.Performance_schema.metadata_locks should have content, but nothing. update performance_schema.setup_instruments set enabled = 'yes', timed = 'no' where name = 'wait/lock/metadata/sql/mdl';
Details are as follows test@(none) 09:42:06>update performance_schema.setup_instruments set enabled = 'yes', timed = 'no' -> where name = 'wait/lock/metadata/sql/mdl'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0
test@(none) 09:42:33>select * from performance_schema.setup_instruments where name like 'wait/lock/metadata/sql/mdl'; ------------------------------------+------+
NAME
ENABLED
TIMED
------------------------------------+------+
wait/lock/metadata/sql/mdl
YES
NO
------------------------------------+------+ 1 row in set (0.00 sec)
test@(none) 09:42:33> test@(none) 09:42:34>select * from performance_schema.metadata_locks; Empty set (0.00 sec)
As I understand it, as soon as you turn on 'wait/lock/metadata/sql/mdl', you can see something in performance_schema.metadata_locks
Environment
CentOS release 6.6 (Final) 2.6.32-504.el6.x86_64
Smart Checklist
Activity
Show:
Sveta Smirnova April 6, 2018 at 5:07 PM
You must enable metadata lock instrumentation before examined thread acquires the lock. Also, if there is no metadata lock acquired you will see nothing in performance_schema.metadata_locks.
I am using the mysql version as follows
Server version: 5.7.19-17-log Percona Server (GPL), Release 17, Revision e19a6b7b73f
However, I can not view the thread holding the MDL lock.
I refer to the following article
https://www.percona.com/blog/2016/12/28/quickly-troubleshooting-metadata-locks-mysql-5-7/
When I execute the following command.Performance_schema.metadata_locks should have content, but nothing.
update performance_schema.setup_instruments set enabled = 'yes', timed = 'no' where name = 'wait/lock/metadata/sql/mdl';
Details are as follows
test@(none) 09:42:06>update performance_schema.setup_instruments set enabled = 'yes', timed = 'no'
-> where name = 'wait/lock/metadata/sql/mdl';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
test@(none) 09:42:33>select * from performance_schema.setup_instruments where name like 'wait/lock/metadata/sql/mdl';
------------------------------------+------+
NAME
ENABLED
TIMED
------------------------------------+------+
wait/lock/metadata/sql/mdl
YES
NO
------------------------------------+------+
1 row in set (0.00 sec)
test@(none) 09:42:33>
test@(none) 09:42:34>select * from performance_schema.metadata_locks;
Empty set (0.00 sec)
As I understand it, as soon as you turn on 'wait/lock/metadata/sql/mdl', you can see something in performance_schema.metadata_locks