前言
在MySQL 5.7中，可以通过查询performance_schema库中的metadata_locks表来查看数据库MDL锁的情况，而用户就需要手动查询performance_schema.metadata_locks表中的MDL锁信息，可能会存在这些痛点：
1、查询效率低下：performance_schema.metadata_locks表中包含了大量的MDL锁信息，用户需要手动查询才能获取想要的信息，这样效率非常低下，尤其在MDL锁数量较多的情况下。
2、难以分析MDL锁信息：由于MDL锁的特殊性，它的信息比较复杂，包含了很多字段，用户需要对每一条MDL锁的信息进行分析，才能了解其对数据库的影响。这样会增加用户的工作量，同时也容易出现误解和错误判断。
3、难以进行MDL锁调试和优化：用户就需要手动查询MDL锁信息，这样会增加调试和优化的难度，尤其在MDL锁数量较多的情况下，用户很难对MDL锁进行全面的分析和优化。
所以创建MDL锁视图能方便用户查看和分析MDL锁信息，并进行MDL锁调试和优化。
获取MDL锁
在MySQL 5.7中，可以通过查询performance_schema库中的metadata_locks表来查看数据库MDL锁的情况。
不过，在查询之前，首先需要确认开启performance_schema，在默认情况下，该功能已经开启。但如果未开启，修改my.cnf配置文件中的performance_schema属性来启用它，重启MySQL即可。
接着开启MDL锁记录，可以执行以下SQL语句来开启：
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME='wait/lock/metadata/sql/mdl';
然后通过关联表performance_schema.metadata_locks和表performance_schema.threads查看MDL锁的情况，执行SQL如下：
SELECT t.PROCESSLIST_ID,m.LOCK_STATUS,m.LOCK_TYPE,m.OBJECT_TYPE,m.LOCK_DURATION,m.OBJECT_SCHEMA as TABLE_SCHEMA,m.OBJECT_NAME as TABLE_NAME from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id where m.OBJECT_NAME not in( "metadata_locks","threads") and m.OBJECT_SCHEMA <> 'performance_schema' or m.OBJECT_SCHEMA is null or m.OBJECT_NAME is null;
各字段的说明如下：
|
序号 |
字段名 |
字段定义 |
字段说明 |
|---|---|---|---|
|
1 |
PROCESSLIST_ID |
bigint(20) unsigned |
会话ID，MySQL的线程连接标识符。 |
|
2 |
LOCK_STATUS |
varchar(24) |
MDL锁的状态，包含。
|
|
3 |
LOCK_TYPE |
varchar(24) |
MDL加锁的模式，如SHARED 、EXCLUSIVE 、INTENTION_SHARED等。 |
|
4 |
OBJECT_TYPE |
varchar(30) |
MDL锁的类型，如TABLE、SCHEMA、GLOBAL、FUNCTION、PROCEDURE等。 |
|
5 |
LOCK_DURATION |
varchar(30) |
MDL锁范围，取值如下：
|
|
6 |
TABLE_SCHEMA |
varchar(64) |
数据库名 |
|
7 |
TABLE_NAME |
varchar(64) |
表名 |
创建MDL锁视图
可以执行create view创建该视图：
create view metadata_lock_info(PROCESSLIST_ID,LOCK_STATUS,LOCK_TYPE,OBJECT_TYPE,LOCK_DURATION,TABLE_SCHEMA,TABLE_NAME) as SELECT t.PROCESSLIST_ID,m.LOCK_STATUS,m.LOCK_TYPE,m.OBJECT_TYPE,m.LOCK_DURATION,m.OBJECT_SCHEMA as TABLE_SCHEMA,m.OBJECT_NAME as TABLE_NAME from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id where m.OBJECT_NAME not in( "metadata_locks","threads","metadata_lock_info") and m.OBJECT_SCHEMA <> 'performance_schema' or m.OBJECT_SCHEMA is null or m.OBJECT_NAME is null;
演示
现有表t1
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| C1 | int(11) | YES | MUL | NULL | |
| C2 | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
|
会话1
|
会话2
|
|---|---|
| begin; | |
| insert into t1 values(1,'c'); | |
| begin; | |
|
alter table t1 add C3 int; (blocked) |
执行show processlist;
根据线程列表信息显示，会话ID为18684210的会话正在执行alter table操作，但是由于有其他会话持有该表的metadata lock，因此该会话被阻塞。然而，我们无法确定是哪个会话持有了该锁。在这种情况下，随机KILL其他会话可能会对线上业务造成不可预知的风险。因此，我们只能等待其他会话释放该MDL锁，以便会话ID为18684210的会话能够继续执行。
创建了MDL锁视图后，我们查看MDL锁视图情况
根据show processlist的结果和MDL锁视图信息，我们可以得知会话ID为18684210的线程正在等待表t1的metadata lock。而会话ID为18684207的线程持有表t1的metadata lock，因为该MDL锁是事务级别的，所以只要会话ID为18684207的线程的事务没有提交，会话ID为18684210的线程将一直被阻塞。
因此，要让业务继续运行，用户可以在会话ID为18684207的线程中执行commit命令或者终止该会话，以释放MDL锁。这样，会话ID为18684210的线程就可以继续执行。
