长时间DDL中执行FLUSH TABLE WITH READ LOCK导致数据库无法服务

在某项目中使用了xtrabackup来备份mysql数据库。有两次异常的DDL语句执行时间过长，导致凌晨备份时flush table with read lock锁无法释放。后续增删改操作阻塞。

flush table with read lock的官方解释如下

FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not
subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking
and implicit commits. For example, START TRANSACTION does not release the global read lock.

当时数据库show processlist输入如下

| 388671 | XXX          | 000:000  | xxx | Query       |   85945 | Sending data                                                          | create table test_xx2 as select * from test_xx1 a where  id =10000 

| 388716 | XXX          | 0007:0007 | NULL    | Query       |   85787 | Waiting for table metadata lock                                       | show create table `XXX`.`test_xx2`                                                         |
| 389593 | XXX          | 00032:600063  | NULL    | Query       |   82644 | Waiting for table metadata lock                                       | show create table `XXX`.`test_xx2`      

| 404002 | xxx       | 000019:40003  | NULL    | Query    |   29001 | Waiting for global read lock                                          | FLUSH TABLES WITH READ LOCK                                                                          |
| 404221 | xxx       | 10002:0        | xxx | Query       |   28939 | Waiting for global read lock                                          | UPDATE USERS A SET ....... 

尽量将长时间的DDL转变为DML语句，执行失败的DDL及时kill掉。没空了...稍后补充