一步步搞懂MySQL元数据锁（MDL）

2023年5月10日 82点热度 0人点赞 0条评论

某日，路上收到用户咨询，为了清除空间，想删除某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:  
 <span class="hljs-keyword">BEGIN;
 <span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO t ... ;
 <span class="hljs-keyword">COMMIT;  

 session2:  
 <span class="hljs-keyword">DROP <span class="hljs-keyword">TABLE t;</span></span></span></span></span></span>

然而上面的操作流程在binlog记录的顺序是

 <span class="hljs-keyword">DROP <span class="hljs-keyword">TABLE t; 
 <span class="hljs-keyword">BEGIN;  
 <span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO t ... ; 
 <span class="hljs-keyword">COMMIT;</span></span></span></span></span></span>

很显然备库执行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 ;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
<span class="hljs-params">| OBJECT_TYPE | OBJECT_SCHEMA      <span class="hljs-params">| OBJECT_NAME    | COLUMN_NAME <span class="hljs-params">| OBJECT_INSTANCE_BEGIN | LOCK_TYPE   <span class="hljs-params">| LOCK_DURATION | LOCK_STATUS <span class="hljs-params">| SOURCE            | OWNER_THREAD_ID <span class="hljs-params">| OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
<span class="hljs-params">| TABLE       | performance_schema <span class="hljs-params">| metadata_locks | NULL        <span class="hljs-params">|       139776223308432 | SHARED_READ <span class="hljs-params">| TRANSACTION   | GRANTED     <span class="hljs-params">| sql_parse.cc:6014 |              <span class="hljs-number">54 <span class="hljs-params">|             12 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
<span class="hljs-number">1 row <span class="hljs-keyword">in set (<span class="hljs-number">0.<span class="hljs-number">00 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

session2：执行简单查询，为了让表处于执行状态，这里使用了sleep函数。

mysql> <span class="hljs-built_in">select sleep(<span class="hljs-number">10) from t1;
+<span class="hljs-comment">-----------+
| sleep(<span class="hljs-number">10) |
+<span class="hljs-comment">-----------+
|         <span class="hljs-number">0 |
|         <span class="hljs-number">0 |
|         <span class="hljs-number">0 |
+<span class="hljs-comment">-----------+
<span class="hljs-number">3 rows <span class="hljs-keyword">in set (<span class="hljs-number">30.00 sec)</span></span></span></span></span></span></span></span></span></span></span></span>

session1：

mysql> select * from performance_schema.metadata_locks ;
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
<span class="hljs-params">| OBJECT_TYPE | OBJECT_SCHEMA      <span class="hljs-params">| OBJECT_NAME    | COLUMN_NAME <span class="hljs-params">| OBJECT_INSTANCE_BEGIN | LOCK_TYPE   <span class="hljs-params">| LOCK_DURATION | LOCK_STATUS <span class="hljs-params">| SOURCE            | OWNER_THREAD_ID <span class="hljs-params">| OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
<span class="hljs-params">| TABLE       | db1                <span class="hljs-params">| t1             | NULL        <span class="hljs-params">|       139776154308336 | SHARED_READ <span class="hljs-params">| TRANSACTION   | GRANTED     <span class="hljs-params">| sql_parse.cc:6014 |              <span class="hljs-number">53 <span class="hljs-params">|             22 |
<span class="hljs-params">| TABLE       | performance_schema <span class="hljs-params">| metadata_locks | NULL        <span class="hljs-params">|       139776223308432 | SHARED_READ <span class="hljs-params">| TRANSACTION   | GRANTED     <span class="hljs-params">| sql_parse.cc:6014 |              <span class="hljs-number">54 <span class="hljs-params">|             13 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
<span class="hljs-number">2 rows <span class="hljs-keyword">in set (<span class="hljs-number">0.<span class="hljs-number">00 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

此时再次查看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;
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
<span class="hljs-params">| Id | User            <span class="hljs-params">| Host      | db   <span class="hljs-params">| Command | Time   <span class="hljs-params">| State                           | Info                        <span class="hljs-params">|
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
|  <span class="hljs-number">4 <span class="hljs-params">| event_scheduler | localhost <span class="hljs-params">| NULL | Daemon  <span class="hljs-params">| 861577 | Waiting on empty queue          <span class="hljs-params">| NULL                        |
<span class="hljs-params">| 18 | root            <span class="hljs-params">| localhost | db1  <span class="hljs-params">| Sleep   |     <span class="hljs-number">50 <span class="hljs-params">|                                 | NULL                        <span class="hljs-params">|
| <span class="hljs-number">19 <span class="hljs-params">| root            | localhost <span class="hljs-params">| NULL | Query   <span class="hljs-params">|      0 | starting                        <span class="hljs-params">| show processlist            |
<span class="hljs-params">| 20 | root            <span class="hljs-params">| localhost | db1  <span class="hljs-params">| Query   |     <span class="hljs-number">11 <span class="hljs-params">| Waiting <span class="hljs-keyword">for table metadata lock | alter table t1 add col1 int <span class="hljs-params">|
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------+
4 rows <span class="hljs-keyword">in set (0.00 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

显然，id为20的线程还未执行alter操作，状态为‘Waiting for table metadata lock’，也就是在等待session2的sleep操作完成。

三、MDL为什么会造成系统崩溃？

举一个简单例子:

session1启动一个事务，对表t1执行一个简单的查询；
session2对t1加一个字段；
session3来对t1做一个查询；
session4来对t1做一个update;

各个session串行操作。

session1：

mysql> <span class="hljs-keyword">begin;
Query OK, <span class="hljs-number">0 rows affected (<span class="hljs-number">0.<span class="hljs-number">00 sec)


mysql> select * from t1 where id=<span class="hljs-number">1;
+----+------+------+-------+
<span class="hljs-params">| id | name <span class="hljs-params">| age  | birth <span class="hljs-params">|
+----+------+------+-------+
|  <span class="hljs-number">1 <span class="hljs-params">| aa   |   <span class="hljs-number">10 <span class="hljs-params">| NULL  |
+----+------+------+-------+
<span class="hljs-number">1 row <span class="hljs-keyword">in set (<span class="hljs-number">0.<span class="hljs-number">00 sec)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

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 <span class="hljs-string">'lock_wait_timeout'      ;
+<span class="hljs-comment">-------------------+----------+
| Variable_name     | Value    |
+<span class="hljs-comment">-------------------+----------+
| lock_wait_timeout | <span class="hljs-number">31536000 |
+<span class="hljs-comment">-------------------+----------+
<span class="hljs-number">1 row <span class="hljs-keyword">in set (<span class="hljs-number">0.00 sec)</span></span></span></span></span></span></span></span>

这么长的数字，掰着指头算了半天，居然真的是......一年，环游世界一圈回来还得接着等!!!

当然，生产环境中，我们很少会等待metadata lock超时，更多的是要想办法把产生metadata lock的源头找到，快速提交或者回滚，或者想办法kill掉。那么如何找到阻塞的源头呢？

五、如何快速找到阻塞源头？

快速解决问题永远是第一位的，一旦出现长时间的metadata lock，尤其是在访问频繁的业务表上产生，通常会导致表无法访问，读写全被阻塞，此时找到阻塞源头是第一位的。这里最重要的表就是前面提到过的
performance_schema.metadata_locks表。

metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。但5.7默认设置是关闭的（8.0默认打开），需要通过下面命令打开设置：

<span class="hljs-keyword">UPDATE performance_schema.setup_instruments <span class="hljs-keyword">SET ENABLED = <span class="hljs-string">'YES', TIMED = <span class="hljs-string">'YES'<span class="hljs-keyword">WHERE <span class="hljs-keyword">NAME = <span class="hljs-string">'wait/lock/metadata/sql/mdl';</span></span></span></span></span></span></span>

如果要永久生效，需要在配置文件中加入如下内容：

<span class="hljs-section">[mysqld]
<span class="hljs-attr">performance-schema-instrument=<span class="hljs-string">'wait/lock/metadata/sql/mdl=ON'</span></span></span>

单纯查询这个表无法得出具体的阻塞关系，也无法得知什么语句造成的阻塞，这里要关联另外两个表performance_schema.thread和
performance_schema.events_statements_history,thread表可以将线程id和show processlist中id关联，events_statements_history表可以得到事务的历史sql，关联后的完整sql如下：

<span class="hljs-keyword">SELECT
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,<span class="hljs-string">"transaction_begin;" ,<span class="hljs-number">-1) <span class="hljs-keyword">AS blocking_query,
    sql_kill_blocking_connection
<span class="hljs-keyword">FROM
    (
        <span class="hljs-keyword">SELECT
            b.OWNER_THREAD_ID <span class="hljs-keyword">AS granted_thread_id,
            a.OBJECT_SCHEMA <span class="hljs-keyword">AS locked_schema,
            a.OBJECT_NAME <span class="hljs-keyword">AS locked_table,
            <span class="hljs-string">"Metadata Lock" <span class="hljs-keyword">AS locked_type,
            c.PROCESSLIST_ID <span class="hljs-keyword">AS waiting_processlist_id,
            c.PROCESSLIST_TIME <span class="hljs-keyword">AS waiting_age,
            c.PROCESSLIST_INFO <span class="hljs-keyword">AS waiting_query,
            c.PROCESSLIST_STATE <span class="hljs-keyword">AS waiting_state,
            d.PROCESSLIST_ID <span class="hljs-keyword">AS blocking_processlist_id,
            d.PROCESSLIST_TIME <span class="hljs-keyword">AS blocking_age,
            d.PROCESSLIST_INFO <span class="hljs-keyword">AS blocking_query,
            <span class="hljs-keyword">concat(<span class="hljs-string">'KILL ', d.PROCESSLIST_ID) <span class="hljs-keyword">AS sql_kill_blocking_connection
        <span class="hljs-keyword">FROM
            performance_schema.metadata_locks a
        <span class="hljs-keyword">JOIN performance_schema.metadata_locks b <span class="hljs-keyword">ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
        <span class="hljs-keyword">AND a.OBJECT_NAME = b.OBJECT_NAME
        <span class="hljs-keyword">AND a.lock_status = <span class="hljs-string">'PENDING'
        <span class="hljs-keyword">AND b.lock_status = <span class="hljs-string">'GRANTED'
        <span class="hljs-keyword">AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
        <span class="hljs-keyword">AND a.lock_type = <span class="hljs-string">'EXCLUSIVE'
        <span class="hljs-keyword">JOIN performance_schema.threads c <span class="hljs-keyword">ON a.OWNER_THREAD_ID = c.THREAD_ID
        <span class="hljs-keyword">JOIN performance_schema.threads d <span class="hljs-keyword">ON b.OWNER_THREAD_ID = d.THREAD_ID
    ) t1,
    (
        <span class="hljs-keyword">SELECT
            thread_id,
            <span class="hljs-keyword">group_concat(   <span class="hljs-keyword">CASE <span class="hljs-keyword">WHEN EVENT_NAME = <span class="hljs-string">'statement/sql/begin' <span class="hljs-keyword">THEN <span class="hljs-string">"transaction_begin" <span class="hljs-keyword">ELSE sql_text <span class="hljs-keyword">END <span class="hljs-keyword">ORDER <span class="hljs-keyword">BY event_id SEPARATOR <span class="hljs-string">";" ) <span class="hljs-keyword">AS sql_text
        <span class="hljs-keyword">FROM
           performance_schema.events_statements_history
        <span class="hljs-keyword">GROUP <span class="hljs-keyword">BY thread_id
    ) t2
<span class="hljs-keyword">WHERE
    t1.granted_thread_id = t2.thread_id \G
   </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

对于前面的例子执行此sql，得到一个清晰的阻塞关系：

               <span class="hljs-attr">locked_schema: <span class="hljs-string">db1
                <span class="hljs-attr">locked_table: <span class="hljs-string">t1
                 <span class="hljs-attr">locked_type: <span class="hljs-string">Metadata <span class="hljs-string">Lock
      <span class="hljs-attr">waiting_processlist_id: <span class="hljs-number">28
                 <span class="hljs-attr">waiting_age: <span class="hljs-number">227
               <span class="hljs-attr">waiting_query: <span class="hljs-string">alter <span class="hljs-string">table <span class="hljs-string">t1 <span class="hljs-string">add <span class="hljs-string">cl3 <span class="hljs-string">int
               <span class="hljs-attr">waiting_state: <span class="hljs-string">Waiting <span class="hljs-string">for <span class="hljs-string">table <span class="hljs-string">metadata <span class="hljs-string">lock
     <span class="hljs-attr">blocking_processlist_id: <span class="hljs-number">27
                <span class="hljs-attr">blocking_age: <span class="hljs-number">252
              <span class="hljs-attr">blocking_query: <span class="hljs-string">select <span class="hljs-string">* <span class="hljs-string">from <span class="hljs-string">t1
<span class="hljs-attr">sql_kill_blocking_connection: <span class="hljs-string">KILL <span class="hljs-number">27
<span class="hljs-number">1 <span class="hljs-string">row <span class="hljs-string">in <span class="hljs-string">set, <span class="hljs-number">1 <span class="hljs-string">warning <span class="hljs-string">(0.00 <span class="hljs-string">sec)

</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

根据显示结果，processlist_id为27的线程阻塞了28的线程，我们需要kill 27即可解锁。

实际上，MySQL也提供了一个类似的视图来解决metadata lock问题，视图名称为sys.schema_table_lock_waits，但此视图查询结果有bug，不是很准确，建议大家还是参考上面sql。

六、本文开始的案例最终如何解决？

通过前面的介绍，本文开始的案例产生的过程就很简单了：用户执行了一个全表delete，在目标表上加了metadata读锁，由于表很大，读锁长时间无法释放，后来另外一个session执行了drop table操作，又需要在表上加metadata写锁，由于读写锁互相阻塞，drop操作只能等待delete操作完成才能获得写锁，因此从表面来看，二个命令都长时间没有响应，其实内部一个在执行，一个在等待。

那怎么来解决呢？因为从show processlist以及客户描述可以很清楚的知道故障机制，当时建议客户将delete操作kill掉，等数据回滚完后再执行drop操作因为delete已经执行了一段时间，回滚过程可能会较长，客户最终kill delete后顺利drop成功。

小结

生产环境大多是dml操作，metadata读锁之间不会产生锁等待，而目前MySQL的ddl操作大多可以online执行，因此即使有写锁，也会很快降级为读锁，所以ddl执行期间阻塞dml的几率也很小。最容易出现的情况是由于有未完成的事务，导致ddl metadata 写锁无法加上，只能在锁队列等待，而一旦进入锁队列，写锁又会阻塞其他的读锁，导致数据库连接快速增长，直至消耗殆尽，最终业务受到影响。

为了尽可能避免类似问题，下面是几个小建议：

生产环境的任何大表或频繁操作的小表，ddl都要非常慎重，最好在业务低峰期执行。
设计上要尽可能避免大事务，大事务不仅仅会带来各种锁问题，还好引起复制延迟/回滚空间爆满等各类问题。
要及时提交事务，经常发现客户端设置了事务手工提交，但sql执行后忘记点击提交按钮，导致事务长时间无法提交。建议监控实例中的长事务，避免由于各种原因导致事务没有及时提交。

作者：翟振兴

一步步搞懂MySQL元数据锁（MDL）

文章评论