How to handle waiting for metadata lock
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:
SHOW FULL PROCESSLIST;
Look for a session in the Sleep state 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 the metadata_locks table.
SELECT * FROM performance_schema.metadata_locks WHERE LOCK_STATUS='GRANTED';
This identifies the specific thread holding the lock that others are waiting for.
Kill the Blocking Session:
Once you find the ID of the thread holding the lock, you can terminate it.
KILL [thread_id];
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 SELECT statements in non-autocommit mode.
- Batch DDL: Perform structural changes during low-traffic periods to minimize the chance of conflicting with active users.
