Troubleshooting MySQL Metadata Lock
MySQL uses metadata locking to manage concurrent access to database objects and to ensure
data consistency. Table metadata lock is required while executing a table online DDL change.
Online DDL acquire metadata lock in three phase, Initialization phase require metadata
exclusive lock, in this phase any access on the table will block the DDL command and the
DDL command will waiting on ““Waiting for table metadata lock” state. Once the DDL
command acquire the metadata lock then will enter into phase two, execution phase. In this
phase, the metadata exclusive lock degrades to shared lock which will allow DML command
on the table. The new table data file will be created and table data migrated to the new data
file with new table definition. Once all the data migrated, the DDL change operation will
trying to upgrade the metadata shared lock to exclusive lock and swap the table data file. This
is the phase three, commit table definition phase.
During the phase one and phase three, if there is any other connection accessing the table, the
DDL command will be waiting on metadata lock to be granted. Any other subsequent access
on this table will waiting the metadata lock too. This is why customer might see large number
of “Waiting for table metadata lock” connections in the ‘SHOW PROCESSLIST” command
output.
In MySQL 5.7 and higher version, metadata_locks system table can be used to monitor and
find the blocking connections. The table need to be enabled prior to use it.
Enable the metadata lock table:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
Disable the metadata lock table:
UPDATE performance_schema.setup_instruments
SET ENABLED = 'NO', TIMED = 'NO'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
Test 1:
In Session A: start a transaction and run a select command on the table.
mysql> begin; select * from testdatetime;
Query OK, 0 rows affected (0.00 sec)
+----+------+---------------------+------+------+
| id | a | b | col1 | col2 |
+----+------+---------------------+------+------+
| 1 | 1 | 2023-09-20 00:00:00 | NULL | NULL |
| 2 | 1 | 0000-00-00 00:00:00 | NULL | NULL |
| 3 | 1 | 0000-00-00 00:00:00 | NULL | NULL |
+----+------+---------------------+------+------+
3 rows in set (0.00 sec)
In Session B: Run a DDL command on the same table
mysql> alter table testdatetime add col10 int;
--session is waiting
In Session C: run a select command on the table
mysql> begin; select * from testdatetime;
--session is waiting
In Session D:
mysql> show processlist;
+-----+-----------------+---------------------+--------+---------+-------
+---------------------------------+----------------------------------------
+
| Id | User | Host | db | Command | Time |
State | Info |
+-----+-----------------+---------------------+--------+---------+-------
+---------------------------------+---------------------------------------
|
| 193 | admin | 172.31.26.235:38308 | testdb | Query | 6 |
Waiting for table metadata lock | select * from testdatetime |
| 197 | admin | 172.31.26.235:55430 | testdb | Query | 0 |
init | show processlist |
| 300 | admin | 172.31.26.235:52348 | testdb | Sleep | 69 |
| NULL |
| 301 | admin | 172.31.26.235:55970 | testdb | Query | 47 |
Waiting for table metadata lock | alter table testdatetime add col10 int |
+-----+-----------------+---------------------+--------+---------+-------
+---------------------------------+----------------------------------------
From the output, the connection id 193 (select) and connection id 301 (alter table) both
waiting on the metadata lock. The connection id 300 is in sleep state. In a busy production
database, you might see hundreds or even thousands of sleep and metadata waiting
connections.
With metadata table enabled, you can use below query to find out which connection is
blocking the alter command
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 | testdb | testdatetime | SHARED_READ |
GRANTED | 340 | 300 | NULL
|
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE |
GRANTED | 341 | 301 | alter table testdatetime add
col10 int |
| BACKUP LOCK | NULL | NULL | INTENTION_EXCLUSIVE |
GRANTED | 341 | 301 | alter table testdatetime add
col10 int |
| SCHEMA | testdb | NULL | INTENTION_EXCLUSIVE |
GRANTED | 341 | 301 | alter table testdatetime add
col10 int |
| TABLE | testdb | testdatetime | SHARED_UPGRADABLE |
GRANTED | 341 | 301 | alter table testdatetime add
col10 int |
| TABLESPACE | NULL | testdb/testdatetime | INTENTION_EXCLUSIVE |
GRANTED | 341 | 301 | alter table testdatetime add
col10 int |
| TABLE | testdb | #sql-32b_12d | EXCLUSIVE |
GRANTED | 341 | 301 | alter table testdatetime add
col10 int |
| TABLE | testdb | testdatetime | EXCLUSIVE |
PENDING | 341 | 301 | alter table testdatetime add
col10 int |
| TABLE | testdb | testdatetime | SHARED_READ |
PENDING | 230 | 193 | select * from testdatetime
|
+-------------+---------------+---------------------+---------------------
+-------------+-----------+----------------+------------------------------
The connection id 193 and 301 is pending the EXCLUSIVE and SHARED_READ metalock
on table “testdatetime”, and the connection id 300 is granted the SHARED_READ lock on
table “testdatetime”. So we need to terminate the connection 300 to release the lock.
After the connection 300 be terminated, the metadata lock connection were gone.
mysql> CALL mysql.rds_kill(300);
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist;
+-----+-----------------+---------------------+--------+---------+-------
+------------------------+------------------+
| Id | User | Host | db | Command | Time |
State | Info |
+-----+-----------------+---------------------+--------+---------+-------
+------------------------+------------------+
| 193 | admin | 172.31.26.235:38308 | testdb | Sleep | 138 |
| NULL |
| 197 | admin | 172.31.26.235:55430 | testdb | Query | 0 |
init | show processlist |
| 301 | admin | 172.31.26.235:55970 | testdb | Sleep | 179 |
| NULL |
+-----+-----------------+---------------------+--------+---------+-------
+------------------------+------------------+
And in the above session B and C, the alter table and select command were completed with
the waiting for (2 min 35.33) sec and (1 min 54.56 sec) respectively.
mysql> alter table testdatetime add col10 int;
Query OK, 0 rows affected (2 min 35.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> begin; select * from testdatetime;
Query OK, 0 rows affected (0.00 sec)
+----+------+---------------------+------+------+-------+
| id | a | b | col1 | col2 | col10 |
+----+------+---------------------+------+------+-------+
| 1 | 1 | 2023-09-20 00:00:00 | NULL | NULL | NULL |
| 2 | 1 | 0000-00-00 00:00:00 | NULL | NULL | NULL |
| 3 | 1 | 0000-00-00 00:00:00 | NULL | NULL | NULL |
+----+------+---------------------+------+------+-------+
3 rows in set (1 min 54.56 sec)
If the metadata_lock table is not enabled on the instance, it will be difficult to pinpoint which
session is blocking. You will need to query current database transactions and running SQL
statements for any clue.
select * from information_schema.INNODB_TRX;
select * from performance_schema.events_statements_current
The metadata lock waiting timing is rely on parameter lock_wait_timeout setting, by default,
the waiting time is 31536000 seconds (365 days). please do not confused with parameter
innodb_lock_wait_timeout.
lock_wait_timeout, This timeout applies to all statements that use metadata
locks.
innodb_lock_wait_timeout, The length of time in seconds an InnoDB
transaction waits for a row lock before giving up. The default value is 50
seconds.
Test metadata lock timeout.
in connection A: start a transaction and query the table
mysql> begin; select * from testdatetime;
Query OK, 0 rows affected (0.00 sec)
+----+------+---------------------+------+------+-------+
| id | a | b | col1 | col2 | col10 |
+----+------+---------------------+------+------+-------+
| 1 | 1 | 2023-09-20 00:00:00 | NULL | NULL | NULL |
| 2 | 1 | 0000-00-00 00:00:00 | NULL | NULL | NULL |
in connection B: set lock_wait_timeout parameter and try to alter table.
mysql> set lock_wait_timeout = 30;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table testdatetime add col11 int;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
The alter table command timeout after 30 seconds.
In this article, we learned what is MySQL metadata lock and why the online DDL operation
is blocked by waiting on metadata lock, and how pinpoint the blocking session by using
metadata_lock system table.
=====Reference======
[1] https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html
[2] https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html
[3] https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html
[4] https://dev.mysql.com/doc/refman/8.0/en/server-system-
variables.html#sysvar_lock_wait_timeout
