MySQ中如何加鎖

本篇文章給大家分享的是有關(guān)MySQ中如何加鎖，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

　　多版本并發(fā)控制

　　在MySQL默認(rèn)存儲(chǔ)引擎InnoDB中，實(shí)現(xiàn)的是基于多版本的并發(fā)控制協(xié)議——MVCC(Multi-Version Concurrency Control)(注：與MVVC相對(duì)的，是基于鎖的并發(fā)控制，Lock-Based Concurrency Control)。其中MVCC最大的好處是：讀不加鎖，讀寫不沖突。在讀多寫少的OLTP應(yīng)用中，讀寫不沖突是非常重要的，極大的提高了系統(tǒng)的并發(fā)性能，在現(xiàn)階段，幾乎所有的RDBMS，都支持MVCC。其實(shí)，MVCC就一句話總結(jié)：同一份數(shù)據(jù)臨時(shí)保存多個(gè)版本的一種方式，進(jìn)而實(shí)現(xiàn)并發(fā)控制。

　　當(dāng)前讀和快照讀

　　在MVCC并發(fā)控制中，讀操作可以分為兩類：快照讀與當(dāng)前讀。

　　快照讀(簡(jiǎn)單的select操作)：讀取的是記錄中的可見版本(可能是歷史版本)，不用加鎖。這你就知道第二個(gè)問(wèn)題的答案了吧。

　　當(dāng)前讀(特殊的select操作、insert、delete和update)：讀取的是記錄中最新版本，并且當(dāng)前讀返回的記錄都會(huì)加上鎖，這樣保證了了其他事務(wù)不會(huì)再并發(fā)修改這條記錄。

　　聚集索引

　　也叫做聚簇索引。在InnoDB中，數(shù)據(jù)的組織方式就是聚簇索引：完整的記錄，儲(chǔ)存在主鍵索引中，通過(guò)主鍵索引，就可以獲取記錄中所有的列。

　　最左前綴原則

　　也就是最左優(yōu)先，這條原則針對(duì)的是組合索引和前綴索引，理解：

　　1、在MySQL中，進(jìn)行條件過(guò)濾時(shí)，是按照向右匹配直到遇到范圍查詢(>,<,between,like)就停止匹配，比如說(shuō)a 1="" and="" b="2" c=""> 3 and d = 4 如果建立(a, b, c, d)順序的索引，d是用不到索引的，如果建立(a, b, d, c)索引就都會(huì)用上，其中a，b，d的順序可以任意調(diào)整。

　　2、= 和 in 可以亂序，比如 a = 1 and b = 2 and c = 3 建立(a, b, c)索引可以任意順序，MySQL的查詢優(yōu)化器會(huì)優(yōu)化索引可以識(shí)別的形式。

　　兩階段鎖

　　傳統(tǒng)的RDMS加鎖的一個(gè)原則，就是2PL(Two-Phase Locking，二階段鎖)。也就是說(shuō)鎖操作分為兩個(gè)階段：加鎖階段和解鎖階段，并且保證加鎖階段和解鎖階段不想交。也就是說(shuō)在一個(gè)事務(wù)中，不管有多少條增刪改，都是在加鎖階段加鎖，在 commit 后，進(jìn)入解鎖階段，才會(huì)全部解鎖。

　　隔離級(jí)別

　　MySQL/InnoDB中，定義了四種隔離級(jí)別：

　　Read Uncommitted：可以讀取未提交記錄。此隔離級(jí)別不會(huì)使用。

　　Read Committed(RC)：針對(duì)當(dāng)前讀，RC隔離級(jí)別保證了對(duì)讀取到的記錄加鎖(記錄鎖)，存在幻讀現(xiàn)象。

　　Repeatable Read(RR)：針對(duì)當(dāng)前讀，RR隔離級(jí)別保證對(duì)讀取到的記錄加鎖(記錄鎖)，同時(shí)保證對(duì)讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入(間隙鎖)，不存在幻讀現(xiàn)象。

