mysql出现Waitingfortablemetadatalock的原因及解决方案_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql出现Waitingfortablemetadatalock的原因及解决方案_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5766字，纯文字阅读大概需要9分钟。

内容图文

mysql出现Waiting for table metadata lock的原因及解决方案

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

在insert into t select * from share 运行时， 同时执行alter table t add index(play_count)，

alter table语句会Waiting for table metadata lock， 直到insert into … select 语句结束。

不是传说5.6支持online DDL么？ 怎么还会Waiting for table metadata lock?

后来想想， online DDL应该是指在alter table进行的时候， 插入/修改/删除数据的sql语句不会Waiting for table metadata lock.

MySQL 5.6 enhances many other types OF ALTER TABLE operations TO avoid copying the TABLE.

Another enhancement allows SELECT queries AND INSERT, UPDATE, AND DELETE (DML) statements TO proceed while the TABLE IS being altered.

This combination OF features IS now known AS online DDL.

那么就让alter table wait去吧。

后来又发现另外一个神奇的事：

mysql [localhost] {msandbox} (spc) > SHOW processlist;+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+| Id | USER     | Host      | db   | Command | TIME | State                           | Info                                |+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+|  5 | msandbox | localhost | spc  | Query   |    1 | Waiting FOR TABLE metadata LOCK | ALTER TABLE t ADD INDEX(play_count) ||  8 | msandbox | localhost | spc  | Query   |    3 | USER sleep                      | SELECT sleep(100) FROM t            || 10 | msandbox | localhost | spc  | Query   |    0 | init                            | SHOW processlist                    |+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+

重启后再试一次：

mysql [localhost] {msandbox} (spc) > SHOW processlist;+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+| Id | USER     | Host      | db   | Command | TIME | State                           | Info                                |+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+|  1 | msandbox | localhost | spc  | Query   |  129 | USER sleep                      | SELECT sleep(100) FROM t            ||  2 | msandbox | localhost | spc  | Query   |  102 | Waiting FOR TABLE metadata LOCK | ALTER TABLE t DROP INDEX play_count ||  3 | msandbox | localhost | spc  | Query   |    0 | init                            | SHOW processlist                    |+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+

这个sleep的时间。。。已经超过100秒了…

结论：

在准备alter table tbl 的时候，先观察一下，有没有正在运行的，且在短时间内无法结束的sql语句在操作tbl表

内容总结

以上是互联网集市为您收集整理的mysql出现Waitingfortablemetadatalock的原因及解决方案_MySQL全部内容，希望文章能够帮你解决mysql出现Waitingfortablemetadatalock的原因及解决方案_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端