Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Who Holds the Metadata Lock? MySQL 5.7.3 Brings Help
+3 Vote Up -0 Vote Down

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.

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

Note: 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.

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.