【來自：http://dinglin.iteye.com/blog/1884696】 背景

???? 有同學(xué)討論到MySQL 5.5下給大表加主鍵時(shí)會(huì)鎖住讀的問題，懷疑與fast index creation有關(guān)，這里簡單說明下。

對照現(xiàn)象

???????? 為了說明這個(gè)問題的原因，有興趣的同學(xué)可以做對比實(shí)驗(yàn)。

??? 1) ?在給InnoDB表創(chuàng)建主鍵期間，會(huì)鎖住該表上的讀數(shù)據(jù)

??? 2) 但是同樣的表執(zhí)行刪除主鍵期間，不會(huì)鎖住該表上的讀操作

----這說明與是否fast index creation無關(guān)，因?yàn)檫@兩個(gè)操作在數(shù)據(jù)層面的行為應(yīng)該是類似的，實(shí)際上，創(chuàng)建/刪除主鍵都必須copy data。

??? 3) 在創(chuàng)建主鍵期間，會(huì)鎖住該表上執(zhí)行的show create table

----用1、3的現(xiàn)象可以猜測出，實(shí)際上與meta data lock有關(guān)。

關(guān)于meta data lock(MDL)

???????? MySQL 5.5中引入了MDL，當(dāng)需要訪問、修改表結(jié)構(gòu)時(shí)，都需要對meta data加鎖（讀或?qū)?#xff09;。比如，當(dāng)一個(gè)線程需要修改表結(jié)構(gòu)的任意一部分時(shí)，此時(shí)需要阻塞對表結(jié)構(gòu)的訪問，當(dāng)然也需要阻塞對數(shù)據(jù)行的訪問。

加主鍵流程

???????? 當(dāng)對一個(gè)表作加主鍵操作時(shí)，大致流程如下

??????? 1) MDL加寫鎖

?????? 2) 操作數(shù)據(jù)，最耗時(shí)部分，注意需要copy data，因此流程上是

???????????? a)創(chuàng)建一個(gè)臨時(shí)表A，表A定義為修改后的表結(jié)構(gòu)

???????????? b)從原表讀取數(shù)據(jù)插入表A

???????????? c)刪除原表，將表A重命名為原表名

?????? 3)? MDL釋放寫鎖

從這個(gè)流程可以看到，在最耗時(shí)的部分，meta data是被一個(gè)X鎖保護(hù)的。因此在此期間，show create table 或者select data都是會(huì)被阻塞。

這解釋了上面的1) 3)。

刪除主鍵流程

??????? 1)? MDL加讀鎖

?????? 2)? 操作數(shù)據(jù)，最耗時(shí)部分

???????????? a) 創(chuàng)建一個(gè)臨時(shí)表A，表A定義為修改后的表結(jié)構(gòu)

???????????? b) 從原表讀取數(shù)據(jù)插入表A

??????? 3) MDL將讀鎖升級為寫鎖

??????????? c) 刪除原表，將表A重命名為原表名

?????? 4)? MDL釋放寫鎖

?? 這個(gè)在最耗時(shí)的數(shù)據(jù)操作部分，加的是MDL的讀鎖，這樣不會(huì)影響訪問原表的表結(jié)構(gòu)或數(shù)據(jù)（當(dāng)然要做更新是不行的）。而最后升級為寫鎖的時(shí)間，只是做重命名表的操作，阻塞的時(shí)間就很短。

結(jié)論

?? ???? 1) 顯然第二個(gè)流程更合理

??????? 2) 這個(gè)可以認(rèn)為是MySQL一個(gè)可改進(jìn)的點(diǎn)，并且在5.6下已經(jīng)改進(jìn)

??????? 3) 這個(gè)問題與是copy data還是inplace方式執(zhí)行DDL無關(guān)，實(shí)際上由于InnoDB的聚集索引組織結(jié)構(gòu)，增、刪主鍵都是必須得copy data的。

==========更新====

?有同學(xué)問說為什么在5.5 set old_alter_table=on;之后是不會(huì)阻塞讀的？ 因?yàn)榇蜷_old_alter_table之后，MySQL認(rèn)為這次無論如何是要copy data的，所以鎖用的是“刪除主鍵流程”的策略。

實(shí)際上無論old_alter_table是否打開，對主鍵操作都是必須copy data的，5.6的改進(jìn)就是基于這個(gè)判斷。

更多的Metadata lock 信息 請見：http://www.cnblogs.com/zuoxingyu/archive/2013/03/15/2960862.html

轉(zhuǎn)載于:https://www.cnblogs.com/zhoujinyi/archive/2013/06/14/3135122.html

總結(jié)

以上是生活随笔為你收集整理的MySQL5.5加主键锁读问题【转】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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