How to Resolve MySQL Waiting for Table Metadata Lock Issue
Table of contents
This post guides you through the process of addressing performance degradation in MySQL InnoDB databases due to Waiting for Table Metadata Lock.
Cases When Metadata Lock Occurs
Executing DDL Statements: Metadata lock can occur when executing DDL (Data Definition Language) statements that change the structure of the database.
Transaction Processing: Metadata lock can occur during transaction processing. When transactions are processed, MySQL sets a metadata lock on the database objects used during the transaction.
Record Locks: Metadata lock can occur when MySQL sets record locks. Record locks control access to specific records, and MySQL uses metadata locks when setting record locks.
Index Creation: When executing INSERT or UPDATE queries, MySQL uses metadata locks for creating or modifying indexes.
Table Locks: When executing INSERT or UPDATE queries, MySQL uses table locks to restrict access from other sessions. Table locks are related to metadata locks in this context.
Solutions
Terminating Processes that Cause Locks
Execute SHOW FULL PROCESSLIST in the MySQL Console to identify the processes and information Waiting for Table Metadata Lock state.
mysql> SHOW FULL PROCESSLIST
Id User Host db Command Time State Info
------- --------- -------------------- ------ ------- ------ ------------------------------- -------------------------------
1894823 dbuser 172.30.3.31:32982 dbname Query 0 Waiting for table metadata lock INSERT INTO table_name SET ...Alternatively, you can use SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE [Condition...] to query specific data.
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 30 AND STATE LIKE '%Waiting for table metadata lock%'
ID USER HOST DB COMMAND TIME STATE INFO
------- --------- ----------------- ------ ------- ------ ------------------------------- --------------------------------
1920735 dbuser 172.30.3.31:32982 dbname Query 35 Waiting for table metadata lock INSERT INTO table_name SET ...
1920755 dbuser 172.30.3.31:49340 dbname Query 35 Waiting for table metadata lock INSERT INTO table_name SET ...
1921446 dbuser 172.30.3.14:38118 dbname Query 49 Waiting for table metadata lock UPDATE table_name SET ...
1921339 dbuser 172.30.3.14:37524 dbname Query 34 Waiting for table metadata lock INSERT INTO ...
1921181 dbuser 172.30.3.73:56050 dbname Query 42 Waiting for table metadata lock UPDATE table_name SET ...If the oldest ID causing the lock is 1921446, terminate the process using the KILL command.
mysql> KILL 1921446
