mysql metadata lock(二)

     上一篇《mysql metadata lock(一)》介绍了为什么引入MDL,MDL作用以及MDL锁导致阻塞的几种典型场景,文章的最后还留下了一个小小的疑问。本文将更详细的介绍MDL,主要侧重介绍MDL的原理和实现。一般而言,商业数据库系统实现锁,一般将锁划分为读锁(共享锁)和写锁(排它锁),为了进一步提高并发性,还会加入意向共享锁和意向排它锁。但是偏偏mysql的MDL搞地比较复杂,但目的也是为了提高并发度。MDL包含有9种类型,详细参考表1。主要其实也是两大类,只是对共享锁做了进一步细分。

一、MDL的锁类型

锁名称

锁类型

说明

适用语句

MDL_INTENTION_EXCLUSIVE

共享锁

意向锁,锁住一个范围

任何语句都会获取MDL意向锁,

然后再获取更强级别的MDL锁。

MDL_SHARED

共享锁,表示只访问表结构

 

MDL_SHARED_HIGH_PRIO

共享锁,只访问表结构

show create table 等

只访问INFORMATION_SCHEMA的语句

MDL_SHARED_READ

访问表结构并且读表数据

select语句

LOCK TABLE ...  READ

MDL_SHARED_WRITE

访问表结构并且写表数据

SELECT ... FOR UPDATE

DML语句

MDL_SHARED_UPGRADABLE

可升级锁,访问表结构并且读写表数据

Alter语句中间过程会使用

MDL_SHARED_NO_WRITE

可升级锁,访问表结构并且读写表数据,并且禁止其它事务写。

Alter语句中间过程会使用

MDL_SHARED_NO_READ_WRITE

可升级锁,访问表结构并且读写表数据,并且禁止其它事务读写。

LOCK TABLES ... WRITE

MDL_EXCLUSIVE

写锁

禁止其它事务读写。

CREATE/DROP/RENAME TABLE等DDL语句。

                    表1

二、MDL的兼容性矩阵(对象维度)

 

 说明:横向表示其它事务已经持有的锁,纵向表示事务想加的锁

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

1.select语句操作MDL锁流程

   1)Opening tables阶段,加共享锁

       a)   加对象级别的MDL_SHARED_READ锁

  2)事务提交阶段,释放MDL锁

       a)   释放MDL_SHARED_READ锁

2. DML语句操作MDL锁流程

  1)Opening tables阶段,加共享锁

     a)   加global类型的MDL_INTENTION_EXCLUSIVE锁

     b)   加对象级别MDL_SHARED_WRITE锁

  2)事务提交阶段,释放MDL锁

    a)   释放MDL_INTENTION_EXCLUSIVE锁

    b)   释放MDL_SHARED_WRITE锁

3. alter操作MDL锁流程

  1)Opening tables阶段,加共享锁

    a)   加global类型的MDL_INTENTION_EXCLUSIVE锁

    b)   加对象级别的MDL_SHARED_UPGRADABLE锁,升级到MDL_SHARED_NO_WRITE锁

  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锁

 

四、典型问题分析。

一般而言,我们关注MDL锁,大部分情况都是线上出现异常了。那么出现异常后,我们如何去判断是MDL锁导致的呢。监视MDL锁主要有两种方法,一种是通过show  processlist命令,判断是否有事务处于“Waiting for table metadata lock”状态,另外就是通过mysql的profile,分析特定语句在每个阶段的耗时时间。

抛出几个问题:

  1. select 与alter是否会相互阻塞
  2. dml与alter是否会相互阻塞
  3. select与DML是否会相互阻塞

结合第三节几种语句的上锁流程,我们很容易得到这三个问题的答案。语句会在阻塞在具体某个环节,可以通过profile来验证我们的答案是否正确。

第一个问题,当执行select语句时,只要select语句在获取MDL_SHARED_READ锁之前,alter没有执行到rename阶段,那么select获取MDL_SHARED_READ锁成功,后续有alter执行到rename阶段,请求MDL_EXCLUSIVE锁时,就会被阻塞。rename阶段会持有MDL_EXCLUSIVE锁,但由于这个过程时间非常短(大头都在copy数据阶段),并且是alter的最后一个阶段,所以基本感觉不到alter会阻塞select语句。由于MDL锁在事务提交后才释放,若线上存在大查询,或者存在未提交的事务,则会出现ddl卡住的现象。这里要注意的是,ddl卡住后,若再有select查询或DML进来,都会被堵住,就会出现threadrunning飙高的情况。

第二个问题,alter在opening阶段会将锁升级到MDL_SHARED_NO_WRITE,rename阶段再将升级为MDL_EXCLUSIVE,由于MDL_SHARED_NO_WRITE与MDL_SHARED_WRITE互斥,所以先执行alter或先执行DML语句,都会导致语句阻塞在opening tables阶段。结合第一个和第二个问题,就可以回答《mysql metadata lock(一)》的疑问了。

第三个问题,显然,由于MDL_SHARED_WRITE与MDL_SHARED_READ兼容,所以它们不会因为MDL而导致等待的情况。具体例子和profile分析可以参考《mysql metadata lock(一)》。这里我们要考虑一个问题,LOCK TABLE ... READ上的MDL锁是MDL_SHARED_READ,而DML操作上的是MDL_SHARED_WRITE,那么前者和后者如何互斥?其实这个MDL是无能为力的,需要通过SERVER层的table lock来解,可以简单做一个实验,一个会话执行LOCK TABLE test READ,另外一个会话执行update test set xxx where id=xxx,会发现update语句堵塞,通过show processlist查看,状态为:"Waiting for table level lock"。但是如果使用LOCK TABLE test WRITE,则状态变为"Waiting for table metadata lock",其实此时table lock也是堵住的,只不过MDL在之前挡住了,这说明SERVER层的table lock和MDL在同时起作用。