If you see the “Waiting for table metadata lock” error, you may be wondering what is the best course of action to prevent it in the future. I have briefly discussed troubleshooting metadata locks before, however, that post more illustrated how easy it is to encounter the “Waiting for table metadata lock” error in both MyISAM and InnoDB. It provides simple examples for reproducing using both storage engines, so one could hopefully more easily identify where the locks are stemming from in their particular case.

Pin-pointing which transaction holds the locks is a different story. There are feature requests filed in MySQL and MariaDB bugs databases to track this information, but they are recent, and no ETA is scheduled for either yet, as far as I know:

http://bugs.mysql.com/bug.php?id=69527 …

With the introduction of metadata locking in MySQL 5.5, it is much more common to see queries stuck in the “Waiting for table metadata lock” state.

If you see a query stuck in the “Waiting for table metadata lock” state, waiting for a MyISAM table, then it is because this table has been included as part of some uncommitted transaction (whether intentionally or not).

In fact, it could be as simple as issuing SET AUTOCOMMIT=0 followed by a SELECT against a MyISAM table (a transaction can be started 3 ways: START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0). After that, the table will be locked to other DDL statements until the transaction is committed (or ended).

This metadata lock is meant for DDL statements, and so that is what it blocks. However, once a DDL statement is blocked and waiting, then all queries …