今天发生一个故障，MM复制结构（主备库），备库slave delay越来越大，造成在备库上的读与主库数据不一致，登上备库分析：
1.show processlist
drop table tmp_table 在 Waiting for table metadata lock
2.ps
mysqldump 在备份整个实例数据
kill了备份进程，drop table tmp_table执行成功，slave delay逐步减少
疑问：
1.metadata lock是什么东西
2.mysqldump中什么操作hold table metadata lock，hold范围是单表还是实例上全部表
mysqldump原理：
1.FLUSH TABLES
2.FLUSH TABLES WITH READ LOCK
- sets the global read lock
- closes open tables
- sets a flag to block commits
3.SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
4.start transaction
5.记录log pos
6.unlock tables
7.select * from ...
8.commit
测试(5.5.18):
|
Time |
sessionA |
sessionB |
|
1 |
Set auto_commit=0; |
Set auto_commit=0; |
|
2 |
create table tmp1(a int); |
|
|
3 |
create table tmp2(a int); |
|
|
4 |
create table tmp3(a int); |
|
|
5 |
FLUSH TABLES WITH READ LOCK; |
|
|
6 |
start transaction; |
|
|
7 |
unlock tables; |
|
|
8 |
|
insert into tmp1 value(1); （执行成功） |
|
9 |
|
drop table tmp1;（执行成功） |
|
10 |
Select * from tmp1;(表不存在) |
|
|
11 |
Select * from tmp2; |
|
|
12 |
|
insert into tmp2 value(1); （执行成功） |
|
13 |
|
drop table tmp2; （Waiting for table metadata lock） |
|
14 |
|
Kill drop table tmp2; |
|
15 |
Select * from tmp3; |
|
|
16 |
|
drop table tmp2; （Waiting for table metadata lock） |
|
17 |
commit; |
|
|
18 |
|
drop table tmp2; （执行成功） |
- DML操作在备份期间是不会被block(time 8)
- DDL操作在备份进程未获取某个table的meta data lock时，并发的DDL语句是可以执行的（time 9）
- DDL是不遵循MVCC的，session2执行DDL后，session1受到影响，一致性被破坏（time 10）
- Select tmp2获取table tmp2的table meta data lock，导致session2无法执行DDL，保证一致性（time 13）
- Mysqldump备份过程逐一获取table meta data lock,但是不是逐一释放，而是等到备份结束时统一释放 （time 16）
测试(5.1.48)：
由于5.1中没有引入meta data lock，所有在mysqldump备份过程中，并发session都可以执行DDL，导致备份集不一致，最终表现是使用此备份集恢复的备库在relay主库binlog会出现slave error，如DML找不到表，DDL重复操作等问题，之前一直以为是备份中断导致备份集不完整，现在终于找到原因了
思考：
虽然5.5中引入了meta data lock，但是仍然无法完全解决并发DDL对备份的影响：
- 5.1中由于没有引入meta data lock，在备份整个过程中并发DDL都会对其产生影响
- 5.5中引入meta data lock后，只是保证针对已经备份表的DDL会被block，只是降低了并发DDL影响的概率，解决方式是在start transaction与unlock tables之间获取实例上全部表的meta data lock，但是当表数量很大时，这个操作可以很耗时，而这个过程由于处于FLUSH TABLES WITH READ LOCK下，所以DML会被block，也许是因为DML执行频率远大于DDL操作，所以mysqldump选择了最大DML并发度
有同学讨论到MySQL 5.5下给大表加主键时会锁住读的问题，怀疑与fast index creation有关，这里简单说明下。
对照现象
为了说明这个问题的原因，有兴趣的同学可以做对比实验。
1) 在给InnoDB表创建主键期间，会锁住该表上的读数据
2) 但是同样的表执行删除主键期间，不会锁住该表上的读操作
----这说明与是否fast index creation无关，因为这两个操作在数据层面的行为应该是类似的，实际上，创建/删除主键都必须copy data。
3) 在创建主键期间，会锁住该表上执行的show create table
----用1、3的现象可以猜测出，实际上与meta data lock有关。
关于meta data lock(MDL)
MySQL 5.5中引入了MDL，当需要访问、修改表结构时，都需要对meta data加锁（读或写）。比如，当一个线程需要修改表结构的任意一部分时，此时需要阻塞对表结构的访问，当然也需要阻塞对数据行的访问。
加主键流程
当对一个表作加主键操作时，大致流程如下
1) MDL加写锁
2) 操作数据，最耗时部分，注意需要copy data，因此流程上是
a)创建一个临时表A，表A定义为修改后的表结构
b)从原表读取数据插入表A
c)删除原表，将表A重命名为原表名
3) MDL释放写锁
从这个流程可以看到，在最耗时的部分，meta data是被一个X锁保护的。因此在此期间，show create table 或者select data都是会被阻塞。
这解释了上面的1) 3)。
删除主键流程
1) MDL加读锁
2) 操作数据，最耗时部分
a) 创建一个临时表A，表A定义为修改后的表结构
b) 从原表读取数据插入表A
3) MDL将写锁升级为读锁
c) 删除原表，将表A重命名为原表名
4) MDL释放写锁
这个在最耗时的数据操作部分，加的是MDL的读锁，这样不会影响访问原表的表结构或数据（当然要做更新是不行的）。而最后升级为写锁的时间，只是做重命名表的操作，阻塞的时间就很短。
结论
1) 显然第二个流程更合理
2) 这个可以认为是MySQL一个可改进的点，并且在5.6下已经改进
3) 这个问题与是copy data还是inplace方式执行DDL无关，实际上由于InnoDB的聚集索引组织结构，增、删主键都是必须得copy data的。
==========更新====
有同学问说为什么在5.5 set old_alter_table=on;之后是不会阻塞读的？ 因为打开old_alter_table之后，MySQL认为这次无论如何是要copy data的，所以锁用的是“删除主键流程”的策略。
实际上无论old_alter_table是否打开，对主键操作都是必须copy data的，5.6的改进就是基于这个判断。
更多的Metadata lock 信息 请见：http://www.cnblogs.com/zuoxingyu/archive/2013/03/15/2960862.html