什么是元数据
描述数据库中的数据的数据都是元数据，如库名、表明、列名、版本名，和show语句展示的大多数内容都是元数据，以及在information_shema中记录数据库对象的表中的内容也是元数据
为什么MySQL要设置元数据锁
为了保证可以并发访问数据库对象及保证数据的一致性，所以应用metadata lock，如session1正在扫描t表数据，此会话持有t表的元数据锁，这时session2话尝试要drop t表，在尝试获取t表元数据锁的时候被阻塞，假如没有MDL的设计，那么在session1执行完之前session2将表drop掉，那么就会出现优化器在扫描到表一半时候突然抛出表不存在的异常
MDL的工作原理
在同一事务中操作事务表t和非事务表nt，如果此事务不结束，则另一会话对表t和nt的ddl操作都无法进行
如果事务中的语句通过语法校验但在执行的时候抛出异常，但是此事务不结束，对其涉及到的表的ddl操作也被阻塞，测试见例二
测试MDL
例一
# session114:14:13[test](;)> begin;Query OK, 0 rows affected (0.00 sec)14:14:18[test](;)> select * from t1 limit 1;+----+------+------+------+| id | col1 | col2 | col3 |+----+------+------+------+| 2 | 3 | 5 | NULL |+----+------+------+------+1 row in set (0.00 sec)#session214:14:48[test](;)> alter table t1 add col4 int;#session3 14:15:48[test](;)> show processlist; | 4834501 | root | localhost | test | Query | 14 | Waiting for table metadata lock | alter table t1 add col4 int |
例二
session114:28:36[test](;)> begin;Query OK, 0 rows affected (0.00 sec)14:28:40[test](;)> insert into t1(id) values(2); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'session214:28:40[test](;)> alter table t1 drop col4;session3| 4834501 | root | localhost | test | Query | 12 | Waiting for table metadata lock | alter table t1 drop col4 |
MDL的等待超时时长默认值为一年
14:17:32[(none)](;)> select @@lock_wait_timeout;+---------------------+| @@lock_wait_timeout |+---------------------+| 31536000 |+---------------------+1 row in set (0.00 sec)
建议
在操作频繁的时候尽量避免大表的DDL操作；MDL超时时长可以根据业务场景设置；有必要做MDL的监控报警
官方文档：https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html
补充
在autocommit 模式下，非显示开启事务，执行 lock tables ..... read 这类语句也会持有元数据锁，知道执行unlock table 或是会话中断
在这种情况下在information_schema.innodb_trx 表中是查不到它的信息的，这个时候就需要视版本来选择查看表锁信息的方式，如
session1[test] mysql> lock tables t2 read;Query OK, 0 rows affected (0.00 sec)
MySQL5.6 在session2会话中通过show open tables语句可以看出t2表正在被使用
session2[test] mysql> show open tables from test like 't2';+----------+-------+--------+-------------+| Database | Table | In_use | Name_locked |+----------+-------+--------+-------------+| test | t2 | 1 | 0 |+----------+-------+--------+-------------+1 row in set (0.00 sec)
当seesion1 执行 unlock tables 后t2上的元数据锁才会释放
MySQL5.7开始系统库performance_schema中新增表metadata_locks，默认不监控metadata lock，打开方式
mysql> UPDATE performance_schema.setup_instruments -> SET ENABLED = 'YES', TIMED = 'YES' -> WHERE NAME = 'wait/lock/metadata/sql/mdl';Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0select * from performance_schema.setup_instruments where NAME = 'wait/lock/metadata/sql/mdl';+----------------------------+---------+-------+| NAME | ENABLED | TIMED |+----------------------------+---------+-------+| wait/lock/metadata/sql/mdl | YES | YES |+----------------------------+---------+-------+1 row in set (0.00 sec)
同样会话session1 执行lock table ....后，会话session2 执行ddl 被挂起，此时查看metadata_locks
mysql> select * from metadata_locks;+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+| TABLE | test | t2 | 140433686616208 | SHARED_READ_ONLY | TRANSACTION | GRANTED | sql_parse.cc:6020 | 554 | 11 || GLOBAL | NULL | NULL | 140433489208736 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5533 | 556 | 18 || SCHEMA | test | NULL | 140433487872928 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5518 | 556 | 18 || TABLE | test | t2 | 140433487983984 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:6020 | 556 | 18 || TABLE | test | t2 | 140433487984224 | EXCLUSIVE | TRANSACTION | PENDING | mdl.cc:3919 | 556 | 18 || TABLE | performance_schema | metadata_locks | 140433765968400 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6020 | 555 | 95 |+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+6 rows in set (0.00 sec)
OWNER_EVENT_ID 对应的11 为session1 会话，18对应的是session2