mysql metadata lock(2)

mysql metadata lock(二)

一、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的兼容性矩阵

 

IX

S

SH

SR

SW

SU

SNW

SNRW

X

IX

1

1

1

1

1

1

1

1

1

S

1

1

1

1

1

1

1

1

0

SH

1

1

1

1

1

1

1

1

0

SR

1

1

1

1

1

1

1

0

0

SW

1

1

1

1

1

1

0

0

0

SU

1

1

1

1

1

1

0

0

0

SNW

1

1

1

1

0

0

0

0

0

SNRW

1

1

1

0

0

0

0

0

0

X

1

0

0

0

0

0

0

0

0

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

 

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

1.select语句操作MDL锁流程

   1)Opening tables阶段,加共享锁

       a)   加MDL_INTENTION_EXCLUSIVE锁

       b)   加MDL_SHARED_READ锁

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

      a)   释放MDL_INTENTION_EXCLUSIVE锁

      b)   释放MDL_SHARED_READ锁

2. DML语句操作MDL锁流程

  1)Opening tables阶段,加共享锁

     a)   加MDL_INTENTION_EXCLUSIVE锁

     b)   加MDL_SHARED_WRITE锁

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

    a)   释放MDL_INTENTION_EXCLUSIVE锁

    b)   释放MDL_SHARED_WRITE锁

3. alter操作MDL锁流程

  1)Opening tables阶段,加共享锁

    a)   加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(一)》。

 

1楼八宝旗
你好, ,以mysql metadata lock(一)中的例子,#### 大查询或mysqldump导致alter等待MDL,# 时间点1,quot;Select count(*) from t;quot;,+ MDL_SHARED_READ,,# 时间点2,quot;alter table t add column c3 int;quot;阻塞,+ MDL_SHARED_UPGRADABLE锁,升级到MDL_SHARED_NO_WRITE锁,,但 SR与 SU、SNW是兼容的,并不会阻塞 copy data
Re: 天士梦
@八宝旗,谢谢您的指正,我文章《mysql metadata lock(一)》中描述的有问题,现在已经改正。alter操作在执行copy时不会被阻塞,而是在rename时才会被阻塞,再次通过实验验证。

相关内容推荐