mysql,

MySQL Fix: 'Waiting for table metadata lock'

Feb 24, 2022 · 2 mins read · Post a comment
MySQL Fix: 'Waiting for table metadata lock'

Working with high load SQL queries or big transactions, you may face: Waiting for table metadata lock. Here I’m gonna list some useful solutions that might help you.

Prerequisites

  • MySQL
  • MariaDB
  • AWS Aurora

Solution

Step 1. As a first step get a full list of processes and see if the state is Waiting for table metadata lock.

SHOW processlist;

Example output:

+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User            | Host            | db       | Command | Time | State                  | Info             |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
|4569| admin           | localhost       | devcoops | Daemon  | 6899 | Waiting for table metadata lock | NULL    |
...

To kill that process, execute:

kill 4569

To list all blocking transactions, run:

SELECT * 
FROM INNODB_LOCKS 
WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);

Step 2. It may happen the process that is locking the table to be dead but maybe some thread is still stuck. So to find that one, run:

show engine innodb status;

There will be a list of all transactions but you need to spot the one that is taking a lot of time like in the example below:

Example output:

---TRANSACTION 1336758179, ACTIVE 7789 sec
MySQL thread id 6710429, OS thread handle ...

Killing the transaction thread should solve the issue.

kill 6710429

Step 3. If the previous methods didn’t help to solve the issue you can reboot the MySQL daemon to get rid of all Waiting for table metadata lock processes. If your MySQL or MariaDB is hosted on-premises server you need to ssh into the server and run:

  • RHEL based distro:
    sudo systemctl restart mysqld
    
  • Debian based distro:
    sudo systemctl restart mysql
    

If you are using AWS as a hosting provider you can do that through the RDS console.

Conclusion

Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.