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:
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:
You should find which query causes a transaction lock by executing:
and see a section named