MySQL - 'FLUSH TABLES WITH READ LOCK' is pending for a long time
The Problem
After issuing FLUSH TABLES WITH READ LOCK, we can see it is pended for a long time with one of the following status:
- Flushing tables
- Waiting for table flush
- Waiting for global read lock
Check whether:
- Check whether there are any long-running queries which execution time is the same or longer than the time the FLUSH TABLES WITH READ LOCK has been waiting. You can consider killing this query to allow the rest of the connections to proceed.
- LOCK TABLES … WRITE was executed in one of the other connections. Unfortunately, there is no good way to check that, but if 1. is not the case, then this is the most likely cause. You will need to investigate the application logic to see where the LOCK TABLES … WRITE is executed and there is an UNLOCK TABLES when the application no longer requires the lock. Killing the connection will release the lock(s).
Note: If the query has changes rows in InnoDB tables, be aware it will take longer time to roll back the query than it has been running. For queries on non-transactional tables, the changes already made will not be rolled back.
If you think that the problem is long blocking time only, consider setting the lock_wait_timeout option short. This option is introduced as of MySQL 5.5 series and limits maximum waiting time for a table and global read locks. It causes ER_LOCK_WAIT_TIMEOUT when the timeout is reached instead of blocking infinitely.