某日，路上收到用户咨询，为了清除空间，想删除某200多G大表数据，且已经确认此表不再有业务访问，于是执行了一条命令‘delete from bigtable’，但好长时间也没删完，经过咨询后，获知drop table删除表速度快，而且能彻底释放空间，于是又在另外一个session中执行了‘drop table bigtable’命令，但是这个命令并没有快速返回结果，光标一直hang在原地不动。最后找我们协助，在登录数据库执行‘show processlist’后发现drop语句的状态是‘waiting for table metadata lock’，而之前执行的另外一个delete语句依旧能看到，状态为‘updating’，截图如下：
到底什么是metadata lock？这个锁等待是如何产生的？会带来什么影响？最后又如何来解决？今天我们挑6个常见问题给大家解答一下。
一、什么是metadata lock？
在MySQL5.5.3之前，有一个著名的bug#989，大致如下:
session1:
BEGIN ;
INSERT INTO t ... ;
COMMIT ;
session2:
DROP TABLE t;然而上面的操作流程在binlog记录的顺序是
DROP TABLE t;
BEGIN ;
INSERT INTO t ... ;
COMMIT ;很显然备库执行binlog时会先删除表t，然后执行insert 会报1032 error，导致复制中断。为了解决该bug,MySQL 在5.5.3引入了MDL锁（metadata lock），来保护 表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。
再举一个简单的例子，如果你在查询一个表的过程中，另外一个session对该表删除了一个列，那前面的查询到底该显示什么呢？如果在RR隔离级别下，事物中再次执行相同的语句还会和之前结果一致吗？为了防止这种情况，表查询开始MySQL会在表上加一个锁，来防止被别的session修改了表定义，这个锁就叫‘metadata lock’，简称MDL，翻译成中文也叫‘元数据锁’。
二、MDL和行锁有什么区别？
metadata lock是表级锁，是在server层加的，适用于所有存储引擎。 所有的dml操作都会在表上加一个metadata读锁；所有的ddl操作都会在表上加一个metadata写锁。读锁和写锁的阻塞关系如下：
读锁和写锁之间相互阻塞，即同一个表上的dml和ddl之间互相阻塞。 写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。 读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的dml之间的锁等待是innodb行锁引起的，和metadata lock无关。
熟悉innodb行锁的同学这里可能有点困惑，因为行锁分类和metadata lock很类似，也主要分为读锁和写锁，或者叫共享锁和排他锁，读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁，一个是行锁，且行锁中的读写操作对应在metadata lock中都属于读锁。
大家也许会奇怪，以前听说普通查询不加锁的，怎么这里又说要加表锁，我们做一个简单测试：
session1：查询前，先看一下metadata_locks表，这个表位于performance_schema下，记录了metadata lock的加锁信息。
mysql> select * from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | performance_schema | metadata_locks | NULL | 139776223308432 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6014 | 54 | 12 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)
session2：执行简单查询，为了让表处于执行状态，这里使用了sleep函数。
mysql> select sleep(10 ) from t1;
+
| sleep(10 ) |
+
| 0 |
| 0 |
| 0 |
+
3 rows in set (30.00 sec)session1：
mysql> select * from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | db1 | t1 | NULL | 139776154308336 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6014 | 53 | 22 |
| TABLE | performance_schema | metadata_locks | NULL | 139776223308432 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6014 | 54 | 13 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)此时再次查看metadata_lock表，发现多了一条t1的加锁记录，加锁类型为SHARED_READ,且状态是已授予（GRANTED)。大家通常理解的查询不加锁，是指不在表上加innodb行锁。
如果在执行sleep期间，另外一个session执行了一个加字段操作，此时就会产生metadata lock锁等待：
session2:
mysql> select sleep(10) from t1;
执行中......
session3：
mysql> alter table t1 add col1 int;
阻塞中......
session1:
mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 861577 | Waiting on empty queue | NULL |
| 18 | root | localhost | db1 | Sleep | 50 | | NULL |
| 19 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 20 | root | localhost | db1 | Query | 11 | Waiting for table metadata lock | alter table t1 add col1 int |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
4 rows in set (0.00 sec) 显然，id为20的线程还未执行alter操作，状态为‘Waiting for table metadata lock’，也就是在等待session2的sleep操作完成。
三、MDL为什么会造成系统崩溃？
举一个简单例子:
session1启动 一个事务，对表t1执行一个简单的查询； session2对t1加一个字段； session3来对t1做一个查询； session4来对t1做一个update; 各个session串行操作。
session1：
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where id=1 ;
+----+------+------+-------+
| id | name | age | birth |
+----+------+------+-------+
| 1 | aa | 10 | NULL |
+----+------+------+-------+
1 row in set (0.00 sec)session2：
mysql> alter table t1 add col1 int;
阻塞中...
session3:
mysql> select sleep(10) from t1 ;
阻塞中...
session4:
mysql> update t1 set name='aaaa' where id=2;
阻塞中...
也就是由于session1的一个事务没有提交，导致session2的ddl操作被阻塞，session3和session4本身不会被session1阻塞，但由于在锁队列中，session2排队更早，它准备加的是metadata lock写锁，阻塞了session3和session4的读锁。如果t1是一个执行频繁的表，show processlist会发现大量‘waiting for table metadata lock’的线程，数据库连接很快就会消耗完，导致业务系统无法正常响应。
此时如果session1提交，是session2的alter语句先执行还是session3和session4先执行呢？之前一直以为先到的先执行，当然是session2先执行，但经过测试，在5.7中，session3和session4先执行，session2最后执行，也就会出现alter长时间无法执行的情况；而在8.0中，session2先执行，session3和session4后执行，由于5.6以后ddl是online的，session2并不会阻塞session3和session4，感觉这样才是合理的，alter不会被‘饿死’。
四、MDL的生命周期有多长？
事务！事务！事务！ 重要的事情说三遍，表上的metadata lock的生命周期从事务中的第一条涉及自身的语句开始，到整个事务结束而结束。而5.5之前是基于语句的，事务中执行完语句就释放，如果此时另外一个session对表做了一个删字段操作，那么就会造成两个问题：
ddl操作如果先于事务完成，那么binlog中ddl就会排在事务之前，明显和逻辑不符，触发了本文开始提到的bug。 如果是RR隔离级别，那么事务中此表第二次执行将无法返回同样的结果，无法满足可重复读的要求。
所以，如果要降低metadata lock的锁等待时间，最好要及时提交事务，同时尽量避免大事务。
那么如果发生metadata lock锁等待，等待锁的session会等待多长时间呢？大家都知道MySQL里面行锁等待有个超时时间（参数innodb_lock_wait_timeout），默认50s。metadata lock也有类似参数控制 ：
mysql> show variables like 'lock_wait_timeout' ;
+
| Variable_name | Value |
+
| lock_wait_timeout | 31536000 |
+
1 row in set (0.00 sec)这么长的数字，掰着指头算了半天，居然真的是......一年，环游世界一圈回来还得接着等!!!
当然，生产环境中，我们很少会等待metadata lock超时，更多的是要想办法把产生metadata lock的源头找到，快速提交或者回滚，或者想办法kill掉。那么如何找到阻塞的源头呢？
五、如何快速找到阻塞源头？
快速解决问题永远是第一位的，一旦出现长时间的metadata lock，尤其是在访问频繁的业务表上产生，通常会导致表无法访问，读写全被阻塞，此时找到阻塞源头是第一位的。这里最重要的表就是前面提到过的
metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。但5.7默认设置是关闭的（8.0默认打开），需要通过下面命令打开设置：
关闭
站长推荐
/1 
