Who Holds the Metadata Lock? MySQL 5.7.3 Brings Help

In MySQL 5.5 metadata locks were introduced to ensure consistency for some operations that previously could cause problems. For example if a transaction SELECTed from a table and another connection changed the structure of the table before the transaction was committed; in that case the two operations would be written in reverse order to the binary log which could prevent replaying the binary log.

However there was no way to see who held metadata locks. SHOW PROCESSLIST would show who were waiting for metadata locks, and often you could guess who held it, but it wasn't so always. Consider for example the following:

mysql> SELECT Id, db, Command, Time, State, Info FROM information_schema.PROCESSLIST;
+------+--------------------+---------+------+---------------------------------+-------------------------------------------------------------------------------+
| Id   | db                 | Command | Time | State                           | Info                                                                          |
+------+--------------------+---------+------+---------------------------------+-------------------------------------------------------------------------------+
|    1 | NULL               | Daemon  |    8 | Waiting for next activation     | NULL                                                                          |
|    2 | performance_schema | Query   |    0 | executing                       | SELECT Id, db, Command, Time, State, Info FROM information_schema.PROCESSLIST |
|    3 | world              | Sleep   |   39 |                                 | NULL                                                                          |
|    4 | world              | Query   |   35 | Waiting for table metadata lock | ALTER TABLE City COMMENT='Cities'                                             |
| 1111 | world              | Sleep   |   32 |                                 | NULL                                                                          |
| 1108 | world              | Sleep   |   47 |                                 | NULL                                                                          |
| 1113 | world              | Sleep   |   18 |                                 | NULL                                                                          |
| 1112 | world              | Connect |   23 | Waiting for table metadata lock | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| 1109 | world              | Sleep   |   44 |                                 | NULL                                                                          |
| 1114 | world              | Connect |    8 | Waiting for table metadata lock | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
+------+--------------------+---------+------+---------------------------------+-------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

In this case it happens to be the process with Id = 3 that holds the metadata lock, but that is not obvious from the above output.

In MySQL 5.7.3 this has changed. There is a new Performance Schema table metadata_locks which will tell which metadata locks are held and which are pending. This can be used to answer the question for a case like the one above.

First instrumentation of metadata locks should be enabled. To do that you must enable the wait/lock/metadata/sql/mdl instrument in setup_instruments. Additionally the global_instrumentation consumer must be enabled in setup_consumers.

Currently the wait/lock/metadata/sql/mdl instrument is not enabled by default. I have created a feature request to consider enabling it by default, but obviously whether that will happen also depends on the performance impact. Update: The instrument is enabled by default in MySQL 8.0.

To enable metadata lock instrumentation:

mysql> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE 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

Information

The global_instrumentation consumer is enabled by default.

With the metadata lock instrumentation enabled, it is now easy to answer who holds the metadata lock (I've excluded the connections own metalocks here as I'm only interested in the metadata lock contention going on between other queries):

mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,
    ->        THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO
    ->   FROM performance_schema.metadata_locks
    ->        INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
    ->  WHERE PROCESSLIST_ID <> CONNECTION_ID();
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE           | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO                                                              |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------+
| TABLE       | world         | City        | SHARED_READ         | GRANTED     |        22 |              3 | NULL                                                                          |
| GLOBAL      | NULL          | NULL        | INTENTION_EXCLUSIVE | GRANTED     |        23 |              4 | ALTER TABLE City COMMENT='Cities'                                             |
| SCHEMA      | world         | NULL        | INTENTION_EXCLUSIVE | GRANTED     |        23 |              4 | ALTER TABLE City COMMENT='Cities'                                             |
| TABLE       | world         | City        | SHARED_UPGRADABLE   | GRANTED     |        23 |              4 | ALTER TABLE City COMMENT='Cities'                                             |
| TABLE       | world         | City        | EXCLUSIVE           | PENDING     |        23 |              4 | ALTER TABLE City COMMENT='Cities'                                             |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1185 |           1166 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1185 |           1166 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1186 |           1167 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1186 |           1167 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1187 |           1168 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1187 |           1168 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1188 |           1169 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1188 |           1169 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1189 |           1170 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1189 |           1170 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1190 |           1171 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1190 |           1171 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | view_city   | SHARED_READ         | GRANTED     |      1191 |           1172 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
| TABLE       | world         | City        | SHARED_READ         | PENDING     |      1191 |           1172 | SELECT COUNT(*) INTO @count FROM view_city WHERE CountryCode = in_CountryCode |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------+
19 rows in set (0.00 sec)

So in this case there is a metadata lock GRANTED to process list id 3 whereas the other connections have a PENDING lock status for the metadata lock for the City table.

I have worked with MySQL databases since 2006 both as an SQL developer, a database administrator, and for more than eight years as part of the Oracle MySQL Support team. I have spoken at MySQL Connect and Oracle OpenWorld on several occasions. I have contributed to the sys schema and four Oracle Certified Professional (OCP) exams for MySQL 5.6 to 8.0. I have written four books, all published at Apress.

1 Comment on “Who Holds the Metadata Lock? MySQL 5.7.3 Brings Help

Leave a Reply

Your email address will not be published.

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.