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 SELECT
ed 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
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 really like the full lock type names 🙂