? ? ??想必玩過mysql的人對Waiting for table metadata lock肯定不會(huì)陌生，一般都是進(jìn)行alter操作時(shí)被堵住了，導(dǎo)致了我們在show processlist 時(shí)，看到線程的狀態(tài)是在等metadata lock。本文會(huì)對mysql 的metadata lock做一個(gè)小小的總結(jié)，希望對大家有所幫助。

? ? ?MDL是在5.5才引入到mysql，之前也有類似保護(hù)元數(shù)據(jù)的機(jī)制，只是沒有明確提出MDL概念而已。但是5.5之前版本(比如5.1)與5.5之后版本在保護(hù)元數(shù)據(jù)這塊有一個(gè)顯著的不同點(diǎn)是，5.1對于元數(shù)據(jù)的保護(hù)是語句級別的，5.5對于metadata的保護(hù)是事務(wù)級別的。所謂語句級別，即語句執(zhí)行完成后，無論事務(wù)是否提交或回滾，其表結(jié)構(gòu)可以被其他會(huì)話更新；而事務(wù)級別則是在事務(wù)結(jié)束后才釋放MDL。

? ? ?引入MDL后，主要解決了2個(gè)問題，一個(gè)是事務(wù)隔離問題，比如在可重復(fù)隔離級別下，會(huì)話A在2次查詢期間，會(huì)話B對表結(jié)構(gòu)做了修改，兩次查詢結(jié)果就會(huì)不一致，無法滿足可重復(fù)讀的要求；另外一個(gè)是數(shù)據(jù)復(fù)制的問題，比如會(huì)話A執(zhí)行了多條更新語句期間，另外一個(gè)會(huì)話B做了表結(jié)構(gòu)變更并且先提交，就會(huì)導(dǎo)致slave在重做時(shí)，先重做alter，再重做update時(shí)就會(huì)出現(xiàn)復(fù)制錯(cuò)誤的現(xiàn)象。

? ? ?5.5以后，什么情況下會(huì)碰到MDL鎖，我結(jié)合實(shí)際情況舉3個(gè)會(huì)出現(xiàn)MDL的場景，來分析MDL加鎖時(shí)機(jī)。下文的.測試都是以mysql 5.5這個(gè)版本為基準(zhǔn)，沒有考慮到online ddl，下一篇博文會(huì)詳細(xì)介紹5.6的online ddl。

1.大查詢或mysqldump導(dǎo)致alter等待MDL

時(shí)間點(diǎn)	會(huì)話A	會(huì)話B	會(huì)話C
1	Select count(*) from t;	?	?
2	?	alter table t add column c3 int;	?
3	?	?	Show processlist； B：copy to tmp table
4	?	?阻塞	Show processlist; B：Waiting for table metadata lock ?
5	?A：執(zhí)行完畢	?	?
6	?	?	Show processlist;? B：rename table
7	Select count(*) from t;	?	?
8	?	?B：執(zhí)行完畢	?
9	?	?	Show processlist; ? A: Sending data ?
10	A:執(zhí)行完畢	?	?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 表1

? ? ? ?從表1可以看到，會(huì)話A先執(zhí)行select ,B后執(zhí)行alter,在會(huì)話A執(zhí)行完畢前，會(huì)話B拿不到MDL鎖，從表格上面來看，主要阻塞在rename階段。A會(huì)話在時(shí)間點(diǎn)5執(zhí)行完畢后，會(huì)話B拿到MDL鎖，變?yōu)閞ename table狀態(tài)，這個(gè)操作持續(xù)時(shí)間非常短，時(shí)間點(diǎn)7，A會(huì)話再次執(zhí)行查詢，當(dāng)B執(zhí)行完后，此時(shí)A正常執(zhí)行。這說明對于MDL鎖而言，select會(huì)阻塞alter，而alter不會(huì)阻塞select。在rename的瞬間，alter是會(huì)阻塞select的，詳細(xì)請參考《mysql metadata lock(二)》

2.表上存在未提交的事務(wù)，導(dǎo)致alter等待MDL

