We are trying to drop one table but it's getting hanged, and when we see the 'SHOW PROCESSLIST' command it's showing as 'waiting for meta data lock'. Even we are unable to perform any operation on that particular table. Does anybody know how to resolve it?
If an InnoDB table is being accessed at all via SELECT or DML (INSERT, UPDATE, DELETE), you should rightly expect a metadata lock.
According to the MySQL Documentation on MetaData Locking:
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.
That being said, you should determine whether or not another DB Session is holding locks on the table. If such a session is an uncompleted transaction, there is where the hold up may be.
If you simply need the diskspace back quickly, you could run
Try renaming the table before attempting to drop it as following:
ALTER TABLE mydb.mytable RENAME mydb.mytabletodrop; TRUNCATE TABLE mydb.mytabletodrop; DROP TABLE mydb.mytabletodrop;
You should find which query causes a transaction lock by executing:
SHOW ENGINE INNODB STATUS
and see a section named
TRANSACTION. Later you should kill that query to drop a database.