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_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,分析特定语句在每个阶段的耗时时间。
抛出几个问题:
- select 与alter是否会相互阻塞
- dml与alter是否会相互阻塞
- 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在同时起作用。