MySQL 元数据锁查询

为什么引入MDL锁

对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。

什么是MDL

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥），申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。

事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中
包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作
作为一个单独的事务存在，同时也保证元数据排他锁的释放）。

查看元数据锁

在MySQL5.7中，performance_schema库中新增了metadata_locks表，专门记录MDL的相关信息。首先要开启metadata_locks锁记录，执行如下SQL开启：（8.0默认打开）

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';

#配置文件中添加，重启生效
performance-schema-instrument = wait/lock/metadata/sql/mdl=ON

以8.0为例模拟MDL锁和锁冲突

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        |       139653546092288 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             402 |              4 |
+-------------+--------------------+----------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.00 sec)

开启一个session

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor;


mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|             362 |
+-----------------+
1 row in set (0.00 sec)

再次查询

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       | sakila             | actor          | NULL        |       139653546092096 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             402 |             39 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139653682246880 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             403 |              5 |
+-------------+--------------------+-------------

此时再次查看metadata_lock表，发现多了一条actor表的加锁记录，加锁类型为SHARED_READ,且状态是已授予（GRANTED)。

如果在session2执行select期间且事务未提交，另外一个session执行了一个DDL操作，此时就会产生互斥的metadata lock：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table actor;

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       | sakila             | actor          | NULL        |       139653546092096 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             402 |             39 |
| GLOBAL      | NULL               | NULL           | NULL        |       139653617167616 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5436  |             404 |             36 |
| BACKUP LOCK | NULL               | NULL           | NULL        |       139653616499728 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5443  |             404 |             36 |
| SCHEMA      | sakila             | NULL           | NULL        |       139653615812928 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5423  |             404 |             36 |
| TABLE       | sakila             | actor          | NULL        |       139653624258944 | EXCLUSIVE           | TRANSACTION   | PENDING     | sql_parse.cc:5768 |             404 |             36 |
| TABLE       | performance_schema | metadata_locks | NULL        |       139653682246880 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:5768 |             403 |              6 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
6 rows in set (0.00 sec)

查看metadata_locks表记录发现actor表有MDL锁冲突（ SHARED_READ 和EXCLUSIVE 互斥）

通过查看 show processlist;也看到pid为364的回话，在等到元数据锁释放Waiting for table metadata lock

mysql> show processlist;
+-----+-----------------+-----------+--------+---------+--------+---------------------------------+------------------+
| Id  | User            | Host      | db     | Command | Time   | State                           | Info             |
+-----+-----------------+-----------+--------+---------+--------+---------------------------------+------------------+
|   5 | event_scheduler | localhost | NULL   | Daemon  | 412694 | Waiting on empty queue          | NULL             |
| 362 | root            | localhost | sakila | Sleep   |    441 |                                 | NULL             |
| 363 | root            | localhost | NULL   | Query   |      0 | init                            | show processlist |
| 364 | root            | localhost | sakila | Query   |    124 | Waiting for table metadata lock | drop table actor |
+-----+-----------------+-----------+--------+---------+--------+---------------------------------+------------------+

元数据锁为什么会造成系统崩溃

举一个简单例子，session1启动一个事务，对表actor执行一个简单的查询；session2对actor加一个字段；session3来对actor做一个查询；session4来对actor做一个update，各个session串行操作。

前面提到过申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。

也就是由于session1的一个事务没有提交，导致session2的DDL操作被阻塞，session3和session4本身不会被session1阻塞，但由于在锁队列中，session2排队更早，它准备加的是metadata lock写锁，阻塞了session3和session4的读锁。如果students是一个执行频繁的表，show processlist会发现大量‘waiting for table metadata lock’的线程，数据库连接很快就会消耗完，导致业务系统无法正常响应。

如何监控元数据锁

metadata_locks是5.7中被引入，记录了metadata lock的相关信息，包括持有对象、类型、状态等信息。
单纯查询metadata_locks这个表无法得出具体的阻塞关系，也无法得知什么语句造成的阻塞，这里要关联另外两个表performance_schema.thread和performance_schema.events_statements_history,thread表可以将线程id和show processlist中id关联，events_statements_history表可以得到事务的历史sql，关联得到一条监控元数据锁的sql语句。

mysql> 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_connection
    -> FROM 
    -> ( 
    -> 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
    -> ) t2
    -> WHERE t1.granted_thread_id = t2.thread_id \G
*************************** 1. row ***************************
               locked_schema: sakila
                locked_table: actor
                 locked_type: Metadata Lock
      waiting_processlist_id: 364
                 waiting_age: 248
               waiting_query: drop table actor
               waiting_state: Waiting for table metadata lock
     blocking_processlist_id: 362
                blocking_age: 565
              blocking_query: select * from actor
sql_kill_blocking_connection: KILL 362
1 row in set (0.00 sec)

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