MySQL修改表结构导致的表元数据锁(table metadata lock)
2018-04-25 17:37:12 
阿炯
只要有一个语句被堵住(Waiting for table metadata lock)就会导致后面的一系列查询(包括读)全部被堵。
为什么需要Metadata lock
Metadata lock介绍：参考官方手册：http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
MySQL 5.5.3 and up uses metadata locking to manage access to objects (tables, triggers, and so forth). Metadata locking is used to ensure data consistency but does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.
参考MySQL bug989
该bug是一个比较著名的问题：
我们知道，binlog内操作的记录是基于事务的提交顺序进行的，如果有一个事务未执行完成，而这个时候drop了表，这样在从库的执行顺序就会出现问题。
因此MySQL在5.5.3版本后引入了Metadata lock锁，事务释放后才会释放Metadata lock，这样在事务完成期间，是不能进行DDL操作的。为了在并发环境下维护表元数据的数据一致性，在表上有活动事务(显式或隐式)的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁(metadata lock)，来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。
所以在对表进行上述操作时，如果表上有活动事务(未提交或回滚)，请求写入的会话会等待在Metadata lock wait 。
注：支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。我们在执行DDL语句(包括添加字段、添加索引、更改数据类型等)的时候得事先看一下，进程中是否已经存在某些DML语句占用了表的元数据锁，这样会导致DDL语句处于锁等待状态。一旦出现Waiting for table metadata lock等待现象，后续所有对该表的访问都会阻塞在该等待上，包括读操作。
经常检查错误日志，日志中记录了信息以及任何不同寻常的东西，"mysqladmin debug" and innodb_lock_monitor中的额外数据都会记录在错误日志中。可以通过查询运行库的这些表得到一些锁相关的信息。
SELECT * FROM INFORMATION_SCHEMA.{INNODB_LOCKS,INNODB_LOCK_WAITS,INNODB_TRX}
在MySQL 5.1及以后的版本可用(且必须使用innodb插件)，由于大多数人将使用这个，他们包含非常有用的信息。经常出现metadata lock锁等待，导致后面的对这个表的所有操作(包括读)全部metadata lock等待。严重影响了数据库运行。且metadata lock锁等待不同于普通的行级锁，等待超时时间默认为365天，而普通的行级锁超时是120s。
> show variables like '%lock_wait%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 120 |
| lock_wait_timeout | 31536000 |
+--------------------------+----------+
因此如果metadata lock锁的源头不释放，则会一直阻塞，必须需要人为干预。
如何判断DDL被什么锁住了
这个从目前来看比较困难，不能直观的去判断，网上有人做了一个插件可以实现(在MariaDB 10中默认已提供类似功能)。
mysql> INSTALL SONAME 'metadata_lock_info';
具体的插件名称：metadata_lock_info.so
mysql> show plugins;
| METADATA_LOCK_INFO | ACTIVE | INFORMATION SCHEMA | metadata_lock_info.so | GPL |
SELECT * FROM information_schema.metadata_lock_info;
联查
SELECT CONCAT('Thread ',P.ID,' executing "',P.INFO,'" IS LOCKED BY Thread ',M.THREAD_ID) WhoLocksWho
FROM INFORMATION_SCHEMA.PROCESSLIST P,INFORMATION_SCHEMA.METADATA_LOCK_INFO M
WHERE LOCATE(lcase(LOCK_TYPE), lcase(STATE))>0;
示例，对一个写入很频繁且占用空间较大的表进行索引修改：
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51105377 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51105270 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51082670 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51104940 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51105201 |
...
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51105359 |
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51103795 |
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51104003 |
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51104150 |
METADATA_LOCK_INFO Plugin
导致Metadata Lock的场景
场景1：
会话1正在对表a进行DML操作(包括query)，这个时候会话2执行DDL操作，需要获取metadata独占锁，因此等待会话1。这个时候可以通过show processlist能查看该会话(该会话的state不会waiting for table metadata lock)
场景2：
会话1对表a进行DML(包括query)事务操作后，没有commit/rollback，这个时候show processlist是看到的只是会话处于sleep状态，执行的SQL显示为空。而这个时候会话2执行DDL操作，同样获取不到metadata独占锁，就会等待。
但是如果事务很多，则没办法判断是哪个会话导致。该场景最为普遍，而且是最频发，长事物运行，阻塞DDL，继而阻塞所有同表的后续操作。当前有对表的长时间查询或使用mysqldump时，使用alter会被堵住。
场景3：显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住。通过show processlist看不到表A上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对表A进行了一个失败的操作(比如查询了一个不存在的字段)，这时事务没有开始，但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。
官方手册上对此的说明如下：
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。
未提交事物，阻塞DDL，继而阻塞所有同表的后续操作，通过show
processlist看不到Table上有任何操作，但实际上存在有未提交的事务，可以在
information_schema.innodb_trx中查看到。在事务没有完成之前，Table上的锁不会释放，alter
table同样获取不到metadata的独占锁。
处理方法：通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。
那如果还是不能奏效呢？
处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session，也可以 kill 掉DDL所在的session。
那如果还是不能奏效呢？
先找找相关的库的语句：
select * from performance_schema.events_statements_current where CURRENT_SCHEMA='freeoa' and SQL_TEXT like '%key_refresh_token%' order by TIMER_WAIT desc \G
会有该库相关的查询记录，但所给出的线程id在目前运行的线程中是找不到的，因此，最有效的办法(但可能会导致数据丢失！)，查出该库上的所有连接然后终止它们：
mysql> select concat('KILL ',id,';') from information_schema.processlist where db='freeoa' into outfile '/tmp/ku.txt';
Query OK, 104 rows affected (0.01 sec)
mysql> source /tmp/ku.txt;
如下操作就会引起MDL：
创建、删除索引。
修改表结构。
表维护操作(optimize table、repair table等)。
删除表。
获取表上表级写锁 (lock table tab_name write)。
如何避免Metadata Lock
1.关注autocommit
autocommit分成2个层次：
数据库autocommit
客户端工具的autocommit
MySQL默认的autocommit为1，即自动提交，这种方式不太安全，因为事务默认不受人为控制，因此建议关闭autocommit。咨询了支付宝的MySQL DBA，支付宝的MySQL的autocommit全部是关闭的。
客户端工具分为2种，一种是继承数据库的autocommit模式，例如SQLyog、Mysql命令行接口；还有一种是自己独立的autocommit，例如MySQL workbench，设置工具本身的autocommit，而无视数据库层面autocommit。
无论是开发还是维护，一定要弄清楚自己的客户端的autocommit模式。无论使用客户端工具，首先要弄清楚当前环境下的autocommit方式是什么，如果不是autocommit，一定要确保所有的操作都需要显示的commit/rollback，否则即使是select查询某个表，甚至是语义(select一个错误的字段)报错，也会造成对其他会话对该表的DDL的metadata lock等待。
2.开发中注意事项
1、首先要确认驱动中的autocommit级别，例如JDBC中，默认conn.setAutoCommit()=true，当在该模式下，无论做select还是DML操作，均会自动提交，不会造成应用阻塞DDL操作。
2、当我们需要开启事务，设置conn.setAutoCommit(false)，任何SQL操作(包括读)操作后需要显式的调用conn.commit()，或者事务完成后conn.setAutoCommit(true)开启默认自动提交，才会释放元数据锁。
3、注意SQL执行后，一定要确保在很短的时间内显式commit/rollback或者conn.setAutoCommit(true)，做了相关测试，数据库的autocommit参数的设置结果，与应用中的conn.setAutoCommit(false/true)没有任何关系。
未提交事物或者长事务在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter
table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。
说说事务
事务由一条或者多条sql语句组成，在事务中的操作，这些sql语句要么都执行，要么都不执行，这就是事务的目的。对于事务而言，它需要满足ACID特性，下面就简要的说说事务的ACID特性。
A，表示原子性；原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态；
C，表示一致性；也就是说一致性指事务将数据库从一种状态转变为另一种一致的状态，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；
I，表示隔离性；隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现；
D，持久性，表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。
数据库的隔离级别
由于性能的考虑，许多数据库允许使用牺牲隔离属性来换取并发度，从而获取性能的提升。
Read uncommitted(RU)：读取未提交的数据(未授权读取)，即其他事务已经修改单未commit的数据，这是最低的隔离级别。允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
Read committed(RC)：允许不可重复读取，在一个事务中，对同一个项，前面的读取跟后面的读取结果可能不一样。例如第一次读取时另一个事务的修改还没有提交，第二次读取时已经提交了。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
Repeatable read(RR)：可重复读取，在一个事务中，对同一个项，前面的读取跟后面的读取结果一样。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。
Serializable(S)：可序列化，即数据库的事务市可串行化执行的，就像一个事务执行的时候没有别的事务同时在执行，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
隔离级别的降低可能导致读取到脏数据或事务执行异常
Lost update(LU) ：两个事务同时修改一个数据项，但后一个事务中途失败退出，则对数据项的两个修改可能都丢失。
Dirty Reads(DR)： 一个事务读取某数据项，但另一个事务更新了此数据项却没有提交，这样所有的操作可能都得回滚。
Non-repeatable Reads(BRR)：一个事务对同一数据项的多次读取可能得到不同的结果。
Second lost updates problem(SLU)：无法重复读取的特例，两个并发事务同时读取和修改同一数据项，则后面的修改可能使得前面的修改失效。
Phantom Reads(PR)：也称为幻读，例如在事务执行过程中，由于前面的查询和后面的查询的期间有另外一个事务插入数据，后面的查询结果中未出现的数据。
MySQL中使用事务
理论总结的再好，终归都要通过实践来进行理解。下面就来说说MySQL中是如何使用事务的。
在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
来看看我们可以使用哪些事务控制语句。
BEGIN或START TRANSACTION；显示地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
这些不用你“管”
有的时候有些SQL语句会产生一个隐式的提交操作，即执行完成这些语句后，会有一个隐式的COMMIT操作。有以下SQL语句，不用你去“管”：
DDL语句，ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等；
修改MYSQL架构的语句，CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD；
管理语句，ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE等。
以上的这些SQL操作都是隐式的提交操作，不需要手动显式提交。
事务的隔离级别
上面也说到了SET TRANSACTION用来设置事务的隔离级别。那事务的隔离级别是什么？在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。
InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE，这些隔离级别之间的区别如下：
脏读：一个事务读取到了另外一个事务没有提交的数据；比如：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了；
不可重复读：在同一事务中，两次读取同一数据，得到内容不同；比如：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化；
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同；比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。
隔离级别越低，事务请求的锁越少或保持锁的时间就越短。InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ；在这种默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE级别隔离。
我们可以可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意：默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别，你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。任何客户端都能自由改变会话隔离级别(甚至在事务的中间)，或者为下一个事务设置隔离级别。
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
为什么需要Metadata lock
Metadata lock介绍：参考官方手册：http://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
MySQL 5.5.3 and up uses metadata locking to manage access to objects (tables, triggers, and so forth). Metadata locking is used to ensure data consistency but does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.
参考MySQL bug989
该bug是一个比较著名的问题：
我们知道，binlog内操作的记录是基于事务的提交顺序进行的，如果有一个事务未执行完成，而这个时候drop了表，这样在从库的执行顺序就会出现问题。
因此MySQL在5.5.3版本后引入了Metadata lock锁，事务释放后才会释放Metadata lock，这样在事务完成期间，是不能进行DDL操作的。为了在并发环境下维护表元数据的数据一致性，在表上有活动事务(显式或隐式)的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁(metadata lock)，来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。
所以在对表进行上述操作时，如果表上有活动事务(未提交或回滚)，请求写入的会话会等待在Metadata lock wait 。
注：支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。我们在执行DDL语句(包括添加字段、添加索引、更改数据类型等)的时候得事先看一下，进程中是否已经存在某些DML语句占用了表的元数据锁，这样会导致DDL语句处于锁等待状态。一旦出现Waiting for table metadata lock等待现象，后续所有对该表的访问都会阻塞在该等待上，包括读操作。
经常检查错误日志，日志中记录了信息以及任何不同寻常的东西，"mysqladmin debug" and innodb_lock_monitor中的额外数据都会记录在错误日志中。可以通过查询运行库的这些表得到一些锁相关的信息。
SELECT * FROM INFORMATION_SCHEMA.{INNODB_LOCKS,INNODB_LOCK_WAITS,INNODB_TRX}
在MySQL 5.1及以后的版本可用(且必须使用innodb插件)，由于大多数人将使用这个，他们包含非常有用的信息。经常出现metadata lock锁等待，导致后面的对这个表的所有操作(包括读)全部metadata lock等待。严重影响了数据库运行。且metadata lock锁等待不同于普通的行级锁，等待超时时间默认为365天，而普通的行级锁超时是120s。
> show variables like '%lock_wait%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 120 |
| lock_wait_timeout | 31536000 |
+--------------------------+----------+
因此如果metadata lock锁的源头不释放，则会一直阻塞，必须需要人为干预。
如何判断DDL被什么锁住了
这个从目前来看比较困难，不能直观的去判断，网上有人做了一个插件可以实现(在MariaDB 10中默认已提供类似功能)。
mysql> INSTALL SONAME 'metadata_lock_info';
具体的插件名称：metadata_lock_info.so
mysql> show plugins;
| METADATA_LOCK_INFO | ACTIVE | INFORMATION SCHEMA | metadata_lock_info.so | GPL |
SELECT * FROM information_schema.metadata_lock_info;
联查
SELECT CONCAT('Thread ',P.ID,' executing "',P.INFO,'" IS LOCKED BY Thread ',M.THREAD_ID) WhoLocksWho
FROM INFORMATION_SCHEMA.PROCESSLIST P,INFORMATION_SCHEMA.METADATA_LOCK_INFO M
WHERE LOCATE(lcase(LOCK_TYPE), lcase(STATE))>0;
示例，对一个写入很频繁且占用空间较大的表进行索引修改：
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51105377 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51105270 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51082670 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51104940 |
| Thread 51104632 executing "alter table freeoa drop index url" IS LOCKED BY Thread 51105201 |
...
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51105359 |
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51103795 |
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51104003 |
| Thread 51104632 executing "alter table freeoa add index url(url(380))" IS LOCKED BY Thread 51104150 |
METADATA_LOCK_INFO Plugin
导致Metadata Lock的场景
场景1：
会话1正在对表a进行DML操作(包括query)，这个时候会话2执行DDL操作，需要获取metadata独占锁，因此等待会话1。这个时候可以通过show processlist能查看该会话(该会话的state不会waiting for table metadata lock)
场景2：
会话1对表a进行DML(包括query)事务操作后，没有commit/rollback，这个时候show processlist是看到的只是会话处于sleep状态，执行的SQL显示为空。而这个时候会话2执行DDL操作，同样获取不到metadata独占锁，就会等待。
但是如果事务很多，则没办法判断是哪个会话导致。该场景最为普遍，而且是最频发，长事物运行，阻塞DDL，继而阻塞所有同表的后续操作。当前有对表的长时间查询或使用mysqldump时，使用alter会被堵住。
场景3：显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住。通过show processlist看不到表A上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对表A进行了一个失败的操作(比如查询了一个不存在的字段)，这时事务没有开始，但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。
官方手册上对此的说明如下：
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。
未提交事物，阻塞DDL，继而阻塞所有同表的后续操作，通过show
processlist看不到Table上有任何操作，但实际上存在有未提交的事务，可以在
information_schema.innodb_trx中查看到。在事务没有完成之前，Table上的锁不会释放，alter
table同样获取不到metadata的独占锁。
处理方法：通过 select * from information_schema.innodb_trx\G, 找到未提交事物的sid, 然后 kill 掉，让其回滚。
那如果还是不能奏效呢？
处理方法：通过performance_schema.events_statements_current找到其sid, kill 掉该session，也可以 kill 掉DDL所在的session。
那如果还是不能奏效呢？
先找找相关的库的语句：
select * from performance_schema.events_statements_current where CURRENT_SCHEMA='freeoa' and SQL_TEXT like '%key_refresh_token%' order by TIMER_WAIT desc \G
会有该库相关的查询记录，但所给出的线程id在目前运行的线程中是找不到的，因此，最有效的办法(但可能会导致数据丢失！)，查出该库上的所有连接然后终止它们：
mysql> select concat('KILL ',id,';') from information_schema.processlist where db='freeoa' into outfile '/tmp/ku.txt';
Query OK, 104 rows affected (0.01 sec)
mysql> source /tmp/ku.txt;
如下操作就会引起MDL：
创建、删除索引。
修改表结构。
表维护操作(optimize table、repair table等)。
删除表。
获取表上表级写锁 (lock table tab_name write)。
如何避免Metadata Lock
1.关注autocommit
autocommit分成2个层次：
数据库autocommit
客户端工具的autocommit
MySQL默认的autocommit为1，即自动提交，这种方式不太安全，因为事务默认不受人为控制，因此建议关闭autocommit。咨询了支付宝的MySQL DBA，支付宝的MySQL的autocommit全部是关闭的。
客户端工具分为2种，一种是继承数据库的autocommit模式，例如SQLyog、Mysql命令行接口；还有一种是自己独立的autocommit，例如MySQL workbench，设置工具本身的autocommit，而无视数据库层面autocommit。
无论是开发还是维护，一定要弄清楚自己的客户端的autocommit模式。无论使用客户端工具，首先要弄清楚当前环境下的autocommit方式是什么，如果不是autocommit，一定要确保所有的操作都需要显示的commit/rollback，否则即使是select查询某个表，甚至是语义(select一个错误的字段)报错，也会造成对其他会话对该表的DDL的metadata lock等待。
2.开发中注意事项
1、首先要确认驱动中的autocommit级别，例如JDBC中，默认conn.setAutoCommit()=true，当在该模式下，无论做select还是DML操作，均会自动提交，不会造成应用阻塞DDL操作。
2、当我们需要开启事务，设置conn.setAutoCommit(false)，任何SQL操作(包括读)操作后需要显式的调用conn.commit()，或者事务完成后conn.setAutoCommit(true)开启默认自动提交，才会释放元数据锁。
3、注意SQL执行后，一定要确保在很短的时间内显式commit/rollback或者conn.setAutoCommit(true)，做了相关测试，数据库的autocommit参数的设置结果，与应用中的conn.setAutoCommit(false/true)没有任何关系。
未提交事物或者长事务在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter
table的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，避免长时间的metedata锁等待。
说说事务
事务由一条或者多条sql语句组成，在事务中的操作，这些sql语句要么都执行，要么都不执行，这就是事务的目的。对于事务而言，它需要满足ACID特性，下面就简要的说说事务的ACID特性。
A，表示原子性；原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态；
C，表示一致性；也就是说一致性指事务将数据库从一种状态转变为另一种一致的状态，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；
I，表示隔离性；隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现；
D，持久性，表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。
数据库的隔离级别
由于性能的考虑，许多数据库允许使用牺牲隔离属性来换取并发度，从而获取性能的提升。
Read uncommitted(RU)：读取未提交的数据(未授权读取)，即其他事务已经修改单未commit的数据，这是最低的隔离级别。允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。
Read committed(RC)：允许不可重复读取，在一个事务中，对同一个项，前面的读取跟后面的读取结果可能不一样。例如第一次读取时另一个事务的修改还没有提交，第二次读取时已经提交了。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
Repeatable read(RR)：可重复读取，在一个事务中，对同一个项，前面的读取跟后面的读取结果一样。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。
Serializable(S)：可序列化，即数据库的事务市可串行化执行的，就像一个事务执行的时候没有别的事务同时在执行，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
隔离级别的降低可能导致读取到脏数据或事务执行异常
Lost update(LU) ：两个事务同时修改一个数据项，但后一个事务中途失败退出，则对数据项的两个修改可能都丢失。
Dirty Reads(DR)： 一个事务读取某数据项，但另一个事务更新了此数据项却没有提交，这样所有的操作可能都得回滚。
Non-repeatable Reads(BRR)：一个事务对同一数据项的多次读取可能得到不同的结果。
Second lost updates problem(SLU)：无法重复读取的特例，两个并发事务同时读取和修改同一数据项，则后面的修改可能使得前面的修改失效。
Phantom Reads(PR)：也称为幻读，例如在事务执行过程中，由于前面的查询和后面的查询的期间有另外一个事务插入数据，后面的查询结果中未出现的数据。
MySQL中使用事务
理论总结的再好，终归都要通过实践来进行理解。下面就来说说MySQL中是如何使用事务的。
在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
来看看我们可以使用哪些事务控制语句。
BEGIN或START TRANSACTION；显示地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
这些不用你“管”
有的时候有些SQL语句会产生一个隐式的提交操作，即执行完成这些语句后，会有一个隐式的COMMIT操作。有以下SQL语句，不用你去“管”：
DDL语句，ALTER DATABASE、ALTER EVENT、ALTER PROCEDURE、ALTER TABLE、ALTER VIEW、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等；
修改MYSQL架构的语句，CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD；
管理语句，ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE等。
以上的这些SQL操作都是隐式的提交操作，不需要手动显式提交。
事务的隔离级别
上面也说到了SET TRANSACTION用来设置事务的隔离级别。那事务的隔离级别是什么？在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。
InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE，这些隔离级别之间的区别如下：
脏读：一个事务读取到了另外一个事务没有提交的数据；比如：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了；
不可重复读：在同一事务中，两次读取同一数据，得到内容不同；比如：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化；
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同；比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。
隔离级别越低，事务请求的锁越少或保持锁的时间就越短。InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ；在这种默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE级别隔离。
我们可以可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意：默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。如果使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别，你需要SUPER权限来做这个。使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。任何客户端都能自由改变会话隔离级别(甚至在事务的中间)，或者为下一个事务设置隔离级别。
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+