专栏首页北京马哥教育alter table锁表,MySQL出现Waiting for table metadata lock的场景浅析及解决方案

alter table锁表,MySQL出现Waiting for table metadata lock的场景浅析及解决方案

在修改/增加表字段的时候,发现很慢,

show processlist; 时, Waiting for table metadata lock 能一直锁很久。

官网的一段话,可以理解下

http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html

8.10.4. Metadata Locking MySQL 5.5.3 and up uses metadata locking to manage access to objects (tables, triggers, and so forth). Metadata locking is used to ensure data consistency but does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects. Metadata locking is not a replacement for the table definition case, and its mutxes and locks differ from the LOCK_open mutex. The following discussion provides some information about how metadata locking works. To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends. This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that a session begins a transaction that uses transactional table t and nontransactional table nt as follows: START TRANSACTION; SELECT * FROM t; SELECT * FROM nt; Metadata locks are held on both t and nt until the transaction ends. If another session attempts a DDL operation on either table, it blocks until metadata lock release at transaction end. For example, a second session blocks if it attempts any of these operations: DROP TABLE t; ALTER TABLE t ...; DROP TABLE nt; ALTER TABLE nt ...; If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency. In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement. Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction. Before MySQL 5.5.3, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order

一个没提交的事务使用了A表, 另外一个session 对A表进行alter,出现waiting for table metadata lock


MySQL版本为5.6.12。

在进行alter table操作时,有时会出现Waiting for table metadata lock的等待场景。而且,一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态,后续对TableA的任何操作(包括读)都无法进行,也会在Opening tables的阶段进入Waiting for table metadata lock的队列。如果是产品环境的核心表出现了这样的锁等待队列,就会造成灾难性的后果。

造成alter table产生Waiting for table metadata lock的原因其实很简单,一般是以下几个简单的场景:

场景一:

通过show processlist可以看到TableA上有正在进行的操作(包括读),此时alter table语句无法获取到metadata 独占锁,会进行等待。 这是最基本的一种情形,这个和mysql 5.6中的online ddl并不冲突。一般alter table的操作过程中(见下图),在after create步骤会获取metadata 独占锁,当进行到altering table的过程时(通常是最花时间的步骤),对该表的读写都可以正常进行,这就是online ddl的表现,并不会像之前在整个alter table过程中阻塞写入。(当然,也并不是所有类型的alter操作都能online的,具体可以参见官方手册:http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)

场景二:

通过show processlist看不到TableA上有任何操作,但实际上存在有未提交的事务,可以在information_schema.innodb_trx中查看到。在事务没有完成之前,TableA上的锁不会释放,alter table同样获取不到metadata的独占锁。

场景三:

通过show processlist看不到TableA上有任何操作,在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中,对TableA进行了一个失败的操作(比如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效。从performance_schema.events_statements_current表中可以查到失败的语句。

官方手册上对此的说明如下:

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency. 也就是说除了语法错误,其他错误语句获取到的锁在这个事务提交或回滚之前,仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解,因为错误的语句根本不会被记录到二进制日志。

总之,alter table的语句是很危险的,在操作之前最好确认对要操作的表没有任何进行中的操作、没有未提交事务、也没有显式事务中的报错语句。如果有alter table的维护任务,在无人监管的时候运行,最好通过lock_wait_timeout设置好超时时间,避免长时间的metedata锁等待。

参考:

http://ctripmysqldba.iteye.com/blog/1938150

http://blog.csdn.net/cloudcraft/article/details/9343069

本文分享自微信公众号 - 马哥Linux运维(magedu-Linux),作者:马哥教育

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-01-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python练手题,敢来挑战吗?

    这到题用到了字符串的所有字母大写和所有字母小写和字符串拼接,复制,用到的函数有 json 将列表中的内容按照指定字符连接成一个字符串,

    小小科
  • [来稿]pycharm快捷键、tips、常用设置及版本控制 制

    在PyCharm安装目录 /opt/pycharm-3.4.1/help目录下可以找到ReferenceCard.pdf快捷键英文版说明 or 打开pychar...

    小小科
  • CentOS系统启动流程你懂否

    一、Linux内核的组成 相关概念: Linux系统的组成部分:内核+根文件系统 内核:进程管理、内存管理、网络协议栈、文件系统、驱动程序。 ...

    小小科
  • BizTalk Server 2006 Virtual Multi-Box Install

    One of the great improvements in BizTalk Server 2006 was the ease of the install...

    阿新
  • Peer Code Reviews Made Easy with Eclipse Plug-In

    Origin Article: Peer Code Reviews Made Easy with Eclipse Plug-In Origin Author: ...

    用户1221057
  • PAT 1006 Sign In and Sign Out

    1006. Sign In and Sign Out (25) 时间限制 400 ms 内存限制 65536 kB 代码长度限制 1600...

    ShenduCC
  • NLP标注工具brat 配置文件说明

    the configuration of an annotation project is controlled by four files:

    JadePeng
  • 探秘Tomcat——启动篇

    tomcat作为一款web服务器本身很复杂,代码量也很大,但是模块化很强,最核心的模块还是连接器Connector和容器Container。具体请看下图: ? ...

    JackieZheng
  • 使用假设协助调试(cs.SE)

    在开发人员调试时,开发人员会对故障原因做一定的假设,并收集证据来测试这些假设。为了更好地理解假设在调试中的作用,我们进行了两项研究。在一项初步研究中,我们发现,...

    Donuts_choco
  • 数学--数论--POJ1365——Prime Land

    风骨散人Chiam

扫码关注云+社区

领取腾讯云代金券