flush table mysql_MySQL flush table 导致的锁问题

MySQL Flush导致的等待问题tables 参数关闭所有打开的表，同时该操作将会清空查询缓存中的内容

FLUSH TABLES WITH READ LOCK   关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，

直到显示地执行unlock tables，该操作常常用于数据备份的时候。

链接：http://wangqiaowqo.iteye.com/blog/1627985

前言

在实际生产环境中有时会发现大量的sql语句处于waiting for table.. 状态中，有时候看起来会让人觉得困惑，本文将讲解此状态产生的原因及解决办法。

正文

本文中用到了lock table来模拟实际情况， 首先介绍一个lock tables需要注意的事项，如果在一个session中使用了lock tables，那么在此session中将只能访问获取到lock的表。官方解释：

If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired.

A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement:

Tables in the INFORMATION_SCHEMA database are an exception. They can be

accessed without being locked explicitly even while a session holds

table locks obtained with LOCK TABLES.

基于这样的原因，往往在备份时选择flush table with read lock;是个不错的选择，如果成功，它会锁住所有表。

Closes all open tables, forces all tables in use to be closed, and

flushes the query cache. FLUSH TABLES also removes all query results

from the query cache, like the RESET QUERY CACHE statement.

模拟Flush操作导致的waiting tables等待问题。

a) 在终端1中执行lock table锁住jackjhu库中的一张表

mysql> lock table t1 read;

Query OK, 0 rows affected (0.00 sec)

b) 在终端2中执行flush tables

mysq