MySQL appears large flush TABLES with READ LOCK

Solutions

Failure summary

The global read lock in the system was acquired by another thread (with an S lock), so DML will appear waiting for global read lock on the GRL with IX lock, and the set READ_ONLY is the S lock on GRL, So at this point it is possible to grant, and at this time there is a large query in the system, set global Read_only=1 needs to wait for the table to be closed, so the status is: Waiting for table flush

So the reason can be determined: there is a flush tables with read lock in the system but no unlock tables

Little Tricks

Before manually doing flush TABLES with read Lock/set global Read_only=1, perform a flush TABLES to greatly reduce the system's time to read-only

Reference

http://www.gpfeng.com/?p=222

MySQL appears large flush TABLES with READ LOCK