本文共 5419 字，大约阅读时间需要 18 分钟。
昨天碰到一个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.
会关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行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://vekix.baihongyu.com/