Description:
MySQL 5.7.3 introduced support for monitoring metadata locks through the metadata_locks table in the performance_schema database.
However by default it is disabled.
Depending on whether there is any significant performance impact, the metadata lock instrumentation should be enabled by default as it is too late to enable it, once you have a lock issue (as only new locks/request for locks are instrumented after enabling the instrument).
How to repeat:
1) Start MySQL 5.7.3
2) In one connection cause a metadata lock:
connection 1> LOCK TABLES world.City WRITE;
3) Enable MDL instrumentation:
connection 2> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
4) In a third connection try to SELECT from the world.City table:
connection 3> SELECT * FROM world.City WHERE ID = 130;
(this will block)
5) See the processlist:
connection 2> SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_COMMAND, PROCESSLIST_STATE, PROCESSLIST_INFO FROM threads WHERE PROCESSLIST_ID <> CONNECTION_ID()\G
*************************** 1. row ***************************
THREAD_ID: 20
PROCESSLIST_ID: 1
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_STATE: Waiting on empty queue
PROCESSLIST_INFO: NULL
*************************** 2. row ***************************
THREAD_ID: 22
PROCESSLIST_ID: 3
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
*************************** 3. row ***************************
THREAD_ID: 23
PROCESSLIST_ID: 4
PROCESSLIST_COMMAND: Query
PROCESSLIST_STATE: Waiting for table metadata lock
PROCESSLIST_INFO: SELECT * FROM world.City WHERE ID = 130
3 rows in set (0.01 sec)
6) Check the metadata_locks table:
connection 2> SELECT * FROM metadata_locks\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
OBJECT_NAME: City
OBJECT_INSTANCE_BEGIN: 194627536
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:5542
OWNER_THREAD_ID: 23
OWNER_EVENT_ID: 3
*************************** 2. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: performance_schema
OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 194628976
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5542
OWNER_THREAD_ID: 21
OWNER_EVENT_ID: 87
2 rows in set (0.00 sec)
So the metadata lock held by thread_id = 22 is not included.
Suggested fix:
Set the wait/lock/metadata/sql/mdl instrument to enabled by default.
Description: MySQL 5.7.3 introduced support for monitoring metadata locks through the metadata_locks table in the performance_schema database. However by default it is disabled. Depending on whether there is any significant performance impact, the metadata lock instrumentation should be enabled by default as it is too late to enable it, once you have a lock issue (as only new locks/request for locks are instrumented after enabling the instrument). How to repeat: 1) Start MySQL 5.7.3 2) In one connection cause a metadata lock: connection 1> LOCK TABLES world.City WRITE; 3) Enable MDL instrumentation: connection 2> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; 4) In a third connection try to SELECT from the world.City table: connection 3> SELECT * FROM world.City WHERE ID = 130; (this will block) 5) See the processlist: connection 2> SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_COMMAND, PROCESSLIST_STATE, PROCESSLIST_INFO FROM threads WHERE PROCESSLIST_ID <> CONNECTION_ID()\G *************************** 1. row *************************** THREAD_ID: 20 PROCESSLIST_ID: 1 PROCESSLIST_COMMAND: Sleep PROCESSLIST_STATE: Waiting on empty queue PROCESSLIST_INFO: NULL *************************** 2. row *************************** THREAD_ID: 22 PROCESSLIST_ID: 3 PROCESSLIST_COMMAND: Sleep PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL *************************** 3. row *************************** THREAD_ID: 23 PROCESSLIST_ID: 4 PROCESSLIST_COMMAND: Query PROCESSLIST_STATE: Waiting for table metadata lock PROCESSLIST_INFO: SELECT * FROM world.City WHERE ID = 130 3 rows in set (0.01 sec) 6) Check the metadata_locks table: connection 2> SELECT * FROM metadata_locks\G *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: world OBJECT_NAME: City OBJECT_INSTANCE_BEGIN: 194627536 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:5542 OWNER_THREAD_ID: 23 OWNER_EVENT_ID: 3 *************************** 2. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: performance_schema OBJECT_NAME: metadata_locks OBJECT_INSTANCE_BEGIN: 194628976 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5542 OWNER_THREAD_ID: 21 OWNER_EVENT_ID: 87 2 rows in set (0.00 sec) So the metadata lock held by thread_id = 22 is not included. Suggested fix: Set the wait/lock/metadata/sql/mdl instrument to enabled by default.