時(shí)間點(diǎn)	會(huì)話A	會(huì)話B	會(huì)話C
1	set autocommit=0; update t set c2='9999' where c1=4;	?	?
2	?	alter table t drop column c3;	?
3	?	?	Show processlist; B：Waiting for table metadata lock
4	A：提交事務(wù) commit	?	?
5	?	?	Show processlist； B：copy to tmp table
6	?	B：繼續(xù)執(zhí)行	?
7	update t set c2='9999' where c1=4;阻塞	?	?
8	?	?	Show processlist; A: Waiting for table metadata lock B: copy to tmp table
9	?	B執(zhí)行完畢	?
10	A執(zhí)行完畢	?	?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 表2

? ? ? ?從表2可以看到，會(huì)話A第一次執(zhí)行update語句后，未提交，導(dǎo)致后面會(huì)話B執(zhí)行alter語句時(shí)需要等待MDL鎖；時(shí)間點(diǎn)4，A會(huì)話提交事務(wù)，此時(shí)會(huì)話B獲取MDL鎖，開始執(zhí)行；時(shí)間點(diǎn)7，A會(huì)話再次發(fā)起update操作，此時(shí)A會(huì)話被阻塞住，這說明對于MDL鎖而言，update會(huì)阻塞alter，同樣alter也會(huì)阻塞update。

PS：時(shí)間點(diǎn)3由于通過show processlist只看到alter被阻塞了，但不清楚被誰阻塞，可以通過查看information_schema.innodb_trx可以找到活動(dòng)的事務(wù)。

3.這種情況是第1種情況的特例，存在一個(gè)查詢失敗的語句，比如查詢不存在的列，語句失敗返回，但是事務(wù)沒有提交，此時(shí)alter仍然會(huì)被堵住。

時(shí)間點(diǎn)	會(huì)話A	會(huì)話B	會(huì)話C
1	Start transaction； Select c99 from t； Unknown column 'c99' in 'field list'	?	?
2	?	alter table t drop column c3;	?
3	?	?	Show processlist； B：copy to tmp table
4	?	?	Show processlist; B：Waiting for table metadata lock
5	A：提交事務(wù) commit	?	?
6	?	執(zhí)行完畢	?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 表3

? ? ??這里注意時(shí)間1，會(huì)話A要顯示開啟一個(gè)事務(wù)，否則查詢會(huì)隱式回滾結(jié)束，無法重現(xiàn)上面的場景。會(huì)話B執(zhí)行alter后，沒有立即阻塞住，而是立馬開始copy to tmp table，這個(gè)過程結(jié)束后，才進(jìn)行了MDL鎖等待。這怎么解釋呢，應(yīng)該是執(zhí)行alter操作主要分為創(chuàng)建臨時(shí)新表->插入老表的數(shù)據(jù)->臨時(shí)新表rename to老表三個(gè)步驟，在這種情況下，到最后一步才需要MDL鎖，所以copy過程中不會(huì)阻塞。由于沒有查詢在進(jìn)行，而且查詢也沒有進(jìn)入innodb層 (失敗返回)，所以show processlist和information_schema.innodb_trx沒有可以參考的信息。

? ? ?這里有一個(gè)小疑點(diǎn)，對于第二種情況，alter在開始時(shí)就立馬堵住了，第一種和第三種情況是copy結(jié)束后，才堵住。通過多次實(shí)驗(yàn)，確實(shí)發(fā)現(xiàn)第二種情況在opening tables就堵住了。為什么要這樣具體原因還沒弄清楚，有興趣的同學(xué)可以去debug源碼看看究竟。

root@chuck 11:57:41>show profile for query 4;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000050 |
| checking permissions | 0.000004 |
| checking permissions | 0.000005 |
| init | 0.000007 |
| Opening tables | 19.068828 |
| System lock | 0.000011 |
| setup | 0.000034 |
| creating table | 0.005047 |
| After create | 0.000056 |
| copy to tmp table | 89.574539 |
| rename result table | 1.101672 |
| end | 0.000040 |
| query end | 0.000004 |
| closing tables | 0.000009 |
| freeing items | 0.000021 |
| logging slow query | 0.000002 |
| logging slow query | 0.000090 |
| cleaning up | 0.000004 |
+----------------------+-----------+

?

參考：

http://www.mysqlperformanceblog.com/2013/02/01/implications-of-metadata-locking-changes-in-mysql-5-5/

http://ctripmysqldba.iteye.com/blog/1938150

?

總結(jié)

以上是生活随笔為你收集整理的mysql metadata lock(一)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。