　　Serializable：從MVCC并發(fā)控制退化為基于鎖的并發(fā)控制。不區(qū)別快照讀和當(dāng)前讀，所有的讀操作都是當(dāng)前讀，讀加讀鎖(S鎖)，寫加寫鎖(X鎖)。在該隔離級(jí)別下，讀寫沖突，因此并發(fā)性能急劇下降，在MySQL/InnoDB中不建議使用。

　　Gap鎖和Next-Key鎖

　　在InnoDB中完整行鎖包含三部分：

　　記錄鎖(Record Lock)：記錄鎖鎖定索引中的一條記錄。

　　間隙鎖(Gap Lock)：間隙鎖要么鎖住索引記錄中間的值，要么鎖住第一個(gè)索引記錄前面的值或最后一個(gè)索引記錄后面的值。

　　Next-Key Lock：Next-Key鎖時(shí)索引記錄上的記錄鎖和在記錄之前的間隙鎖的組合。

　　進(jìn)行分析

　　了解完以上的小知識(shí)點(diǎn)，我們開始分析第一個(gè)問(wèn)題。當(dāng)看到這個(gè)問(wèn)題的時(shí)候，你可能會(huì)毫不猶豫的說(shuō)，加寫鎖啊。這答案也錯(cuò)也對(duì)，因?yàn)橐阎獥l件太少。那么有那些需要已知的前提條件呢?

　　前提一：id列是不是主鍵?

　　前提二：當(dāng)前系統(tǒng)的隔離級(jí)別是什么?

　　前提三：id列如果不是主鍵，那么id列上有沒(méi)有索引呢?

　　前提四：id列上如果有二級(jí)索引，那么是唯一索引嗎?

　　前提五：SQL執(zhí)行計(jì)劃是什么?索引掃描?還是全表掃描

　　根據(jù)上面的前提條件，可以有九種組合，當(dāng)然還沒(méi)有列舉完全。

　　id列是主鍵，RC隔離級(jí)別

　　id列是二級(jí)唯一索引，RC隔離級(jí)別

　　id列是二級(jí)不唯一索引，RC隔離級(jí)別

　　id列上沒(méi)有索引，RC隔離級(jí)別

　　d列是主鍵，RR隔離級(jí)別

　　id列是二級(jí)唯一索引，RR隔離級(jí)別

　　id列是二級(jí)不唯一索引，RR隔離級(jí)別

　　id列上沒(méi)有索引，RR隔離級(jí)別

　　組合一：id主鍵 + RC

　　這個(gè)組合是分析最簡(jiǎn)單的，到執(zhí)行該語(yǔ)句時(shí)，只需要將主鍵id = 10的記錄加上X鎖。如下圖所示：

　　結(jié)論：id是主鍵是，此SQL語(yǔ)句只需要在id = 10這條記錄上加上X鎖即可。

　　組合二：id唯一索引 + RC

　　這個(gè)組合，id不是主鍵，而是一個(gè)Unique的二級(jí)索引鍵值。在RC隔離級(jí)別下，是怎么加鎖的呢?看下圖：

　　由于id是Unique索引，因此delete語(yǔ)句會(huì)選擇走id列的索引進(jìn)行where條件過(guò)濾，在找到id = 10的記錄后，首先會(huì)將Unique索引上的id = 10的記錄加上X鎖，同時(shí)，會(huì)根據(jù)讀取到的name列，回到主鍵索引(聚簇索引)，然后將聚簇索引上的name = 'e' 對(duì)應(yīng)的主鍵索引項(xiàng)加X鎖。

　　結(jié)論：若id列是Unique列，其上有Unique索引，那么SQL需要加兩個(gè)X鎖，一個(gè)對(duì)應(yīng)于id Unique索引上的id = 10的記錄，另一把鎖對(duì)應(yīng)于聚簇索引上的(name = 'e', id = 10)的記錄。

　　組合三：id不唯一索引+RC

　　該組合中，id列不在唯一，而是個(gè)普通索引，那么當(dāng)執(zhí)行sql語(yǔ)句時(shí)，MySQL又是如何加鎖呢?看下圖：

　　由上圖可以看出，首先，id列索引上，滿足id = 10查詢的記錄，均加上X鎖。同時(shí)，這些記錄對(duì)應(yīng)的主鍵索引上的記錄也加上X鎖。與組合er的唯一區(qū)別，組合二最多只有一個(gè)滿足條件的記錄，而在組合三中會(huì)將所有滿足條件的記錄全部加上鎖。

