技术分享 | MySQL 的 MDL 锁解惑

MySQL 5.7 中，performance_schema 库中新增了 metadata_locks 表，专门记录MDL的相关信息，但在5.7中默认关闭(8.0默认打开)，

mysql> select * from performance_schema.setup_instruments \G;...NAME: wait/lock/metadata/sql/mdlENABLED: NOTIMED: NO

为了更直观了解MDL锁，首先要开启MDL锁记录，执行如下SQL开启，此时就可以通过关联 metadata_locks 和 threads 等，观测 MDL 的相关信息，

mysql> update performance_schema.setup_instruments set enabled='YES',TIMED='YES'    -> where name='wait/lock/metadata/sql/mdl';Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0

场景1 会话1执行 SELECT 操作

为了能关注到锁的信息，使用 sleep 函数，

mysql> select sleep(10) from t;+-----------+| sleep(10) |+-----------+|         0 ||         0 |+-----------+2 rows in set (20.01 sec)

processlist 显示的是 user sleep ，

执行如下SQL，

select m.*, t.* from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id = t.thread_id\G;

可以看到这条 SQL 当前正在持有 SHARED_READ 类型的锁，说明 SELECT 读操作，实际上需要锁，

场景2 会话1在事务中执行 SELECT 操作

执行 BEGIN ，再执行 SELECT ，但是不提交，

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)

此时检索 metadata_locks 和 threads ，SELECT 当前还是持有 SHARED_READ 类型的锁，

场景3 事务中执行 INSERT

开启一个事务，执行插入 INSERT 操作，但是不提交，

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(3);Query OK, 1 row affected (0.01 sec)

此时检索 metadata_locks 和 threads ，INSERT 当前是持有 SHARED_WRITE 类型的锁，

场景4 执行 alter table

场景3插入操作不提交的情况下，会话2执行 alter table ，此时就出现 hang ，

alter table t add c varchar(1);

processlist 显示 alter table 操作等待 Waiting for table metadata lock ，

此时检索 metadata_locks 和 threads，alter table 依次持有了以下几种锁，INTENTION_EXCLUSIVE、SHARED_UPGRADABLE、EXCLUSIVE，

          OBJECT_TYPE: GLOBAL        OBJECT_SCHEMA: NULL          OBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: 140512405331056            LOCK_TYPE: INTENTION_EXCLUSIVE        LOCK_DURATION: STATEMENT          LOCK_STATUS: GRANTED               SOURCE:       OWNER_THREAD_ID: 34       OWNER_EVENT_ID: 21            THREAD_ID: 34                 NAME: thread/sql/one_connection                 TYPE: FOREGROUND       PROCESSLIST_ID: 8     PROCESSLIST_USER: root     PROCESSLIST_HOST: localhost       PROCESSLIST_DB: bisal  PROCESSLIST_COMMAND: Query     PROCESSLIST_TIME: 5    PROCESSLIST_STATE: Waiting for table metadata lock     PROCESSLIST_INFO: alter table t add c varchar(1)     PARENT_THREAD_ID: 1                 ROLE: NULL         INSTRUMENTED: YES              HISTORY: YES      CONNECTION_TYPE: Socket         THREAD_OS_ID: 6836*************************** 5. row ***************************          OBJECT_TYPE: SCHEMA        OBJECT_SCHEMA: bisal          OBJECT_NAME: NULLOBJECT_INSTANCE_BEGIN: 140512405285536            LOCK_TYPE: INTENTION_EXCLUSIVE        LOCK_DURATION: TRANSACTION          LOCK_STATUS: GRANTED               SOURCE:       OWNER_THREAD_ID: 34       OWNER_EVENT_ID: 21            THREAD_ID: 34                 NAME: thread/sql/one_connection                 TYPE: FOREGROUND       PROCESSLIST_ID: 8     PROCESSLIST_USER: root     PROCESSLIST_HOST: localhost       PROCESSLIST_DB: bisal  PROCESSLIST_COMMAND: Query     PROCESSLIST_TIME: 5    PROCESSLIST_STATE: Waiting for table metadata lock     PROCESSLIST_INFO: alter table t add c varchar(1)     PARENT_THREAD_ID: 1                 ROLE: NULL         INSTRUMENTED: YES              HISTORY: YES      CONNECTION_TYPE: Socket         THREAD_OS_ID: 6836*************************** 6. row ***************************          OBJECT_TYPE: TABLE        OBJECT_SCHEMA: bisal          OBJECT_NAME: tOBJECT_INSTANCE_BEGIN: 140512405330960            LOCK_TYPE: SHARED_UPGRADABLE        LOCK_DURATION: TRANSACTION          LOCK_STATUS: GRANTED               SOURCE:       OWNER_THREAD_ID: 34       OWNER_EVENT_ID: 21            THREAD_ID: 34                 NAME: thread/sql/one_connection                 TYPE: FOREGROUND       PROCESSLIST_ID: 8     PROCESSLIST_USER: root     PROCESSLIST_HOST: localhost       PROCESSLIST_DB: bisal  PROCESSLIST_COMMAND: Query     PROCESSLIST_TIME: 5    PROCESSLIST_STATE: Waiting for table metadata lock     PROCESSLIST_INFO: alter table t add c varchar(1)     PARENT_THREAD_ID: 1                 ROLE: NULL         INSTRUMENTED: YES              HISTORY: YES      CONNECTION_TYPE: Socket         THREAD_OS_ID: 6836*************************** 7. row ***************************          OBJECT_TYPE: TABLE        OBJECT_SCHEMA: bisal          OBJECT_NAME: tOBJECT_INSTANCE_BEGIN: 140512406836304            LOCK_TYPE: EXCLUSIVE        LOCK_DURATION: TRANSACTION          LOCK_STATUS: PENDING               SOURCE:       OWNER_THREAD_ID: 34       OWNER_EVENT_ID: 21            THREAD_ID: 34                 NAME: thread/sql/one_connection                 TYPE: FOREGROUND       PROCESSLIST_ID: 8     PROCESSLIST_USER: root     PROCESSLIST_HOST: localhost       PROCESSLIST_DB: bisal  PROCESSLIST_COMMAND: Query     PROCESSLIST_TIME: 5    PROCESSLIST_STATE: Waiting for table metadata lock     PROCESSLIST_INFO: alter table t add c varchar(1)     PARENT_THREAD_ID: 1                 ROLE: NULL         INSTRUMENTED: YES              HISTORY: YES      CONNECTION_TYPE: Socket         THREAD_OS_ID: 6836

