A “Waiting for metadata lock” state occurs when a database session tries to change a table’s structure (like
ALTER TABLE or DROP TABLE) while another session is still using that table. To keep the data consistent, the database prevents any structural changes until all active transactions on that table are finished.Why This Happens
- Active Transactions: A session started a transaction (even just a
SELECTstatement) and hasn’t committed or rolled it back yet. - Long-Running Queries: A heavy report or slow data modification is still running on the target table.
-
Unclosed Connections: Applications or ORMs (like Django) may leave connections open in a “Sleep” state while still holding a lock.
How to Fix It
-
Identify the Blocker:
Run this command to see all active sessions:sqlSHOW FULL PROCESSLIST;
Use code with caution.Look for a session in theSleepstate that has been active for a long time, or one currently running a query on the same table. -
Use Performance Schema (MySQL 5.7+):
For a more precise view of who is holding the lock, query themetadata_lockstable:sqlSELECT * FROM performance_schema.metadata_locks WHERE LOCK_STATUS='GRANTED';
Use code with caution.This identifies the specific thread holding the lock that others are waiting for. -
Kill the Blocking Session:
Once you find theIDof the thread holding the lock, you can terminate it:sqlKILL [thread_id];
Use code with caution.Note: Killing the threads that are “Waiting” won’t solve the problem; you must kill the one holding the lock.
Prevention Tips
- Set Timeouts: Lower the lock_wait_timeout to prevent structural changes from hanging indefinitely.
- Commit Often: Ensure your application code always commits or rolls back transactions, especially after
SELECTstatements in non-autocommit mode. -
Batch DDL: Perform structural changes during low-traffic periods to minimize the chance of conflicting with active users.