　　結(jié)論：若id列上有非唯一索引，那么對(duì)應(yīng)的所有滿足SQL查詢條件的記錄，都會(huì)加上鎖。同時(shí)，這些記錄在主鍵索引上也會(huì)加上鎖。

　　組合四：id無(wú)索引+RC

　　相對(duì)于前面的組合，該組合相對(duì)特殊，因?yàn)閕d列上無(wú)索引，所以在 where id = 10 這個(gè)查詢條件下，沒(méi)法通過(guò)索引來(lái)過(guò)濾，因此只能全表掃描做過(guò)濾。對(duì)于該組合，MySQL又會(huì)進(jìn)行怎樣的加鎖呢?看下圖：

　　由于id列上無(wú)索引，因此只能走聚簇索引，進(jìn)行全表掃描。由圖可以看出滿足條件的記錄只有兩條，但是，聚簇索引上的記錄都會(huì)加上X鎖。但在實(shí)際操作中，MySQL進(jìn)行了改進(jìn)，在進(jìn)行過(guò)濾條件時(shí)，發(fā)現(xiàn)不滿足條件后，會(huì)調(diào)用 unlock_row 方法，把不滿足條件的記錄放鎖(違背了2PL原則)。這樣做，保證了最后滿足條件的記錄加上鎖，但是每條記錄的加鎖操作是不能省略的。

　　結(jié)論：若id列上沒(méi)有索引，MySQL會(huì)走聚簇索引進(jìn)行全表掃描過(guò)濾。由于是在MySQl Server層面進(jìn)行的。因此每條記錄無(wú)論是否滿足條件，都會(huì)加上X鎖，但是，為了效率考慮，MySQL在這方面進(jìn)行了改進(jìn)，在掃描過(guò)程中，若記錄不滿足過(guò)濾條件，會(huì)進(jìn)行解鎖操作。同時(shí)優(yōu)化違背了2PL原則。

　　組合五：id主鍵+RR

　　該組合為id是主鍵，Repeatable Read隔離級(jí)別，針對(duì)于上述的SQL語(yǔ)句，加鎖過(guò)程和組合一(id主鍵+RC)一致。

　　組合六：id唯一索引+RR

　　該組合與組合二的加鎖過(guò)程一致。

　　組合七：id不唯一索引+RR

　　在組合一到組合四中，隔離級(jí)別是Read Committed下，會(huì)出現(xiàn)幻讀情況，但是在該組合Repeatable Read級(jí)別下，不會(huì)出現(xiàn)幻讀情況，這是怎么回事呢?而MySQL又是如何給上述語(yǔ)句加鎖呢?看下圖：

　　該組合和組合三看起來(lái)很相似，但差別很大，在改組合中加入了一個(gè)間隙鎖(Gap鎖)。這個(gè)Gap鎖就是相對(duì)于RC級(jí)別下，RR級(jí)別下不會(huì)出現(xiàn)幻讀情況的關(guān)鍵。實(shí)質(zhì)上，Gap鎖不是針對(duì)于記錄本身的，而是記錄之間的Gap。所謂幻讀，就是同一事務(wù)下，連續(xù)進(jìn)行多次當(dāng)前讀，且讀取一個(gè)范圍內(nèi)的記錄(包括直接查詢所有記錄結(jié)果或者做聚合統(tǒng)計(jì)), 發(fā)現(xiàn)結(jié)果不一致(標(biāo)準(zhǔn)檔案一般指記錄增多, 記錄的減少應(yīng)該也算是幻讀)。

　　那么該如何解決這個(gè)問(wèn)題呢?如何保證多次當(dāng)前讀返回一致的記錄，那么就需要在多個(gè)當(dāng)前讀之間，其他事務(wù)不會(huì)插入新的滿足條件的記錄并提交。為了實(shí)現(xiàn)該結(jié)果，Gap鎖就應(yīng)運(yùn)而生。

　　如圖所示，有些位置可以插入新的滿足條件的記錄，考慮到B+樹的有序性，滿足條件的記錄一定是具有連續(xù)性的。因此會(huì)在 [4, b], [10, c], [10, d], [20, e] 之間加上Gap鎖。

