MySQL metadata lock

什么是元数据

描述数据库中的数据的数据都是元数据，如库名、表明、列名、版本名，和show语句展示的大多数内容都是元数据，以及在information_shema中记录数据库对象的表中的内容也是元数据

为什么MySQL要设置元数据锁

为了保证可以并发访问数据库对象及保证数据的一致性，所以应用metadata lock，如session1正在扫描t表数据，此会话持有t表的元数据锁，这时session2话尝试要drop t表，在尝试获取t表元数据锁的时候被阻塞，假如没有MDL的设计，那么在session1执行完之前session2将表drop掉，那么就会出现优化器在扫描到表一半时候突然抛出表不存在的异常

MDL的工作原理

在同一事务中操作事务表t和非事务表nt，如果此事务不结束，则另一会话对表t和nt的ddl操作都无法进行

如果事务中的语句通过语法校验但在执行的时候抛出异常，但是此事务不结束，对其涉及到的表的ddl操作也被阻塞，测试见例二

测试MDL

例一

# session1
14:14:13[test](;)> begin;
Query OK, 0 rows affected (0.00 sec)

14:14:18[test](;)> select * from t1 limit 1;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  2 |    3 |    5 | NULL |
+----+------+------+------+
1 row in set (0.00 sec)

#session2
14:14:48[test](;)> alter table t1 add col4 int;

#session3
14:15:48[test](;)> show processlist;


| 4834501 | root        | localhost | test | Query   |      14 | Waiting for table metadata lock                        | alter table t1 add col4 int |

例二  

session1
14:28:36[test](;)> begin;
Query OK, 0 rows affected (0.00 sec)

14:28:40[test](;)> insert into t1(id) values(2); 
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

session2
14:28:40[test](;)> alter table t1 drop col4;

session3
| 4834501 | root        | localhost | test | Query   |      12 | Waiting for table metadata lock                        | alter table t1 drop col4 |

 

MDL的等待超时时长默认值为一年

14:17:32[(none)](;)> select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
|            31536000 |
+---------------------+
1 row in set (0.00 sec)

建议

在操作频繁的时候尽量避免大表的DDL操作；MDL超时时长可以根据业务场景设置；有必要做MDL的监控报警

 

官方文档：https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

补充

在autocommit 模式下，非显示开启事务，执行 lock tables ..... read 这类语句也会持有元数据锁，知道执行unlock table 或是会话中断

在这种情况下在information_schema.innodb_trx 表中是查不到它的信息的，这个时候就需要视版本来选择查看表锁信息的方式，如

session1
[test] mysql> lock tables t2 read;
Query OK, 0 rows affected (0.00 sec)

 

MySQL5.6  在session2会话中通过show open tables语句可以看出t2表正在被使用

session2
[test] mysql> show open tables from test like 't2';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t2    |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

当seesion1 执行 unlock tables 后t2上的元数据锁才会释放

MySQL5.7开始系统库performance_schema中新增表metadata_locks，默认不监控metadata lock，打开方式

mysql> UPDATE performance_schema.setup_instruments
    -> SET ENABLED = 'YES', TIMED = 'YES'
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select * from performance_schema.setup_instruments where NAME = 'wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES     | YES   |
+----------------------------+---------+-------+
1 row in set (0.00 sec)

同样会话session1 执行lock table ....后，会话session2 执行ddl 被挂起，此时查看metadata_locks

mysql> select * from metadata_locks;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | test               | t2             |       140433686616208 | SHARED_READ_ONLY    | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |             554 |             11 |
| GLOBAL      | NULL               | NULL           |       140433489208736 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5533  |             556 |             18 |
| SCHEMA      | test               | NULL           |       140433487872928 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5518  |             556 |             18 |
| TABLE       | test               | t2             |       140433487983984 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |             556 |             18 |
| TABLE       | test               | t2             |       140433487984224 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:3919       |             556 |             18 |
| TABLE       | performance_schema | metadata_locks |       140433765968400 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |             555 |             95 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
6 rows in set (0.00 sec)

OWNER_EVENT_ID 对应的11 为session1 会话，18对应的是session2

 

转载于:https://www.cnblogs.com/Bccd/p/7523766.html