Working with high load SQL queries or big transactions, you may face: Waiting for table metadata lock. Here I’m going to list some useful solutions that might help you.
Prerequisites
- MySQL
- MariaDB
- AWS Aurora
- sudo privileges
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;
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, and you need to spot the one that is taking a lot of time like in the example below:
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.