Contents
What is table metadata lock?
MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.
What does waiting for table flush mean?
If a query is running that accesses that table at the time the the FLUSH TABLE is issued, the FLUSH TABLE will be blocked until the query completes. While the FLUSH TABLE is blocked, no new queries accessing the affected tables can start running and their state in the process list will show “Waiting for table flush“.
What is waiting for table flush MySQL?
This is because MySQL has detected that the underlying table has changed and it needs to close and reopen it using FLUSH. Therefore the table will be locked until all queries that are using that table finish. There are only two solutions to this situation, wait until the long query finishes or kill the query.
What does thread status ” waiting for table metadata lock ” mean?
(Doc ID 1365549.1) Information in this document applies to any platform. Since version MySQL Server 5.5 there is a new thread state in the output of SHOW PROCESSLIST: To view full details, sign in with your My Oracle Support account. Don’t have a My Oracle Support account?
Is there way to override ALTER TABLE metadata lock in MySQL?
They can be overridden in MySQL config file (my.cnf) for example with: If you’re not sure what they currently are, you can always check it with: After that, in most cases, your ALTER TABLE query will execute without getting “Waiting for table metadata lock”.
When does a DDL wait for a metadata lock?
A DDL waits for a metadata lock on a table up to a certain timeout. > Application programmers make these assumptions all the time, and if MySQL violates them, > then MySQL has a bug. Application programmers should try to familiarize themselves with the changes in the product.
How to find which transaction is causing a lock?
A List of locks on particular table: A list of transactions waiting for locks: Reference – MySQL Troubleshooting: What To Do When Queries Don’t Work, Chapter 6 – Page 96. If you cannot find the process locking the table (cause it is alreay dead), it may be a thread still cleaning up like this