收到开发反应一库的sql频繁超时，系统几乎瘫痪，无法执行任何操作，我登上库先查看到当前的线程，发现有大量的线程状态是

Waiting for table flush

 

查看当前的事务

从昨天开始执行，到今天早晨还没执行完，具体原因还没深究，先将此线程释放，然后备份才可以flush table成功继而备份完成后后面一系列被阻塞的sql都得以正常运行

mysql> select * from information_schema.innodb_trx\G*************************** 1. row ***************************                    trx_id: 192611452                 trx_state: RUNNING               trx_started: 2017-11-30 18:33:58     trx_requested_lock_id: NULL          trx_wait_started: NULL                trx_weight: 3688       trx_mysql_thread_id: 352932171                 trx_query: DELETE FROM xx WHERE xx IN(SELECT xx                                                FROM xx WHERE Remarks LIKE xx)       trx_operation_state: unlock_row         trx_tables_in_use: 2         trx_tables_locked: 2          trx_lock_structs: 3688     trx_lock_memory_bytes: 368848           trx_rows_locked: 4         trx_rows_modified: 0   trx_concurrency_tickets: 0       trx_isolation_level: READ COMMITTED         trx_unique_checks: 1    trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0          trx_is_read_only: 0trx_autocommit_non_locking: 0

后来想了一下每天的凌晨两点有物理备份，于是查看备份日志，发现果然是上面的事务阻塞了物理备份;

 

物理备份的整个流程

先记录当前redo log的序列号171201 02:00:02 >> log scanned up to (54138135415)xtrabackup: Generating a list of tablespacesxtrabackup: using the full scan for incremental backupxtrabackup: Starting 4 threads for parallel data files transfer然后备份innodb库表171201 02:00:12 [01] Copying .备份完之后flush table；因为被阻塞，所以知道释放完事务后才成功171201 02:00:17 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 接着开始备份非事务库表171201 09:36:13 Executing FLUSH TABLES WITH READ LOCK...171201 09:36:13 >> log scanned up to (54147795188)171201 09:36:14 Starting to backup non-InnoDB tables and files      171201 09:36:14 [01] Copying ....xtrabackup: The latest check point (for incremental): '54138858140'xtrabackup: Stopping log copying thread..171201 09:36:14 >> log scanned up to (54147795198)171201 09:36:14 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 备份完之后释放表锁171201 09:36:14 Executing UNLOCK TABLES171201 09:36:14 All tables unlocked171201 09:36:14 [00] Copying ib_buffer_pool to xxx171201 09:36:14 [00]        ...done171201 09:36:14 Backup created in directory xxxxMySQL binlog position: xxx171201 09:36:14 [00] Writing backup-my.cnf171201 09:36:14 [00]        ...done171201 09:36:14 [00] Writing xtrabackup_info171201 09:36:14 [00]        ...donextrabackup: Transaction log of lsn (54138129801) to (54147795198) was copied.171201 09:36:15 completed OK!

被阻塞的语句是FLUSH NO_WRITE_TO_BINLOG TABLES...

官方解释flush tables

Closes all open tables, forces all tables in use to be closed, and flushes the query cache and prepared statement cache.

没有涉及到锁相关的字眼；但是测试表明在执行查询或者变更还未完成时，如果另起一个会话执行flush tables 则会被阻塞，

如果此后如果有操作慢查询中的表的任何sql都会被阻塞；