场景5 执行SELECT

场景3和4的前提下，会话3执行 SELECT ，同样出现了 hang ，

mysql> select * from t;

processlist 显示 SELECT 的状态，Waiting for table metadata lock，

此时检索 metadata_locks 和 threads，SELECT 当前还是持有 SHARED_READ 锁，进程状态是 Waiting for table metadata lock ，

场景6 提交会话1

执行 commit 或者 rollback ，提交会话1的事务，此时会话2的 DDL ，

alter table t add c varchar(1);

会话3的查询，

mysql> select * from t;

都可以执行了，processlist 没有任何的等待，而且 metadata_locks 和 threads 关联检索都是空的了，

从以上的6个场景，我们能得到一些表象信息，

(1) 即使普通的 SELECT 查询操作，都是要持有锁的，类型是 SHARED_READ 。

(2) 如果当前有未提交的活动事务，不允许执行 alter table 这种 DDL 。

(3) 如果当前 DDL 是 hang ，其他会话哪怕只是查询 SELECT ，都会 hang 。

MySQL 是 Server-Engine 架构，MDL锁是在 Server 层实现的表级锁，适用于所有存储引擎。MDL的读锁和写锁的阻塞关系如下，

(1) 读锁和写锁之间相互阻塞，即同一个表上的 DML 和 DDL 之间互相阻塞。这就是上面提到的表象1，以及场景4。

(2) 写锁和写锁之间互相阻塞，即两个 session 不能对表同时做表定义变更，需要串行操作。这个很容易理解。

(3) 读锁和读锁之间不会产生阻塞。就是说增删改查不会因为 metadata lock 产生阻塞，可以并发执行，如果不是这样，数据库就是串行操作了。

如果出现 metadata lock 锁等待，对应的会话需要等待的时间，是受 lock_wait_timeout 参数控制的，默认值是 31536000 ，换算一下，是1年，因此，很少一直等待MDL锁超时的，而是要找到MDL产生的源头，提交、回滚，或者直接 kill 掉。降低 lock_wait_timeout 的值，只能让等待 metadata lock 的会话更快超时，并未从根上解决问题，还是得针对具体的场景，找到合适的方案，

mysql> show variables like 'lock_wait_timeout';+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| lock_wait_timeout | 31536000 |+-------------------+----------+1 row in set (0.31 sec)

为了找到 metadata lock 的源头，可以关联 performance_schema 的 metadata_locks 、threads 和 events_statements_history ，梳理出关系。例如针对上述的场景4，执行SQL ，

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

可以得到非常具体的锁等待关系，而且给出了快速解锁的指令，

*************************** 1. row ***************************               locked_schema: bisal                locked_table: t                 locked_type: Metadata Lock      waiting_processlist_id: 8                 waiting_age: 168               waiting_query: alter table t add c varchar(1)               waiting_state: Waiting for table metadata lock     blocking_processlist_id: 7                blocking_age: 172              blocking_query: insert into t values(3)sql_kill_blocking_connection: KILL 71 row in set, 1 warning (0.01 sec)

我们在系统设计的时候，还是要注意，规范使用事务，合理控制事务的粒度，避免出现大事务，另外，如果通过图形化工具操作数据库，避免开启事务，但忘了关的情况，这些都可能造成MDL锁等待的出现，进而影响业务。