MySQL:Waiting for table flush，blocker与表锁

主要内容

所有操作基于mysql8.0

操作顺序：
id 257 – lock table users read;
id 258 – flush tables;
id 259 – select * from users

mysql> show processlist;
+-----+-----------------+----------------+--------------------+---------+--------+-------------------------+---------------------+
| Id  | User            | Host           | db                 | Command | Time   | State                   | Info                |
+-----+-----------------+----------------+--------------------+---------+--------+-------------------------+---------------------+
|   5 | event_scheduler | localhost      | NULL               | Daemon  | 773583 | Waiting on empty queue  | NULL                |
| 257 | test            | localhost:7117 | testdb             | Sleep   |   1933 |                         | NULL                |
| 258 | root            | localhost:7226 | NULL               | Query   |   6401 | Waiting for table flush | flush tables        |
| 259 | root            | localhost:7324 | testdb             | Query   |   6349 | Waiting for table flush | select * from users |
| 261 | root            | localhost:7332 | performance_schema | Query   |      0 | init                    | show processlist    |
+-----+-----------------+----------------+--------------------+---------+--------+-------------------------+---------------------+
5 rows in set (0.00 sec)

从操作者的角度可以知道，Waiting for table flush的源头是id 257，但如果是其他人，可能就第一眼看出是id 257造成(虽然生产中一般不会有lock table操作出现)。
这位博主 (https://blog.csdn.net/feijiges/article/details/77430806) 讲的非常清楚了，其中提到innotop可以查看表锁信息，但innotop又是如何查到的表锁信息的？

在几个关于lock与wait的视图并查不到相关锁或锁等待信息，比如：
sys.innodb_lock_waits
sys.schema_table_lock_waits;
sys.x$innodb_lock_waits
performance_schema.data_lock_waits
performance_schema.data_locks
这就比较苦恼了。

最后查到performance_schema.table_handles中有INTERNAL_LOCK，EXTERNAL_LOCK等信息。table_handles表 是Table locks and lock requests的相关信息，接着就是查看文档的时候了：

table_handles:
The Performance Schema exposes table lock information through the table_handles table to show the table locks currently in effect for each opened table handle. table_handles reports what is recorded by the table lock instrumentation. This information shows which table handles the server has open, how they are locked, and by which sessions.
//performance schema通过table_handles表来揭露表锁信息，显示当前对每个打开的表句柄有效的表锁。

The table_handles table is read only and cannot be updated. It is autosized by default; to configure the table size, set the performance_schema_max_table_handles system variable at server startup.
Table lock instrumentation uses the wait/lock/table/sql/handler instrument, which is enabled by default.

这个性能表中的两个字段：
INTERNAL_LOCK -- SQL层级上的表锁使用. 其值是下列值的其中一个：READ, READ WITH SHARED LOCKS, READ HIGH PRIORITY, READ NO INSERT, WRITE ALLOW WRITE, WRITE CONCURRENT INSERT, WRITE LOW PRIORITY, WRITE. 
EXTERNAL_LOCK -- 存储引擎层级上使用的表锁，其值是READ EXTERNAL或WRITE EXTERNAL中的一个。

于是拼写了一个SQL，查看Waiting for table flush现象时的表锁情况：

select thr.PROCESSLIST_ID,thr.thread_id,p.COMMAND,p.TIME,p.STATE,p.INFO,thl.OBJECT_TYPE,thl.OBJECT_SCHEMA,thl.OBJECT_NAME,thl.INTERNAL_LOCK,thl.EXTERNAL_LOCK from performance_schema.threads thr inner join performance_schema.table_handles thl  on  thr.thread_id=thl.OWNER_THREAD_ID  inner join performance_schema.processlist p on p.id=thr.processlist_id where thl.EXTERNAL_LOCK is not null or thl.internal_lock is not null;

另外记录几点：

1. mysqldump在–flush-logs 或–master-data 时会触发flush tables.

在当进行长时间查询时，单独使用mysqldump --single-transaction并不会出现等待情况，但如果使用了–flush-logs戒–master-data后，再单独使用–single-transaction就会出现阻塞。这里面的机制有点费解。不过，只要kill最源头的查询，即可解决这些问题。

2. lock table造成的表锁的释放

在id 257 上进行commit，并不能释放锁，目前发现两种方法可释放lock table造成的表锁：1) 在锁表的当前会话中执行unlock tables；2) kill 锁表的会话。而长时间的查询，只要相关会话取消该查询即可释放，当然kill该会话也可释放。

3. 在mysql命令行中执行flush logs操作并不会被长时间查询或lock table阻塞

4. 奇怪的是在执行后select *,sleep(10000) from users后，表锁信息是：

	+----------------+-----------+---------+------+------------+----------------------------------+-------------+---------------+-------------+---------------+---------------+
	| PROCESSLIST_ID | thread_id | COMMAND | TIME | STATE      | INFO                             | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INTERNAL_LOCK | EXTERNAL_LOCK |
	+----------------+-----------+---------+------+------------+----------------------------------+-------------+---------------+-------------+---------------+---------------+
	|            266 |       306 | Query   |    8 | User sleep | select *,sleep(10000) from users | TABLE       | testdb        | users       | NULL          | READ EXTERNAL |
	+----------------+-----------+---------+------+------------+----------------------------------+-------------+---------------+-------------+---------------+---------------+

5. sleep(n)函数(从效果看，是要等待的时间= 行数*n的时间)，也挺有用。

mysql> select sid, sleep(1) from testdb.student;
+------+----------+
| sid  | sleep(1) |
+------+----------+
| 01   |        0 |
| 02   |        0 |
| 03   |        0 |
| 04   |        0 |
| 05   |        0 |
| 06   |        0 |
| 07   |        0 |
| 09   |        0 |
| 10   |        0 |
| 11   |        0 |
| 12   |        0 |
| 13   |        0 |
| 14   |        0 |
| 14   |        0 |
| 14   |        0 |
| 15   |        0 |
| 15   |        0 |
| 15   |        0 |
+------+----------+
18 rows in set (18.16 sec)


mysql> select sid, sleep(1) from testdb.student limit 1;
+------+----------+
| sid  | sleep(1) |
+------+----------+
| 01   |        0 |
+------+----------+
1 row in set (1.01 sec)

mysql> select sid, sleep(0.1) from testdb.student limit 1;
+------+------------+
| sid  | sleep(0.1) |
+------+------------+
| 01   |          0 |
+------+------------+
1 row in set (0.10 sec)

mysql> select sid, sleep(0) from testdb.student limit 1;
+------+----------+
| sid  | sleep(0) |
+------+----------+
| 01   |        0 |
+------+----------+
1 row in set (0.00 sec)

mysql> select *, sleep(null) from testdb.student limit 1;
ERROR 1210 (HY000): Incorrect arguments to sleep.

mysql> select *, sleep(-1) from testdb.student limit 1;
ERROR 1210 (HY000): Incorrect arguments to sleep.

6. mysqldump中关于–single-transaction选项

–single-transaction : Issue a BEGIN SQL statement before dumping data from server
这个选项设置事务隔离级别为Repeatable read，并且在导出数据之前发送start transaction命令。
目前只在InnoDB存储引擎相关表起作用，用于导出start transaction命令发起时间点的一致性数据，而不会阻塞其他会话。
一致性读与下列表操作不兼容：ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE。并且不能与–lock-tables选项一起使用，原因是LOCK TABLES操作有隐式commit。