mysql metadata lock(三)
前言
MDL锁主要用来保护Mysql内部对象的元数据，通过MDL机制保证DDL与DML以及SELECT查询操作的并发。MySQL Meta Lock(一)和MySQL Meta Lock(二)已经讲了一些关于MDL知识，本文将会对MDL进行一个补充，并解释查询堵塞和mysqldump获取一致性备份的原理。
一、MDL锁类型
1.按类型划分
2.按对象/范围维度划分
|
属性 |
含义 |
范围/对象 |
|
GLOBAL |
全局锁 |
范围 |
|
COMMIT |
提交保护锁 |
范围 |
|
SCHEMA |
库锁 |
对象 |
|
TABLE |
表锁 |
对象 |
|
FUNCTION |
函数锁 |
对象 |
|
PROCEDURE |
存储过程锁 |
对象 |
|
TRIGGER |
触发器锁 |
对象 |
|
EVENT |
事件锁 |
对象 |
MDL锁主要包括DB对象和范围两个维度，对象的MDL我们很好理解，为了保护对象的元数据。那么范围级别的锁呢？锁本质是为了保护共享资源，所以范围和对象都可以理解为一种资源。MYSQL约定某些操作必需要上COMMIT范围锁或GLOBAL范围锁，通过这种同步机制，保证各个线程有序运转。后面会结合案例详细讨论COMMIT和GLOBAL锁的应用场景。
3.按请求/释放锁持续时间划分
|
属性 |
含义 |
|
MDL_ STATEMENT |
语句级别 |
|
MDL_TRANSACTION |
事务级别 |
|
MDL_EXPLICIT |
需要显示释放 |
MDL另外一个属性是持有时间。如果是STATEMENT，则表示单个语句执行完毕后，MDL释放；TRANSACTION级别表示，事务结束后，MDL锁释放；前两者都是隐式锁，即请求锁和释放锁都是系统内部行为，用户无需发指令，而MDL_EXPLICIT表示MDL锁是显示请求和释放的。比如：flush table with read lock这个指令会显示上GLOBAL:MDL_EXPLICIT:SHARED和COMMIT:MDL_EXPLICIT:SHARED锁；需要通过UNLOCK TABLES指令显示释放。
4.范围锁的兼容性矩阵
5.举个栗子
begin：
update t3 set c1=1 where id=1;
commit;
|
流程 |
执行语句 |
执行内容 |
字典锁 |
|
1 |
begin |
|
释放MDL release_transactional_locks |
|
2 |
update t3 set c1=1 where id=1;
|
请求 STATEMENT MDL |
GLOBAL:STATMENT MDL_INTENTION_EXCLUSIVE |
|
3 |
请求 TRANSACTION MDL |
TABLE:TRANSACTION MDL_SHARED_WRITE |
|
|
6 |
执行更新 |
|
|
|
7 |
释放 STATEMENT MDL |
GLOBAL:STATMENT |
|
|
8 |
commit;
|
请求 COMMIT MDL |
COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE |
|
9 |
执行提交 |
|
|
|
11 |
释放 COMMIT MDL 释放 TRANSACTION MDL |
COMMIT: MDL_EXPLICIT MDL_INTENTION_EXCLUSIVE |
|
|
12 |
release_transactional_locks TABLE:TRANSACTION |
二、请求锁/释放锁原理
锁兼容矩阵在metadata lock(二)已经有详细的介绍，仔细看代码发现MDL的锁兼容矩阵实际上包含两部分：活跃锁兼容矩阵，等待锁兼容矩阵。当请求锁时，需要保证两个矩阵对应的值都兼容，才能请求锁成功。为什么要设计等待锁兼容矩阵？我理解这里主要是优先保证DDL操作。因为如果DDL操作在等待一个查询操作时，其他查询还源源不断地进入，可能会导致DDL永远也拿不到锁，而实际情况下，DDL操作的重要性往往比查询或DML重要地多。
1. 请求锁兼容性检查：
1) 检查请求锁是否与已经存在的活跃锁冲突，若冲突，则等待；
2) 检查请求锁是否与已经存在的等待锁冲突，若冲突，则等待。
3) 请求锁成功。
2. 释放锁时机：
1) 语句执行结束后，释放STAMENT类型的锁
2) 事务提交时，先后请求COMMIT类型和释放COMMIT类型的锁
3) 事务提交后，释放TRANSACTION类型的锁
三、MDL应用场景分析
MDL是Mysql层面很重要的锁，很多常见问题的源头以及功能实现都依赖于MDL，以下我会举几个常见的问题和功能进行分析。
1. 为什么查询也会被阻塞？
我们在实际运维过程中，一个常见的场景是，接到手机threadrunning飙高告警，登上主机，show processlist看到一大片线程处于“Waiting for table metadata lock”状态，当然其中也包含查询。下面我通过一个简单的例子重新这个场景。
|
时间点 |
会话A |
会话B |
会话C |
|
|
1 |
begin update t3 set c1=1 where id=1; |
|
|
|
|
2 |
返回 |
|
|
|
|
3 |
|
alter table t3 add column c3 int; |
|
|
|
4 |
|
等待 |
|
|
|
5 |
|
|
select * from t3 |
|
|
6 |
|
|
等待 |
|
|
7 |
show processlist 返回结果 |
|
|
|
|
8 |
init |
show processlist |
|
|
|
Waiting for table metadata lock |
alter table t3 add column c3 int |
|||
|
Waiting for table metadata lock |
select * from t3 |
|||
从表2可以看到A会话未提交的事务堵住了回话B的DDL语句，而DDL语句进而又堵住了会话C，从第8步来看，会话B和会话C都处于“Waiting for table metadata lock”状态。从表1我们可以看到，会话A的DML操作会请求TABLE- TRANSACTION- MDL_SHARED_WRITE锁，由于没有执行COMMIT，会一直持有；会话B的DDL操作会请求TABLE-TRANSACTION-EXCLUSIVE锁，由于两把锁互斥，等待；会话C的查询操作会请求TABLE- TRANSACTION- MDL_SHARED_READ锁，虽然MDL_SHARED_READ与活跃锁MDL_SHARED_WRITE不冲突，但是与回话B的等待锁EXCLUSIVE冲突，因此也会等待。遇到这种情况，首先要看看是否存在堵住的DDL，如果存在DDL，然后查询是否有大查询或者未提交的事务，这两种情况都会导致DDL堵住，进而影响普通的查询和DML操作。
2. Mysqldump与全局锁
在实际生产环境中，为了容灾和负载均衡，数据库服务一般由一主一备一对实例组成，主库对外提供读写服务，备库提供只读服务，或纯粹为了容灾使用。在这种体系下，通过mysqldump搭建新的实例时，需要获得一个一致性备份集，并且获得对应的位点(拉取主库binlog的依据)，通过全量+增量的方式复制一个数据库实例。Mysqldump中为了保证一致性备份和获取对应的位点，需要设置两个关键的参数--master-data=2 和--single-transaction。我们可以通过mysqld的trace功能，跟踪Mysqldump执行的语句。假设我们要备份chuck库，命令如下：
./bin/mysqldump -uchuck -pchuck -P4006 –h127.0.0.1 --databases chuck mysql --master-data=2 --single-transaction --default-character-set=utf8 > chuck_dump.sql 2>chuck_dump.log
开启mysqld的trace功能
--debug=d,query,general:O,/kkk/mysqld.trace
通过mysqld.trace，我大概整理了关键的语句，分析和结果如下图所示。可以看到，mysqldump获取增量位点主要是通过FLUSH TABLES WITH READ LOCK语句的两把字典锁来实现(GLOBAL LOCK和COMMIT LOCK)。整个过程持续时间不长(如果语句没有被阻塞)，获取位点后，立即将MDL锁释放。第2到第6步即为持有MDL锁的时间，这段时间内无法开启新事务，已有的事务也无法提交，保证位点的正确性。设置隔离级别为RR，则是获取一致性备份的关键，一致性备份的获取源于MVCC，关于MVCC的实现，后续可以单独写一篇文章。第8到第9步为一个表的备份过程， select语句会获取MDL锁(TABLE:TRANSACTION: MDL_SHARED_READ)，执行完毕后，通过rollback语句释放MDL锁。因此除了备份的当前表不能做DDL操作；当前表的DML，以及其它表的DDL和DML都不受影响，所以Mysqldump备份过程中，基本不会阻塞线上的其他语句执行。
|
步骤 |
关键语句 |
含义 |
作用 |
|
1 |
FLUSH /*!40101 LOCAL */ TABLES |
关闭打开表 |
清空查询缓存 |
|
2 |
FLUSH TABLES WITH READ LOCK |
上GLOBAL字典锁： GLOBAL: MDL_SHARED COMMIT: MDL_SHARED
|
阻塞新事务，以及活跃事务提交。 为获取一致性位点做准备 |
|
3 |
SET SESSION TRANSACTION ISOLATION LEVEL
REPEATABLE READ |
设置事务的隔离级别为RR |
保证快照读 |
|
4 |
START TRANSACTION /*!40100 WITH CONSISTENT
SNAPSHOT */ |
开启事务 |
|
|
5 |
SHOW MASTER STATUS |
获取binlog 位点 |
用来获取主库的增量的位点
|
|
6 |
UNLOCK TABLES |
释放 GLOBAL字典锁 |
允许其它事务更新 |
|
7 |
SAVEPOINT sp |
设置保存点 |
后续可以逐个表 释放MDL |
|
8 |
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` |
获取表t1的数据 TABLE:TRANSACTION MDL_SHARED_READ |
一致性读 |
|
9 |
ROLLBACK TO SAVEPOINT sp |
释放： TABLE:TRANSACTION MDL_SHARED_READ |
|
|
10 |
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2` |
获取表t2的数据 TABLE:TRANSACTION MDL_SHARED_READ
|
一致性读 |
|
11 |
ROLLBACK TO SAVEPOINT sp |
释放： TABLE:TRANSACTION MDL_SHARED_READ |
|
|
12 |
…… |
|
|
|
13 |
|
|
备份完成 |
参考文章
http://www.percona.com/blog/2010/04/24/how-fast-is-flush-tables-with-read-lock/
