關於mysql的metadata lock

標籤：

昨天晚上上線，卻發現一個ddl語句長時間沒有生效

查processlist, 發現包括ddl語句在內的眾多查詢提示 “Waiting for table metadata lock”

唯一沒有該提示的查詢為一個全表查詢，並且Time項數值最大。

kill掉這個查詢的線程，後面的ddl語句正常進行了

 

之前一直聽說metadata lock，就是中繼資料鎖，也叫字典鎖或者表結構鎖。但是沒有遇到過。

後來又試了一下——只要在session1裡有未完成的增刪查改事務，如果在另一個session2中出現加表結構鎖的語句時，session2都會等待session1的事務完成後才繼續進行

在session2完成之前，所有的查詢都會被阻塞。

所以在有ddl操作之前，最好還是先看一下有沒有長時間沒完成的查詢，否則會影響資料的寫入。

 

附1： ddl語句加鎖策略與流程：

　　1）上MDL讀鎖
　　2）操作資料，最耗時，需copy data，簡易流程如下：
　　       a) 建立暫存資料表A，重定義A為修改後的表結構
　　       b) 從原表讀取資料插入到A表
　　3）將MDL讀鎖定擴大為寫鎖
      　　 c) 刪除原表，將A重新命名為原表名
　　4）釋放MDL寫鎖

 

附2：官網上的說明

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 

關於mysql的metadata lock