MYSQL 持续踩坑之-metadata lock

起因

最近生产的MySQL数据库出现锁表的情况，通过show processlist;发现下图状态（模拟）waiting for table metadata lock

在metadata lock之前，先大致回顾下MySQL的锁

从锁的粒度和范围，大致分三类：全局锁，表锁，行锁。
共享锁S，与排它锁X均为行锁。innodb支持对更粗粒度（数据库级，表级，页级）加意向锁。MYSQL意向共享锁IS及意向排他锁IX均属于表级锁。

IS、IX相互兼容，S兼容S、IS，X谁都不兼容
注：截图来源<高性能MySQL（第三版）>

接下来大致了解下metadata lock（MDL）

官网解释。先说下在MDL出现前的一个问题（MySQL5.5之前），假定现在有事务A正在执行DML（insert）操作且尚未提交（执行时间长，大事务）,此时另外的session开始事务B（alter某个表字段），执行DDL操作，由于事务按提交顺序执行，事务A并不会阻止事务B的提交，因此先提交执行事务B，此时表结构发生变化，事务A提交出错。
MDL的出现就是为了解决此类DML操作与DDL操作之间不协调导致的问题。同样是上面的事务A事务B，事务A在执行DML操作时先获取MDL（假设类型1），此时，事务A不提交，则事务B无法获取MDL（假设类型2，且与类型1互斥）事务B无法提交，这时如果有事务C也是DML操作，则可以获取和类型1一样的共享锁，则C可以提交。等到事务A提交或者回滚释放DML后，事务B才能提交DDL操作。
MDL最大的作用应该就是保护一个处于事务中的表的结构不被修改。另外需要注意的是，MDL不需要显式使用。
MDL也有共享锁和排它锁之分，类型繁多，下面用其中的几个类型做示例，有兴趣可以尝试按照相同的方式构造对应场景查看更多的MDL锁类型。

场景示例

首先在查看表metadata_locks前需要做如下设置：
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';

场景1、验证DML操作与DDL操作的影响

开启sessionA，执行begin;SELECT * FROM test.my_test;

（begin表示开启事务，没有commit不会提交，不需要设置autocommit=0；）

此时执行select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;查看未提交的事务。

图中的trx_mysql_thread_id就是没有提交的事务ID。

开启sessionB，执行truncate table my_test;

此时可以看到，该DDL无法提交一直在running。

执行show processlist;查看

出现waiting for table metadata lock；

执行mysql> select * from performance_schema.metadata_locks\G;
请看黑框中的注释：

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
****1.row表示sessionA的DML操作，此时获取到MDL的SHARED_READ锁*****
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235208064
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 92
       OWNER_EVENT_ID: 17
*************************** 2. row ***************************
*****执行DDL操作前，获取到MDL的全局意向排它锁INTENTION_EXCLUSIVE******
          OBJECT_TYPE: GLOBAL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 235207104
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: STATEMENT
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 91
       OWNER_EVENT_ID: 89
*************************** 3. row ***************************
***执行DDL操作前，获取到MDL的SCHEMA级别的意向排它锁INTENTION_EXCLUSIVE***
          OBJECT_TYPE: SCHEMA
        OBJECT_SCHEMA: test
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 235208256
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 91
       OWNER_EVENT_ID: 89
*************************** 4. row ***************************
****DDL操作等待获取MDL的排它锁EXCLUSIVE****
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235206720
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE:
      OWNER_THREAD_ID: 91
       OWNER_EVENT_ID: 89
*************************** 5. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 235208160
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 93
       OWNER_EVENT_ID: 3
5 rows in set (0.00 sec)

切回sessionA 执行commit；

再切到sessionB 查看

在400多秒sessionA提交后（或者kill掉没有提交的事务ID），sessionB完成了DDL操作。

再次执行select * from performance_schema.metadata_locks\G;
这时已经没有了my_test相关的锁信息了，同时waiting for table metadata lock也没有了。

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 235208256
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 93
       OWNER_EVENT_ID: 4
1 row in set (0.00 sec)

由此可以看出当sessionA的DML操作尚未提交事务时，会获取MDL共享读锁，该锁是表级别，直接作用在my_test上；当sessionB 执行DDL操作时需要在my_test上获取MDL排它锁，该锁与MDL的共享读锁互斥，因此sessionB的DDL一直在等待获取MDL排它锁，直到sessionA的事务释放MDL共享读锁。

2、DML操作与DML操作的影响

先确保1中的所有事务已提交；

如1中sessionA执行begin;SELECT * FROM test.my_test;，同时开启sessionC 执行begin;insert into my_test values('1','ye','18');

