> 데이터 베이스 > MySQL 튜토리얼 > mysql metadata lock_MySQL

mysql metadata lock_MySQL

WBOY
풀어 주다: 2016-05-31 08:48:36
원래의
1170명이 탐색했습니다.

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

 

원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