Analysis of MySQL FTWRL (Flush Table With Read Lock) Blocking Behavior

The article investigates the behavior of MySQL's FLUSH TABLE WITH READ LOCK (FTWRL) command, focusing on why it can block other sessions and how to analyze the problem. Two test cases are presented using MySQL 5.7.22, each demonstrating a different blocking scenario.

Case 1 – Global Read Lock Blocking

A table baguait1 is created and a SELECT ... FOR UPDATE statement is executed, acquiring a global IX lock. When FTWRL is issued, it attempts to acquire a global S lock, which conflicts with the existing IX lock, resulting in the state Waiting for global read lock . Killing the FTWRL session releases the lock without affecting the original SELECT FOR UPDATE operation.

mysql> select Id,State,Info from information_schema.processlist where command<>'sleep';
+----+------------------------------+------------------------------------------------------------------------------------+
| Id | State                        | Info                                                                               |
+----+------------------------------+------------------------------------------------------------------------------------+
|  1 | Waiting on empty queue       | NULL                                                                               |
| 18 | Waiting for global read lock | flush table with read lock                                                         |
|  3 | User sleep                   | select sleep(1000) from baguait1 for update                                        |
|  6 | executing                    | select Id,State,Info   from information_schema.processlist  where command<>'sleep' |
+----+------------------------------+------------------------------------------------------------------------------------+

Case 2 – Table Flush Blocking

A simple SELECT (no FOR UPDATE ) is run, which does not acquire a global lock. After issuing FTWRL, the global S lock is granted, but the command must flush the table cache. Because the table cache entry for baguait1 is still in use by the sleeping query, FTWRL waits for the cache to be released, showing the state Waiting for table flush . Killing the FTWRL session does not unblock the waiting SELECT until the original sleep finishes.

mysql> select Id,State,Info from information_schema.processlist where command<>'sleep';
+----+------------------------------+------------------------------------------------------------------------------------+
| Id | State                        | Info                                                                               |
+----+------------------------------+------------------------------------------------------------------------------------+
|  1 | Waiting on empty queue       | NULL                                                                               |
| 26 | User sleep                   | select sleep(1000) from baguait1                                                   |
| 23 | Waiting for table flush      | flush table with read lock                                                         |
|  6 | executing                    | select Id,State,Info   from information_schema.processlist  where command<>'sleep' |
+----+------------------------------+------------------------------------------------------------------------------------+

Where the Sleep Function Takes Effect

The article uses SLEEP() to simulate a long‑running SELECT. Profiling shows that the sleep is executed after each row passes the WHERE filter, meaning the table is already opened and MDL‑locked before the pause, making it a valid proxy for a heavy SELECT.

T@3: | | | | | | | | >evaluate_join_record
T@3: | | | | | | | | | enter: join: 0x7ffee0007350 join_tab index: 0 table: tii cond: 0x0
T@3: | | | | | | | | | counts: evaluate_join_record join->examined_rows++: 1
T@3: | | | | | | | | | >end_send
T@3: | | | | | | | | | | >Query_result_send::send_data
T@3: | | | | | | | | | | | >send_result_set_row
T@3: | | | | | | | | | | | | >THD::enter_cond
T@3: | | | | | | | | | | | | | >PROFILING::status_change
T@3: | | | | | | | | | | | | |

FTWRL Internal Workflow

The source code path for FTWRL is in mysql_execute_command handling SQLCOM_FLUSH . The main steps are:

Acquire a global MDL lock of type S .

Increment the global table‑cache version ( refresh_version++ ).

Release unused table‑cache entries via close_cached_tables() .

If any table cache entry still has an old version, wait for its owner to release it ( Waiting for table flush ).

Acquire a COMMIT‑level MDL S lock, which can be blocked by long‑running commits.

Key code excerpts:

if (thd->global_read_lock.lock_global_read_lock(thd)) // acquire GLOBAL S lock
    return 1; // killed

if (close_cached_tables(thd, tables, (options & REFRESH_FAST) ? FALSE : TRUE, thd->variables.lock_wait_timeout))
    { /* ... */ }

if (thd->global_read_lock.make_global_read_lock_block_commit(thd)) // COMMIT S lock
    { /* ... */ }

Blocking Scenarios Summary

What blocks FTWRL: Long‑running DDL/DML/​FOR UPDATE statements (global IX lock) and large commits (global IX on COMMIT).

What FTWRL blocks: Subsequent DDL/DML/​FOR UPDATE (global S lock) and any operation that needs the table cache after FTWRL has advanced the cache version.

FTWRL never blocks plain SELECT unless the SELECT itself holds a table‑cache entry that FTWRL must wait to flush.

Practical Recommendations

Backup tools that issue FTWRL should be used with caution on production systems, especially when long‑running queries, DDL, or large transactions are active. Killing the FTWRL session alone does not resolve the blockage; the original blocking operation must finish or be killed.

For deeper understanding, refer to the author's book 深入理解MySQL主从原理 32讲 and the original source code in the Percona Server 5.7.22 distribution.