MySQL Internals Manual  /  Important Algorithms and Structures  /  How MySQL Handles FLUSH TABLES

10.6 How MySQL Handles FLUSH TABLES

  • FLUSH TABLES is handled in sql/

  • The idea of FLUSH TABLES is to force all tables to be closed. This is mainly to ensure that if someone adds a new table outside of MySQL (for example, by copying files into a database directory with cp), all threads will start using the new table. This will also ensure that all table changes are flushed to disk (but of course not as optimally as simply calling a sync for all tables)!

  • When you do a FLUSH TABLES, the variable refresh_version is incremented. Every time a thread releases a table, it checks if the refresh version of the table (updated at open) is the same as the current refresh_version. If not, it will close it and broadcast a signal on COND_refresh (to await any thread that is waiting for all instances of a table to be closed).

  • The current refresh_version is also compared to the open refresh_version after a thread gets a lock on a table. If the refresh version is different, the thread will free all locks, reopen the table and try to get the locks again. This is just to quickly get all tables to use the newest version. This is handled by sql/ and sql/

  • When all tables have been closed, FLUSH TABLES returns an okay to the client.

  • If the thread that is doing FLUSH TABLES has a lock on some tables, it will first close the locked tables, then wait until all other threads have also closed them, and then reopen them and get the locks. After this it will give other threads a chance to open the same tables.