MYSQL 鎖:metadata lock
參考:http://www.cnblogs.com/cchust/p/3826398.html
metadata lock的超時時間是lock_wait_timeout，並不是innodb_lock_wait_timeout
MySQL 5.5.3版本中引入了Metadata lock: DDL語句打破了事務的隔離級別
在5.5.3版本之前,MySQL事務對於表結構後設資料(Metadata)的鎖定是語句(statement)粒度的
即語句執行完成後，不管事務是否可以完成，其表結構就可以被其他會話更新掉！
引入Metadata lock後，表結構後設資料(Metadata)的鎖定變成了事務(transaction)粒度的
即只有事務結束時才會釋放Metadata lock。
現象：
顯式開啟事務後start transaction; 該事務內的query語句(包含select)會佔用相關表的metadata lock(profile:Opening tables階段)
導致DDL語句被阻塞，因為獲取不到表的metadata lock
MySQL 5.6.6版本後 最佳化
metadata lock不阻塞DDL語句，但原有session再訪問此表時會返回Error資訊“Table definition has changed, please retry transaction”
具體案例：
引入Metadata lock之前(5.5.3版本以前)
5.5.3之前的Metadata處理方式，有如下問題：
1）隔離級別會被破壞
例如：理論上REPEATABLE-READ隔離級別下，一個事務內同樣的查詢能夠讀到同樣的內容，如果沒有新記錄的話。但是由於沒有事務級別的Metadata鎖，這個隔離級別下就不能滿足這樣的約定：
5.1.51版本中執行：
+—————–+
| @@tx_isolation |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)Session1 mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
Session1 mysql> select * from table1 where id=1;
+——+——+——+——-+
| id | one | two | three |
+——+——+——+——-+
| 1 | 34 | 45 | 80 |
+——+——+——+——-+
1 row in set (0.00 sec)
Session2 mysql> alter table table1 add column c1 int;
Query OK, 4 rows affected (0.23 sec)
Records: 4 Duplicates: 0 Warnings: 0
Session1 mysql> select * from table1 where id=1;
Empty set (0.00 sec)
可以看到Session1的事務內部，雖然隔離級別是“可重複讀”，但是由於併發的Session2中途成功更改了table1的結構(因為前一個SQL查完table1後就釋放了對錶結構的約束)，兩次查詢的結果不同！
遺留問題1.為什麼第二次查詢，記錄沒有發生變化，但結果集返回空？
2）可能導致複製的失敗
上例的影響還比較明顯，這種機制還有隱藏的危害，即可能影響複製的成功進行！
我們知道，binlog內操作的記錄是基於事務的提交順序進行的。與上例類似，ALTER語句後執行但可能先提交，這影響到了執行中的其他事務，而那些事務後提交。而SLAVE看來完全是ALTER先執行，這很可能導致被打斷的事務在SLAVE上重現時導致失敗，要麼表結構不對，要麼資料導致不一致。
引入Metadata lock之後(5.5.3版本及以後)
而5.5.3中引入了Metadata lock後，由於事務結束後才會釋放，避免了這樣的問題。還是上例，在5.5.30版本中執行：
+—————–+
| @@tx_isolation |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)Session1 mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
Session1 mysql> select * from table1 where id=1;
+——+——+——+——-+
| id | one | two | three |
+——+——+——+——-+
| 1 | 34 | 45 | 80 |
+——+——+——+——-+
1 row in set (0.00 sec)
Session2 mysql> alter table table1 add column c1 int;
這個語句一直等待。
遺留問題2：為什麼沒有鎖等待超時？？
我們再開一個會話檢視當前狀況：
Session3 mysql>show full processlist;
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| Id | User | Host | db | Command | Time | State | Info |
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| 163612 | sup | 192.168.2.213:1844 | dbadb | Sleep | 37 | | NULL |
| 163613 | sup | 192.168.2.213:1883 | dbadb | Query | 21 | Waiting for table metadata lock | alter table table1 add column c3 int |
| 163614 | sup | 192.168.2.213:1884 | dbadb | Query | 0 | NULL | show full processlist |
+——–+———–+———————-+—————+———+——+———————————+————————————–+
這裡的示例中，我們可以看到ALTER TABLE會應Metadata lock而阻塞。一旦Session 1中的事務提交或者回滾，即釋放了table1表的Metadata lock，Session2的操作立即可以執行。
那麼究竟是怎樣的操作會被Metadata lock影響到呢？我們可以藉助看看到底ALTER命令在哪裡卡住了：
Session2 mysql> show profile;
+——————————+———-+
| Status | Duration |
+——————————+———-+
| starting | 0.000093 |
| checking permissions | 0.000062 |
| checking permissions | 0.000058 |
| init | 0.000058 |
| Opening tables | 0.000078 |
| System lock | 0.000061 |
| setup | 0.000077 |
| creating table | 0.053156 |
| After create | 0.000111 |
| copy to tmp table | 0.000231 |
| rename result table | 5.780077 |
| end | 0.000545 |
| Waiting for query cache lock | 0.000064 |
| end | 0.000126 |
| query end | 0.000090 |
| closing tables | 0.000110 |
| freeing items | 0.000118 |
| logging slow query | 0.000095 |
| logging slow query | 0.000125 |
| cleaning up | 0.000090 |
+——————————+———-+
20 rows in set (0.02 sec)
可以看到，卡住的那一步是在rename result table，MySQL後臺將ALTER儲存變成連續操作“建立臨時新表->插入老表的資料->臨時新表取到老表(RENAME)”，即實際的變化老表的操作在RENAME階段才發生。而這一步即是被阻塞的那一步。所以可以確認，Metadata lock影響到的是要真實修改表結構的動作。
基於此，我們可以驗證ALTER TABLE,RENAME TABLE,DROP TABLE都會被Metadata lock影響，驗證步驟與上類似，省略。
值得注意的是，建立一個已存在的表也會被Metadata lock影響。如果在一個事務中用到了表A的Metadata lock（如SELECT了該表）且事務未完成，那麼建立同名的表也會被卡住。所參考的文章中提及這一點。有興趣可參看： ，這裡不作討論。
metadata lock的另一個副作用：如上例中，ALTER TABLE應metadata lock被阻塞後，這導致後續其他事務針對該表的SELECT也會被阻塞！即ALTER TABLE的操作會影響到其他SELECT操作。根據上述示例：
Session2 mysql> alter table table1 add column c1 int;
此步一直處於等待狀態時，新會話中查詢table1。
Session3 mysql> select * from table1 where id=1; #一直等待
Session4 mysql>show full processlist;
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| Id | User | Host | db | Command | Time | State | Info |
+——–+———–+———————-+—————+———+——+———————————+————————————–+
| 163612 | sup | 192.168.2.213:1844 | dbadb | Sleep | 45 | | NULL |
| 163613 | sup | 192.168.2.213:1883 | dbadb | Query | 35 | Waiting for table metadata lock | alter table table1 add column c1 int |
| 163614 | sup | 192.168.2.213:1884 | dbadb | Query | 0 | NULL | show full processlist |
| 163615 | sup | 192.168.2.213:2142 | dbadb | Query | 16 | Waiting for table metadata lock | select * from table1 where id=1 |
+——–+———–+———————-+—————+———+——+———————————+————————————–+
Session1 mysql> commit;
第一個會話提交後，Session2、Session3也成功執行。看看Session3的select在哪個步驟需要等待：
Session3 mysql> show profile;
+——————————–+———–+
| Status | Duration |
+——————————–+———–+
| starting | 0.000018 |
| Waiting for query cache lock | 0.000003 |
| checking query cache for query | 0.000037 |
| checking permissions | 0.000007 |
| Opening tables | 22.502591 |
| System lock | 0.000013 |
| Waiting for query cache lock | 0.000024 |
| init | 0.000022 |
| optimizing | 0.000009 |
| statistics | 0.000011 |
| preparing | 0.000015 |
| executing | 0.000003 |
| Sending data | 0.000036 |
| end | 0.000009 |
| query end | 0.000005 |
| closing tables | 0.000006 |
| freeing items | 0.000007 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000015 |
| Waiting for query cache lock | 0.000002 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000004 |
| logging slow query | 0.000002 |
| logging slow query | 0.000028 |
| cleaning up | 0.000003 |
+——————————–+———–+
可以看到SELECT語句首先檢查查詢快取，沒有命中，然後在Opening tables階段卡住。
這引出了另一個特點，基於上述場景，如果後續的SELECT能夠在查詢快取中命中，那麼不會被ALTER TABLE卡住，因為不需要Opening tables操作。命中快取SELECT的SHOW PROFILE如下：
mysql> show profile;
+——————————–+———-+
| Status | Duration |
+——————————–+———-+
| starting | 0.000017 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000006 |
| checking privileges on cached | 0.000004 |
| checking permissions | 0.000007 |
| sending cached result to clien | 0.000011 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+——————————–+———-+
8 rows in set (0.00 sec)
遺留問題1.為什麼第二次查詢，記錄沒有發生變化，但結果集返回空？
這個問題在進行了討論，確認原因是：
MySQL中ALTER操作實際後臺是“建立新表”->”複製老表資料”->”RENAME新表成老表”。InnoDB引擎針對客戶端的REPEATABLE READ隔離級別，採用的是基於多版本(Multi-version)功能的”一致性讀”，即在事務中第一個查詢時，獲取的是當時版本的表的快照，而ALTER操作後實際是建立的新表，這個新表對於之前的快照而言是沒有資料的，所以第二次查詢新表基於快照的資料，返回空。在MySQL 5.6.6版本後，這樣的第二個查詢會返回Error資訊“Table definition has changed, please retry transaction”。詳細請看這裡。
遺留問題2.為什麼沒有鎖等待超時？？
我們知道InnoDB行鎖的等待時間超過innodb_lock_wait_timeout就會報超時錯誤。這裡的metadata lock一直等待著，為什麼沒有超時呢？其實metadata lock的超時設定是另一個系統變數lock_wait_timeout。
lock_wait_timeout控制著所有涉及到metadata lock的操作，包括DML and DDL，以及在表、檢視、儲存過程、儲存函式上的操作，以及LOCK TABLES、FLUSH TABLES WITH READ LOCK、HANDLER等。
lock_wait_timeout的預設設定時一年，可以動態設定，根據實際場景進行調整。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1379810/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Mysql】metadata lock鎖MySql
- mysql觀測METADATA LOCK(MDL)鎖MySql
- mysql metadata lock後設資料鎖之鎖狀態lock_status流轉圖MySql
- 【MySQL】MetaData Lock 之一MySql
- 【MySQL】MetaData Lock 之二MySql
- 【MySQL】MetaData Lock 之三MySql
- MYSQL METADATA LOCK(MDL LOCK)學習(1) 理論知識和加鎖型別測試MySql型別
- mysql metadata lock原理與實現MySql
- MySQL metadata鎖實驗MySql
- MySQL 5.6 metadata lock 原始碼解讀MySql原始碼
- MySQL 5.6 drop database時,table metadata lock等待MySqlDatabase
- mysql innodb lock鎖之record lock之一MySql
- MySQL新增索引偶遇waiting for table metadata lockMySql索引AI
- MySQL DDL Waiting for table metadata lock 解決MySqlAI
- mysql表鎖與lock tablesMySql
- MySQL 共享鎖 (lock in share mode)，排他鎖 (for update)MySql
- MySQL-lock(鎖)-v2.0MySql
- MySQL 的next-lock 鎖MySql
- Waiting for table metadata lockAI
- MySQL 5.5 -- innodb_lock_wait 鎖 等待MySqlAI
- innodb_lock_monitor解決mysql死鎖MySql
- MySQL表結構變更引起的Metadata Lock|如何定位DDL被阻塞MySql
- oracle lock鎖_v$lock_轉Oracle
- create table if not exists Waiting for table metadata lockAI
- lock鎖和monitor.enter鎖
- 【JavaSE】Lock鎖和synchronized鎖的比較，lock鎖的特性，讀寫鎖的實現。Javasynchronized
- MYSQL INNODB replace into 死鎖 及 next key lock 淺析MySql
- 如何開啟和關閉MySQL 間隙鎖(gap lock)MySql
- MYSQL中一個特殊的MDL LOCK死鎖案列MySql
- MySQL next-key lock 加鎖範圍是什麼？MySql
- mysql lock操作MySql
- 帶你理解Lock鎖原理
- Lock鎖相關以及AQSAQS
- Lock、Synchronized鎖區別解析synchronized
- TX鎖（Transaction Lock）分析 (zt)
- MySQL自增鎖模式innodb_autoinc_lock_mode引數詳解MySql模式
- Mysql加鎖過程詳解（8）-理解innodb的鎖(record,gap,Next-Key lock)MySql
- java的乾兒子鎖LockJava