FLUSH TABLES WITH READ LOCK

前言

昨天碰到一个flush tables with read lock阻塞其他所有操作的案例，在博客园中6年前我已经写过一篇文章，再次拿出来分享下。

现象

id    账号    来源ip    库名    执行时间(秒)    状态    线程占用的内存(字节)    SQL占用的内存(字节)    SQL            
11316259    test_user    127.0.0.1:51533    test    710    Writing to net    48016    9296    select * from tbname5 where ...;    
11316272    test_user    127.0.0.1:51589    test    707    Writing to net    60440    10056    select * from tbname5 where ...;    
11316276    test_user    127.0.0.1:51657    test    704    Writing to net    60440    10056    select * from tbname5 where ...;    
11316299    test_user    127.0.0.1:51849    test    696    Writing to net    60440    9616    select * from tbname5 where ...;    
11316383    test_user    127.0.0.1:52379    None    672    Waiting for table flush    0    8208    flush tables with read lock    
11271706    test    127.0.0.1:40421    test    667    Waiting for global read lock    0    8208    UPDATE tbname1 where ...;    
11317050    test    127.0.0.1:40066    test    120    Waiting for global read lock    53352    8208    UPDATE tbname6 where ...;
11317338    test_user    127.0.0.1:58803    test    63    Waiting for table flush    39776    8208    SHOW CREATE TABLE `test`.`tbname10`        
11317352    test    127.0.0.1:5680    test    35    Waiting for global read lock    47968    8368    INSERT INTO tbname9    ...;
11317390    test    127.0.0.1:5726    test    30    Waiting for global read lock    39520    8208    UPDATE tbname8 where ...;
11317395    test    127.0.0.1:40427    test    27    Waiting for global read lock    39776    8208    SELECT  * FROM tbname7 where ... FOR UPDATE    
11317345    test    127.0.0.1:42949    test    25    Waiting for global read lock    47968    8504    INSERT INTO tbname6    
11317404    test    127.0.0.1:40435    test    19    Waiting for global read lock    39520    8208    UPDATE tbname5    where ...;    
11317435    test    127.0.0.1:43028    test    9    Waiting for global read lock    47968    8368    INSERT INTO tbname4 ...;
11317422    test    127.0.0.1:40451    test    3    Waiting for global read lock    47968    8368    INSERT INTO tbname3    ...;
11317434    test    127.0.0.1:5759    test    3    Waiting for global read lock    47968    8472    INSERT INTO tbname2    ...;
11317442    test    127.0.0.1:5790    test    2    Waiting for global read lock    47968    8472    INSERT INTO tbname2    ...;

前几个执行比较慢的SQL，阻塞了FTWRL,FTWRL阻塞了后面的DML和SELECT...FOR UPDATE.

flush tables with read lock

会关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。也就是将所有的脏页都要刷新到磁盘，然后对所有的表加上了读锁，于是这时候直接拷贝数据文件也就是安全的。
但是如果你发出命令FTWRL时，还有其他的操作，而且是很耗时的操作呢？先说写操作，这个FTWRL肯定是得等的，等写操作完成才能执行FTWRL，这个很好理解。那么对于其他的读操作呢？
比如说在FLWRL发出之前有一个query：

select count(*) from tb

那么FTWRL也得等待(show processlist可以看到 waiting for table flush)。

为什么会阻塞读？

你可能会说在mysql中读与读不是不会排斥的吗，为什么需要等待呢？因为FTWRL是要flush脏页的，只有这样才真的能保证数据一致性(比如说在xtrabackup备份MyISAM表的时候)，而在select count(*) from tb执行的时候，因为所有的操作都是在内存中操作，所以此时还不能完全flush，因此FTWRL就得等待。
或许你还会有疑问，select的页不是脏页，为什么FTWRL还要等待呢？难道MySQL不能做得更完善点吗？我觉得MySQL还不是不会做的这么简单吧，等待的原因是因为这个表很大，无法一次性将所有的页都读到内存中来，而query具有原子性，总不可能执行一般被堵塞吧，所以说还是得乖乖的让它执行完，所以FTWRL就得等待了。
flush tables with read lock在测试的时候，它有可能花几毫秒就可以完成，就像我遇到的情况，在生产环境也可能花几个小时才能完成。在此期间，MySQL服务完全block住了，而不仅仅是read-only。因为FTWRL会做以下动作：

请求锁

flush tables with read lock请求全局read lock。当这种情况发生时，其他进程如果有修改动作的话就会被阻塞。从理论上讲，这种情况并不是很糟糕，因为flush tables with read lock只需要read lock，其它命令（只需要read lock的命令）可以和flush tables with read lock并存。然而，事实上，大多数表需要读和写锁的。例如：第一个写语句会被这个全局的读锁阻塞，而子查询又会被第一个写语句阻塞，所以真正有效果的是使用的是排它锁，所有新请求就会被阻塞，包括读查询语句。

等待锁

在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables with read lock就会被阻塞，直到所有的锁被释放。请看下面的例子：

mysql> show processlist;
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
| Id | User | Host      | db   | Command    | Time | State             | Info                                                                 |
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
|  4 | root | localhost | test | Query      |   80 | Sending data      | select count(*) from t t1 join t t2 join t t3 join t t4 where t1.b=0 |
|  5 | root | localhost | test | Query      |   62 | Flushing tables   | flush tables with read lock                                          |
|  6 | root | localhost | test | Field List |   35 | Waiting for table |                                                                      |
|  7 | root | localhost | test | Query      |    0 | NULL              | show processlist                                                     |
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)

可以看到线程6没有连进来，因为MySQL的客户端连接时没有指定-A，它尝试获取当前库下的所有的表和列。线程5也没有flush tables，因为它在等线程4释放锁。

刷新表

当flush tables with read lock拿到锁后，必定flush data。对于MyISAM引擎，不光是刷新它自己的data，也刷新操作系统的data到disk上（MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches），所以如果是MyISAM表的话有可能会花费很长时间。

持有锁

我们可以使用unlock tables或者其它命令来释放锁。

结论

一个备份系统一般都是在生产环境中用的，所以我们不能简单的认为flush tables with read lock很快就执行完。在某些情况下，执行慢是没法避免的。但是我们可以配置备份系统避免这种global lock。

参考

http://dev.mysql.com/doc/refman/5.7/en/flush.html
http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is-flush-tables-with-read-lock/