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

比特网

mysql出现等待表元数据锁的原因及解决方案

 

元数据锁定

MySQL 5.5.3及更高版本使用元数据锁定来管理对对象的访问（表、触发器等）。元数据锁定用于确保数据一致性，但确实涉及一些开销，该开销随着查询量的增加而增加。多个查询尝试访问相同对象时，元数据争用会增加。

 

 

元数据锁定不能替代表定义情况及其互斥锁和锁与 LOCK_open 互斥体不同。以下讨论提供了有关元数据锁定如何工作的一些信息。

 

 

为了确保事务可串行化，服务器不得允许一个会话执行数据定义语言（DDL） ) 另一个会话中未完成事务中使用的表上的语句。服务器通过获取事务中使用的表的元数据锁并推迟释放这些锁直到事务结束来实现此目的。表上的元数据锁可防止表结构发生更改。这种锁定方法意味着，在事务结束之前，一个会话中的事务正在使用的表不能被其他会话在 DDL 语句中使用。

 

 

这个原则不仅适用于事务性表，也适用于非事务性表。假设会话开始使用事务性表 t 和非事务性表 nt 的事务，如下所示：

 

 

START TRANSACTION;SELECT * FROM t;SELECT * FROM nt;

 

元数据锁同时保留在 t 和 nt 上，直到事务结束。如果另一个会话尝试对任一表执行 DDL 操作，它将阻塞，直到事务结束时释放元数据锁。例如，如果第二个会话尝试执行以下任何操作，则它会被阻止：

 

 

DROP TABLE t;ALTER TABLE t ...;DROP TABLE nt;ALTER TABLE nt ...;

 

如果服务器获取语法有效但在执行过程中失败的语句的元数据锁，它不会提前释放锁。锁释放仍然推迟到事务结束，因为失败的语句会写入二进制日志，并且锁可以保护日志一致性。

 

 

在自动提交模式下，每个语句实际上都是一个完整的事务，因此为该语句获取的元数据锁仅保留到语句末尾。

 

 

在 PREPARE 期间获取的元数据锁一旦语句准备好，即使准备发生在多语句事务中，语句也会被释放。

 

 

在 MySQL 5.5.3 之前，当事务获取相当于语句中使用的表的元数据锁，它在语句结束时释放锁。这种方法的缺点是，如果活动事务中另一个会话正在使用的表发生 DDL 语句，则语句可能会以错误的顺序写入二进制日志

 

一个没提交的事务使用了A表，另外一个会话对A表进行alter，出现等待表元数据锁

 

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

alter table 语句会等待表元数据锁，直到 insert into … select 语句结束。

 

不是传说5.6支持在线DDL么？怎么还会等待表元数据锁？

后来想想，在线DDL应该是指在alter table进行的时候，插入//删除数据的sql语句不会等待表元数据锁.

 

MySQL 5.6 增强了许多其他类型的 ALTER TABLE 操作以避免复制表。 

另一项增强功能允许在更改表时继续执行 SELECT 查询以及 INSERT、UPDATE 和 DELETE (DML) 语句。 

这种功能组合现在被称为在线 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                    |+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+

 

 

 

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

 

结论：

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

bitsCN.com