此时两个事务均未提交

查看

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
****sessionA 获取到MDL的SHARED_READ，为GRANTED状态****
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235207104
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 94
       OWNER_EVENT_ID: 20
*************************** 2. row ***************************
****sessionC 获取到MDL的SHARED_WRITE，为GRANTED状态****
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235208064
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 90
       OWNER_EVENT_ID: 119
*************************** 3. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 235208160
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 93
       OWNER_EVENT_ID: 5

此时先提交sessionC的事务；执行后只剩sessionA的未提交事务（同时开始事务时间确认是A还是C）

由此看出DML操作之间的事务获取到的MDL共享读写锁之间可以兼容。即便sessionA比sessionC先开启事务，C也可以先提交事务。

3、当DML+DDL+DML的影响

操作流程和1前面的的几个步骤一样，开启sessionA，执行begin;SELECT * FROM test.my_test;不提交

开启sessionB，执行truncate table my_test;

再开启sessionC，直接执行SELECT * FROM test.my_test;

sessionA

sessionB

sessionC

查看show processlist;

查看mysql> select * from performance_schema.metadata_locks\G；
1-4.row的不注释了，跟场景1中的一样，从5.row

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235207104
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 94
       OWNER_EVENT_ID: 25
*************************** 2. row ***************************
          OBJECT_TYPE: GLOBAL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 235208064
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: STATEMENT
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 91
       OWNER_EVENT_ID: 100
*************************** 3. row ***************************
          OBJECT_TYPE: SCHEMA
        OBJECT_SCHEMA: test
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 235208160
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 91
       OWNER_EVENT_ID: 100
*************************** 4. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235206720
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE:
      OWNER_THREAD_ID: 91
       OWNER_EVENT_ID: 100
*************************** 5. row ***************************
***由于4.row在pending中，此时sessionC的也得等sessionB获取
***MDL锁提交事务后才能获取MDL的SHARED_READ，因此也在pending状态***
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235208256
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE:
      OWNER_THREAD_ID: 88
       OWNER_EVENT_ID: 105
*************************** 6. row ***************************
***获取写锁（共享或者排他）前，获取全局的意向排他锁IX，IX间不会互斥
          OBJECT_TYPE: GLOBAL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 235207296
            LOCK_TYPE: INTENTION_EXCLUSIVE
        LOCK_DURATION: STATEMENT
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 90
       OWNER_EVENT_ID: 125
*************************** 7. row ***************************
***此处是另一个sessionD，insert into my_test values('2','ye','18');
***也在等待获取MDL的SHARED_WRITE，获取SHARED_WRITE先要获取全局的意向排他锁IX（6.row）

          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235208928
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE:
      OWNER_THREAD_ID: 90
       OWNER_EVENT_ID: 125
*************************** 8. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 235206912
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 93
       OWNER_EVENT_ID: 7
8 rows in set (0.00 sec)

-此时kill掉sessionB的事务ID；kill 66；sessionA仍不提交，查看

sessionB

sessionC

sessionD

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: my_test
OBJECT_INSTANCE_BEGIN: 235207104
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 94
       OWNER_EVENT_ID: 25
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 235208928
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE:
      OWNER_THREAD_ID: 93
       OWNER_EVENT_ID: 8
2 rows in set (0.00 sec)

由此可以看出当出现DDL被阻塞无法获取到MDL锁之后，后续的DML操作也需要等待，无法获取到MDL锁，即便是和sessionA的MDL锁类型兼容。sessionC和SessionD，只能等sessionA与sessionB提交后或者直接kill掉sessionB的事务ID才能获取到MDL锁。

更多的锁类型可以参考这里不同的sql可能获取的MDL锁不一样

建议：

1、耗时长的DML操作需要注意和DDL操作分隔开，当出现waiting for table metadata lock将影响本可正常执行的后续DML操作，此时可手动kill掉DDL的事务ID，使后续的DML操作正常进行，DDL是一定要等到没有session持有MDL锁才会开始执行。
2、避免大事务，导致DML操作时间过长，优化慢sql;

3、应用执行DDL时可以添加超时限制，eg:set lock_wait_timeout = 5;truncate table my_test;该session在无法获取MDL锁5秒后抛异常。

参考：
https://dev.mysql.com/doc/refman/5.7/en/metadata-locking.html
https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
https://www.cnblogs.com/chenpingzhao/p/9642732.html
http://blog.itpub.net/29896444/viewspace-2101567/
https://blog.csdn.net/michaelyang_yz/article/details/79462330