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 to that table (including SELECTs) are blocked. Here is how you can easliy reproduce it:
Connection #1:
create table t1 (id int) engine=myisam; set @@autocommit=0; select * from t1;
Connection #2:
alter table t1 rename to t2; <-- Hangs
You can see it hanging here (as well as a subsequent SELECT from a 3rd connection):
mysql> show full processlist; +----+---+-------+---------------------------------+-----------------------------+ | Id |...| Time | State | Info | +----+---+-------+---------------------------------+-----------------------------+ | 1 |...| 1653 | | NULL | | 2 |...| 0 | NULL | show full processlist | | 3 |...| 18012 | Waiting for table metadata lock | alter table t1 rename to t2 | | 4 |...| 17983 | Waiting for table metadata lock | select * from t1 | +----+---+-------+---------------------------------+-----------------------------+
This is the expected behavior, but can be a little tricky to diagnose if it's happening to you, and you don't see any other queries currently running in SHOW FULL PROCESSLIST (and/or SHOW ENGINE INNODB STATUS). Furthermore, you cannot track this with any of the known lock tracking methods, such as InnoDB Lock Monitor, InnoDB Status, mysqladmin debug output, INFORMATION_SCHEMA, etc.), not to mention one generally doesn't associate transactions with MyISAM tables anyway.
You can also run into this same error in InnoDB, which one would not necessarily expect because of InnoDB's row-level locking.
However, when you introduce triggers, then it becomes easy to reproduce a similar situation.
create table t3 (id int) engine=innodb;
create table t4 (id int) engine=innodb;
delimiter |
CREATE TRIGGER t3_trigger AFTER INSERT ON t3
FOR EACH ROW BEGIN
INSERT INTO t4 SET id = NEW.id;
END;
|
delimiter ;
Connection #1:
begin; insert into t3 values (1);
Connection #2:
drop trigger if exists t3_trigger; <-- Hangs
So, if you see this with an InnoDB table, then check your SHOW ENGINE INNODB STATUS and look for prior transactions that hold locks and are still running. Those would be your likely culprits for what is blocking the DROP TRIGGER command (in this case).
Hope this helps and happy troubleshooting. 
Tags: innodb locking, innodb locks, lock_wait_timeout, metadata lock, myisam locking, myisam locks, mysql hang, mysql locking, mysql locks, Waiting for table metadata lock
