Home > Database > Mysql Tutorial > mysql metadata lock_MySQL

mysql metadata lock_MySQL

WBOY
Release: 2016-05-31 08:48:36
Original
1106 people have browsed it

      想必玩过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

 

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template