您好,欢迎来到三六零分类信息网!老站,搜索引擎当天收录,欢迎发信息

mysql metadata lock_MySQL

2024/5/1 18:10:48发布33次查看
想必玩过mysql的人对waiting for table metadata lock肯定不会陌生,一般都是进行alter操作时被堵住了,导致了我们在show processlist 时,看到线程的状态是在等metadata lock。本文会对mysql 的metadata lock做一个小小的总结,希望对大家有所帮助。
     mdl是在5.5才引入到mysql,之前也有类似保护元数据的机制,只是没有明确提出mdl概念而已。但是5.5之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是,5.1对于元数据的保护是语句级别的,5.5对于metadata的保护是事务级别的。所谓语句级别,即语句执行完成后,无论事务是否提交或回滚,其表结构可以被其他会话更新;而事务级别则是在事务结束后才释放mdl。
     引入mdl后,主要解决了2个问题,一个是事务隔离问题,比如在可重复隔离级别下,会话a在2次查询期间,会话b对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;另外一个是数据复制的问题,比如会话a执行了多条更新语句期间,另外一个会话b做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。
     5.5以后,什么情况下会碰到mdl锁,我结合实际情况举3个会出现mdl的场景,来分析mdl加锁时机。下文的.测试都是以mysql 5.5这个版本为基准,没有考虑到online ddl,下一篇博文会详细介绍5.6的online ddl。
1.大查询或mysqldump导致alter等待mdl
时间点
会话a
会话b
会话c
1
select count(*) from t;
2
alter table t add column c3 int;阻塞
3
show processlist;
b:waiting for table metadata lock
4
a:执行完毕
5
show processlist;
b:copy to tmp table
6
b:继续执行
7
select count(*) from t;
8
show processlist;
a: sending data
b: copy to tmp table
9
a:执行完毕
表1
       从表1可以看到,会话a先执行select ,b后执行alter,在会话a执行完毕前,会话b拿不到mdl锁。a会话在时间点4执行完毕后,会话b拿到mdl锁,变为copy to tmp table状态,时间点7,a会话再次执行查询,此时a和b都正常执行,这说明对于mdl锁而言,select会阻塞alter,而alter不会阻塞select。
2.表上存在未提交的事务,导致alter等待mdl
时间点
会话a
会话b
会话c
1
set autocommit=0;
update t set c2='9999' where c1=4;
2
alter table t drop column c3;
3
show processlist;
b:waiting for table metadata lock
4
a:提交事务
commit
5
show processlist;
b:copy to tmp table
6
b:继续执行
7
update t set c2='9999' where c1=4;阻塞
8
show processlist;
a: waiting for table metadata lock
b: copy to tmp table
9
b执行完毕
10
a执行完毕
表2
       从表2可以看到,会话a第一次执行update语句后,未提交,导致后面会话b执行alter语句时需要等待mdl锁;时间点4,a会话提交事务,此时会话b获取mdl锁,开始执行;时间点7,a会话再次发起update操作,此时a会话被阻塞住,这说明对于mdl锁而言,update会阻塞alter,同样alter也会阻塞update。
ps:时间点3由于通过show processlist只看到alter被阻塞了,但不清楚被谁阻塞,可以通过查看information_schema.innodb_trx可以找到活动的事务。
3.这种情况是第1种情况的特例,存在一个查询失败的语句,比如查询不存在的列,语句失败返回,但是事务没有提交,此时alter仍然会被堵住。
时间点
会话a
会话b
会话c
1
start transaction;
select c99 from t;
unknown column 'c99' in 'field list'
2
alter table t drop column c3;
3
show processlist;
b:copy to tmp table
4
show processlist;
b:waiting for table metadata lock
5
a:提交事务
commit
6
执行完毕
表3
      这里注意时间1,会话a要显示开启一个事务,否则查询会隐式回滚结束,无法重现上面的场景。会话b执行alter后,没有立即阻塞住,而是立马开始copy to tmp table,这个过程结束后,才进行了mdl锁等待。这怎么解释呢,应该是执行alter操作主要分为创建临时新表->插入老表的数据->临时新表rename to老表三个步骤,在这种情况下,到最后一步才需要mdl锁,所以copy过程中不会阻塞。由于没有查询在进行,而且查询也没有进入innodb层 (失败返回),所以show processlist和information_schema.innodb_trx没有可以参考的信息。
     这里有一个小疑点,对于第一种和第二种情况,alter在开始时就立马堵住了,第三种情况是copy结束后,才堵住。我猜测alter内部会根据实际情况,确定什么时候上mdl锁,有兴趣的同学可以去debug源码看看究竟。
参考:
http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/
http://ctripmysqldba.iteye.com/blog/1938150
该用户其它信息

VIP推荐

免费发布信息,免费发布B2B信息网站平台 - 三六零分类信息网 沪ICP备09012988号-2
企业名录 Product