MDL锁

MetaData Lock即元数据锁，在数据库中元数据即数据字典信息包括db,table,function,procedure,trigger,event等。metadata lock主要为了保证元数据的一致性,用于处理不同线程操作同一数据对象的同步与互斥问题。

MDL锁的性能与并发改进

手动加锁

手动添加读写锁

如何查看锁信息：

-- 据show open tables可以快速判断哪些表产生了共享读的表锁，以此为基础，可以判断其它的一些相关的锁数据库故障
mysql> show OPEN TABLES where In_use > 0;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| sbtest1  | sbtest1 |      1 |           0 |
+----------+---------+--------+-------------+
1 row in set (0.00 sec)

-- mysql5.7还提供metadata_locks表用来快速查看mdl，5.7不默认开启，需手动开启
mysql> UPDATE performance_schema.setup_instruments set enabled='YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  SELECT * FROM performance_schema.metadata_locks where OBJECT_NAME like 'sbtest1'\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: sbtest1
          OBJECT_NAME: sbtest1
OBJECT_INSTANCE_BEGIN: 336823392
            LOCK_TYPE: SHARED_NO_READ_WRITE -- 不允许读写锁
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6730
      OWNER_THREAD_ID: 1431
       OWNER_EVENT_ID: 3
1 row in set (0.00 sec)

手动添加写锁

mysql> lock table sbtest1 read;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  SELECT * FROM performance_schema.metadata_locks where OBJECT_NAME like 'sbtest1'\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: sbtest1
          OBJECT_NAME: sbtest1
OBJECT_INSTANCE_BEGIN: 314326768
            LOCK_TYPE: SHARED_READ_ONLY -- 只读
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6730
      OWNER_THREAD_ID: 1443
       OWNER_EVENT_ID: 8
1 row in set (0.00 sec)

表空间迁移中的元数据锁

表空间迁移流程

将a实例的表的数据迁移到b实例上.
1.在目标实例b上创建一个相同的表
2.在目标库b上执行ALTER TABLE t DISCARD TABLESPACE;
3.在源库a上执行FLUSH TABLES t FOR EXPORT;生成.cfg文件
4.将.ibd文件和.cfg文件拷贝到目标实例b
5.在源库a执行unlock tables;
6.在目标库b执行ALTER TABLE t IMPORT TABLESPACE;

加锁实验

元数据锁查看

mysql> show open tables where in_use > 0;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| sbtest2  | sbtest1 |      1 |           0 |
+----------+---------+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.metadata_locks where OBJECT_NAME like 'sbtest1'\G;
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: sbtest2
          OBJECT_NAME: sbtest1
OBJECT_INSTANCE_BEGIN: 299096848
            LOCK_TYPE: SHARED_NO_WRITE -- 可读不可写
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6730
      OWNER_THREAD_ID: 1437
       OWNER_EVENT_ID: 12
1 row in set (0.00 sec)

ERROR:
No query specified

DDL中的元数据锁

Offline DDL和Online DDL最重要的区别：DDL执行过程中是否支持对表写操作，该区别是由DDL执行过程中加不同的元数据锁决定的。

元数据锁是server层的锁，主要用于隔离DML和DDL以及DDL之间的干扰，本章节只介绍DDL中的元数据锁、元数据锁之间的关系以及如何借助元数据锁信息定位锁冲突问题。

DDL中的元数据锁

MDL_SHARED_UPGRADABLE介绍

元数据锁之间的关系

1） .MDL_EXCLUSIVE和MDL_SHARED_READ互斥

事务1拥有表的MDL_EXCLUSIVE锁，事务2申请MDL_SHARED_READ锁时等待

事务1拥有表的MDL_SHARED_READ锁，事务2申请MDL_EXCLUSIVE锁时等待

2） MDL_EXCLUSIVE和MDL_SHARED_WRITE互斥

3） MDL_SHARED_UPGRADABLE和MDL_SHARED_UPGRADABLE互斥

4） MDL_SHARED_UPGRADABLEE和MDL_SHARED_READ兼容

5） MDL_SHARED_UPGRADABLEE和MDL_SHARED_WRITE兼容

6） MDL_SHARED_NO_WRITE和MDL_SHARED_READ兼容

7） MDL_SHARED_NO_WRITE和MDL_SHARED_WRITE互斥

Online DDL流程

Online DDL主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段。下面将主要介绍ddl执行过程中3个阶段的流程。

Prepare阶段

1. 创建新的临时frm文件
2. 申请MDL_EXCLUSIVE锁，禁止DML读写
3. 根据alter类型，确定执行方式(rebuild / no-rebuild)
4. 分配row_log对象记录增量(仅rebuild类型需要)
5. 生成新的临时ibd文件(仅rebuild类型需要)

执行阶段

1. 降级EXCLUSIVE锁为SHARED_UPGRADABLE，允许DML读写
2. 扫描old_table的聚集索引每一条记录rec
3. 遍历新表的聚集索引和二级索引，逐一处理
4. 根据rec构造对应的索引项
5. 将构造索引项插入sort_buffer块排序
6. 将sort_buffer块更新到新的索引上
7. 记录ddl执行过程中产生的增量(仅rebuild类型需要)
8. 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
9. 重放row_log间产生dml操作append到row_log最后一个Block

Commit阶段

1. 当前Block为row_log最后一个时，禁止读写，升级到MDL_EXCLUSIVE锁
2. 重做row_log中最后一部分增量
3. 更新innodb的数据字典表
4. 提交事务(刷事务的redo日志)
5. 修改统计信息
6. rename临时idb文件，frm文件
7. 变更完成

锁相关

1. 执行阶段加的锁是SHARED_UPGRADABLE，该阶段允许并行读写；
2. Prepare和Commit阶段，加的锁是EXCLUSIVE，这两个阶段不能并行DML；由此可见，Online DDL并不是全过程允许DML并行。但是Prepare和Commit阶段的耗时很短，占整个DDL流程比例非常小，对业务影响可以忽略不计。反过来，正在执行的业务可能会对DDL产生影响，可能会产生锁冲突的情况
3. MDL_SHARED_UPGRADABLE之间是互斥的，所以可以保证同一张表不会并行执行多个DDL。

执行方式

根据DDL是否需要重建表空间，可以分为no-rebuild和rebuild两种方式。

no-rebuild

no-rebuild不涉及表的重建（例如修改字段名），只修改元数据项（添加索引，会产生部分二级索引的写入操作），即只在原表路径下产生.frm文件，是代价最小、速度最快的DDL类型。

rebuild

rebuild涉及表的重建（例如新增字段），在原表路径下创建新的.frm和.ibd文件，拷贝ibd文件时消耗的IO较多。

DDL执行过程中，并行的DML操作原表，同时会申请row log空间记录DML操作，这部分操作会在DDL执行和提交阶段应用到新的表空间中。row log空间是一个独立的空间，其大小可通过参innodb_online_alter_log_max_size控制（默认128M），当DDL过程中，并行的DML超过innodb_online_alter_log_max_size容量，就会报错。

rebuild方式的DDL，对空间有要求，对IO消耗比较大，是代价比较大的DDL类型。

附：在线DDL分类

• Previous
  MySQL Performance_schema数据字典
• Next
  mysql的磁盘碎片清理功能