214_mysql_innodb_6_MDL锁

MDL 锁  属于erver层的锁用于解决或者保证DDL操作与DML操作之间的一致性

MDL lock 类型

MDL锁的性能与并发改进

MDL锁的开销并不比InnoDB层的行锁要小，而且这可能是一个更为密集的并发瓶颈。MySQL 5.6和5.5版本通常通过调整如下两个参数来进行并发调优:

metadata_locks_cache_size: MDL锁的缓存大小

metadata_locks_hash_instances：通过分片来提高并发度,与InnoDB AHI类似

MySQL 5.7 MDL锁的最大改进之处在于将MDL锁的机制通过lock free算法来实现，从而提高了在多核并发下数据库的整体性能提升

MDL锁对象范围

这些对象发生锁等待时，我们在 show full processlist可以分别看到如下等待信息

Waiting for global read lock
Waiting for commit lock 
Waiting for schema metadata lock 
Waiting for table metadata lock 
Waiting for stored function metadata lock 
Waiting for stored procedure metadata lock
Waiting for trigger metadata lock 
Waiting for event metadata lock

MDL 锁持有时间

几种典型语句的加(释放)锁流程

select语句操作MDL锁流程:
1) Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_READ锁

2) 事务提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_SHARED_READ SR锁

DML语句操作MDL锁流程
1) Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_WRITE SW锁 

/////engine (行锁)
2) 事务提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_SHARED_WRITE锁

alter操作MDL锁流程(copy方式 改主键/数据类型等)
1) Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_UPGRADABLE锁，升级到MDL_SHARED_NO_WRITE SNRW锁

2) 操作数据，copy data，流程如下：
a) 创建临时表tmp，重定义tmp为修改后的表结构
b) 从原表读取数据插入到tmp表

3) 将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁
a) 删除原表，将tmp重命名为原表名

4) 提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_EXCLUSIVE锁

Online DDL-- inplace

第一阶段 ： Prepare阶段
创建新的临时frm文件(与InnoDB无关)
持有EXCLUSIVE-MDL X锁，禁止读写
根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
假如是Add Index，则选择online-norebuild即INPLACE方式
更新数据字典的内存对象
分配row_log对象存储空间记录增量(仅rebuild类型需要,记录在row_log里记录执行阶段的记录增量)
生成新的临时ibd文件(仅rebuild类型需要)

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

第三阶段： commit阶段
当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL X锁
重做row_log中最后一部分增量
更新innodb的数据字典表
提交事务(刷事务的redo日志)
修改统计信息
rename临时idb文件，frm文件
变更完成

相关锁监控&&查询

开启 MDL instrument
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

SELECT * FROM performance_schema.metadata_locks\G;
SELECT * FROM performance_schema.metadata_locks  WHERE object_schema != 'performance_schema'

   OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 240554768
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5927
      OWNER_THREAD_ID: 38
       OWNER_EVENT_ID: 10
1 row in set (0.00 sec)
OBJECT_TYPE  元数据锁定子系统中使用的锁定类型(GLOBAL，SCHEMA，TABLE)
OBJECT_SCHEMA  库名
OBJECT_NAME    表名
OBJECT_INSTANCE_BEGIN  内存中的地址
LOCK_TYPE  锁类型
LOCK_DURATION  持续时间(STATEMENT，TRANSACTION或EXPLICIT)
lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL
OWNER_THREAD_ID  元数据锁定的线程
OWNER_EVENT_ID  请求元数据锁定的事件

SELECT
    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\G

locked_schema: slowtech
locked_table: t1
locked_type: Metadata Lock
waiting_processlist_id: 4
waiting_age: 259
waiting_query: alter table slowtech.t1 add c1 int
waiting_state: Waiting for table metadata lock
blocking_processlist_id: 2
blocking_age: 301
blocking_query: NULL
sql_kill_blocking_connection: KILL 2
SELECT *  from sys.schema_table_lock_waits

mysql> select m.*,t.PROCESSLIST_ID from metadata_locks m left join threads t on m.owner_thread_id=t.thread_id;