　　Insert操作時(shí)，如insert(10, aa)，首先定位到 [4, b], [10, c]間，然后插入在插入之前，會(huì)檢查該Gap是否加鎖了，如果被鎖上了，則Insert不能加入記錄。因此通過(guò)第一次當(dāng)前讀，會(huì)把滿足條件的記錄加上X鎖，還會(huì)加上三把Gap鎖，將可能插入滿足條件記錄的3個(gè)Gap鎖上，保證后續(xù)的Insert不能插入新的滿足 id = 10 的記錄，也就解決了幻讀問(wèn)題。

　　而在組合五，組合六中，同樣是RR級(jí)別，但是不用加上Gap鎖，在組合五中id是主鍵，組合六中id是Unique鍵，都能保證唯一性。一個(gè)等值查詢，最多只能返回一條滿足條件的記錄，而且新的相同取值的記錄是無(wú)法插入的。

　　結(jié)論：在RR隔離級(jí)別下，id列上有非唯一索引，對(duì)于上述的SQL語(yǔ)句;首先，通過(guò)id索引定位到第一條滿足條件的記錄，給記錄加上X鎖，并且給Gap加上Gap鎖，然后在主鍵聚簇索引上滿足相同條件的記錄加上X鎖，然后返回;之后讀取下一條記錄重復(fù)進(jìn)行。直至第一條出現(xiàn)不滿足條件的記錄，此時(shí)，不需要給記錄加上X鎖，但是需要給Gap加上Gap鎖嗎，最后返回結(jié)果。

　　組合八：id無(wú)索引+RR

　　該組合中，id列上無(wú)索引，只能進(jìn)行全表掃描，那么該如何加鎖，看下圖：

　　如圖，可以看出這是一個(gè)很恐怖的事情，全表每條記錄要加X鎖，每個(gè)Gap加上Gap鎖，如果表上存在大量數(shù)據(jù)時(shí)，又是什么情景呢?這種情況下，這個(gè)表，除了不加鎖的快照讀，其他任何加鎖的并發(fā)SQL，均不能執(zhí)行，不能更新，刪除，插入，這樣，全表鎖死。

　　當(dāng)然，和組合四一樣，MySQL進(jìn)行了優(yōu)化，就是semi-consistent read。semi-consistent read開啟的情況下，對(duì)于不滿足條件的記錄，MySQL會(huì)提前放鎖，同時(shí)Gap鎖也會(huì)釋放。而semi-consistent read是如何觸發(fā)：要么在Read Committed隔離級(jí)別下;要么在Repeatable Read隔離級(jí)別下，設(shè)置了 innodb_locks_unsafe_for_binlog 參數(shù)。

　　結(jié)論：在Repeatable Read隔離級(jí)別下，如果進(jìn)行全表掃描的當(dāng)前讀，那么會(huì)鎖上表上的所有記錄，并且所有的Gap加上Gap鎖，杜絕所有的 delete/update/insert 操作。當(dāng)然在MySQL中，可以觸發(fā) semi-consistent read來(lái)緩解鎖開銷與并發(fā)影響，但是semi-consistent read本身也會(huì)帶來(lái)其他的問(wèn)題，不建議使用。

　　組合九：Serializable

　　在最后組合中，對(duì)于上訴的刪除SQL語(yǔ)句，加鎖過(guò)程和組合八一致。但是，對(duì)于查詢語(yǔ)句(比如select * from T1 where id = 10)來(lái)說(shuō)，在RC，RR隔離級(jí)別下，都是快照讀，不加鎖。在Serializable隔離級(jí)別下，無(wú)論是查詢語(yǔ)句也會(huì)加鎖，也就是說(shuō)快照讀不存在了，MVCC降級(jí)為L(zhǎng)ock-Based CC。

　　結(jié)論：在MySQL/InnoDB中，所謂的讀不加鎖，并不適用于所有的情況，而是和隔離級(jí)別有關(guān)。在Serializable隔離級(jí)別下，所有的操作都會(huì)加鎖。

以上就是MySQ中如何加鎖，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。