My Sql 中要Alter Table的同学请注意！！！_MySQL

首先我建议你在对MySQL表做DDL操作时：

 

1 执行 show processlist 查看，要操作的表（数据库对象）是否处于锁状态

 

1  if("未锁定")

2 {

3      执行DDL语句

4 }else    

5 {

6      三思后行

7 }

 

作为一个程序猿，随着开发的进行，我们要面临需求的变更。

 

随之而来的有可能就是表结构的变化--字段的增加，字段数据类型的更新。

 

此时此刻，我就在Alter Table面前跪了。

 

My Sql 中 Waiting for table metadata lock，主要发生在你在Alter 一个表时，在这个表上有未完成的查询或者操作，其独占了metalock状态下，alter 操作就会等待这个锁释放，接下来是一段漫长的block之旅

 

以下是mysql 官方对于Metadata Locking概念的解释：

 

MySQL 5.5.3 and up uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).      

 

Metadata locking 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 cache, and its mutexes 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 explicitly or implicitly started 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;

The server holds metadata locks on both t and nt until the transaction ends. If another session attempts a DDL or write lock 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 ...;

LOCK TABLE t ... WRITE;

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.

 

此时此刻，我唯一想说的就是，假如我在犯了这个错误之前就知道这个真相是多么幸福的事。。。