共计 8305 个字符，预计需要花费 21 分钟才能阅读完成。
一、Metadata lock
MySQL 使用 DML 来管理对数据库对象的并发访问，并确保数据一致性。DML 不仅适用于表，还适用于模式和存储程序(过程、函数、触发器和计划的事件)
1.1 MDL 简述
为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从 MySQL5.5 版本开始引入了 MDL 锁，来保护表的元数据信息，用于解决或者保证 DDL 操作与 DML 操作之间的一致性。
元数据锁的获取不依赖于使用的引擎，无论使用的是设置 autocommit= 0 的 MyISAM 引擎还是用 begin 或 start transaction 语句显示声名的事务，连接都会获取元数据锁。一旦出现 Metadata Lock Wait 等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
1.2 MDL 解决的问题
Metadata lock 是 MySQL 在 5.5.3 版本后引入了，为的是防止 5.5.3 以前的一个 bug 的出现：
当一个会话在主库执行 DML 操作还没提交时，另一个会话对同一个对象执行了 DDL 操作如 drop table，而由于 MySQL 的 binlog 是基于事务提交的先后顺序进行记录的，因此在 slave 上应用时，就出现了先 drop table，然后再向 table 中 insert 的情况，导致从库应用出错。
对于引入 MDL，其主要解决了 2 个问题：
一个是事务隔离问题，比如在可重复隔离级别下，会话 A 在 2 次查询期间，会话 B 对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；
另外一个是数据复制的问题，比如会话 A 执行了多条更新语句期间，另外一个会话 B 做了表结构变更并且先提交，就会导致 slave 在重做时，先重做 alter，再重做 update 时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait。
二、常见 MDL 锁场景：
①当前有执行 DML 操作（DML 未执行完成）时，执行 DDL 操作
② 当前有对表的长时间查询或使用 mysqldump/mysqlpump 时，执行 DDL 会被堵住
③ 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL 会被堵住
④ 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时 DDL 仍然会被堵住
三、例子
mysql 版本：5.6.29
隔离级别：READ COMMITTED
3.1 场景 1
(1) 现象模拟
| 事务 1 | 事务 2 | 事务 3 |
|---|---|---|
| begin; | ||
| select * from base_code; | ||
| – | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT ‘ 编码 ’;——执行被阻塞 | |
| – | – | select * from base_code;——执行被阻塞 |
(2) show processlist 查看结果如下:
mysql> show processlist;
+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 20309270 | Waiting on empty queue | NULL |
| 98456 | root | localhost | lcl_abc | Sleep | 85 | | NULL |
| 98459 | root | localhost | lcl_abc | Query | 79 | Waiting for table metadata lock | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码' |
| 98461 | root | localhost | lcl_abc | Query | 51 | Waiting for table metadata lock | select * from base_code |
| 98462 | root | localhost | NULL | Query | 0 | init | show processlist |
+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
select * from base_code; 再次查询 base_code 表也是 Waiting for table metadata lock 状态，说明由于 metadata lock 的存在，会导致后面正常的查询都会因为等待锁而阻塞。
如果先执行事务 3，是可以查询的。执行完事务 2，造成阻塞后，才会阻塞后续所有的操作。
(3) 查看当前事务运行状态:
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 339395
trx_state: RUNNING
trx_started: 2020-04-14 16:51:43
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 98456
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 488
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.17 sec)可以看到，事务 1 由于还没提交，所以这里能看到它的状态还是 running.
但是这里我们看不到正在执行的语句，不知道到底是什么语句导致的。
(4) 查看该事务对应的进程
mysql> select * from information_schema.processlist where id=98456;
+-------+------+-----------+---------+---------+------+-------+------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-------+------+-----------+---------+---------+------+-------+------+
| 98456 | root | localhost | lcl_abc | Sleep | 1107 | | NULL |
+-------+------+-----------+---------+---------+------+-------+------+
1 row in set (0.00 sec)只能根据 trx_mysql_thread_id 看到未提交的事务的 process id，看一下 processlist，INFO 内也没有具体内容。
此时可以通过 performance_schema.events_statements_current 来查看到对应的 sql，包括已经执行完，但没有提交的。
mysql> SELECT b.processlist_id, c.db, a.sql_text, c.command, c.time, c.state FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id) JOIN information_schema.processlist c ON b.processlist_id = c.id WHERE a.sql_text NOT LIKE '%performance%';
+----------------+---------+------------------------------------------------------------------------------------+---------+------+---------------------------------+
| processlist_id | db | sql_text | command | time | state |
+----------------+---------+------------------------------------------------------------------------------------+---------+------+---------------------------------+
| 98459 | lcl_abc | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码' | Query | 636 | Waiting for table metadata lock |
| 98461 | lcl_abc | select * from base_code | Query | 632 | Waiting for table metadata lock |
| 98456 | lcl_abc | select * from base_code | Sleep | 639 | |
+----------------+---------+------------------------------------------------------------------------------------+---------+------+---------------------------------+
3 rows in set (0.05 sec)
(5) 提交或关闭造成 DML 锁的进程
提交或者 kill 98456 后，可以看到事务 2、事务 3 立马执行完了
mysql> alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码';
Query OK, 7 rows affected (21 min 58.00 sec)
Records: 7 Duplicates: 0 Warnings: 03.2 场景 2：
事务 1, 开启事务，执行语句报错，其他语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉
(1) 现象模拟
| 事务 1 | 事务 2 | 事务 3 |
|---|---|---|
| begin; | ||
| update base_code set num2=1 where id=1;——ERROR 1054 (42S22): Unknown column ‘num2’ in ‘field list’ | ||
| – | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT ‘ 编码 ’;——执行被阻塞 | |
| – | – | select * from base_code;——执行被阻塞 |
由于 num2 字段不存在，事务 1 执行报错，导致 update 执行失败，但是没有提交该事务，此时依然会造成 alter 语句阻塞，以后后续的 select 阻塞。
(2)show processlist 查看结果如下：
mysql> show processlist;
+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 20314642 | Waiting on empty queue | NULL |
| 98456 | root | localhost | lcl_abc | Sleep | 1030 | | NULL |
| 98459 | root | localhost | lcl_abc | Query | 983 | Waiting for table metadata lock | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码' |
| 98461 | root | localhost | lcl_abc | Query | 3 | Waiting for table metadata lock | select * from base_code |
| 98462 | root | localhost | NULL | Query | 0 | init | show processlist |
+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)(3) 查看当前事务运行状态:
在 information_schema.innodb_trx 中也没有任何进行中的事务. 需查询表 performance_schema.events_statements_current，该表可以看到对应的 sql，包括已经执行完，但没有提交的
缺陷：一个事务可能有一组 sql 组成，这个方法只能看到这个事务最后执行的是什么 SQL，无法看到全部。(假如事务 1，执行完 update 后又执行了一个 select，则 events_statements_current 表中只能看到最后执行的 select 语句)
mysql> SELECT b.processlist_id, c.db, a.sql_text, c.command, c.time, c.state FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id) JOIN information_schema.processlist c ON b.processlist_id = c.id WHERE a.sql_text NOT LIKE '%performance%';
+----------------+---------+------------------------------------------------------------------------------------+---------+------+---------------------------------+
| processlist_id | db | sql_text | command | time | state |
+----------------+---------+------------------------------------------------------------------------------------+---------+------+---------------------------------+
| 98459 | lcl_abc | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码' | Query | 636 | Waiting for table metadata lock |
| 98461 | lcl_abc | select * from base_code | Query | 632 | Waiting for table metadata lock |
| 98463 | lcl_abc | update base_code set num2=1 where id=1 | Sleep | 639 | |
+----------------+---------+------------------------------------------------------------------------------------+---------+------+---------------------------------+
3 rows in set (0.05 sec)
四、参数
可以通过 lock_wait_timeout 变量来指定超时时间，默认是 31536000 秒（一年），所以锁住的查询永远不会终止。
五、总结
- 为了事务的串行话，和数据一致性，Mysql 会对打开事务进行 DML 的表加上 table metadata lock, 在事务提交前，其他的 DDL 操作会阻塞
- 对于主要是查询数据的项目来说，默认不开启事务即可，如果确实需要，程序上手动开启事务
- 需要使用到事务时，也要尽量缩小事务的运行时间，一个事务中不要包含太多的语句
- 程序上对任何错误异常状况一定要捕捉后，回滚事务，否则事务脱离程序，只能等事务自己超时，手动关闭事务或者重启服务释放锁了
六、查找未提交事务的 sql 的方法
(1) 表 performance_schema.events_statements_current
缺陷：一个事务可能有一组 sql 组成，这个方法只能看到这个事务最后执行的是什么 SQL，无法看到全部。(假如事务 1，执行完 update 后又执行了一个 select，则 events_statements_current 表中只能看到最后执行的 select 语句)
(2) general_log
即使事务没有提交，一样会写到 general_log.
缺陷：一般情况下 general_log 不大可能打开.
(3) commit 后，查看 binlog
假如后面应用层最终 commit 了，那么会在 binlog 里记录，可以根据当时的 tread_id 去 binlog 查看
缺陷：不会记录 select、执行失败的语句。
