MySQL加鎖原理

對於MySQL 的加鎖規則，筆者之前瞭解到的也只是行鎖的概念。除了業務程式碼中使用的樂觀鎖、悲觀鎖，也沒有深入瞭解MySQL 真正加鎖規則。也不知道MySQL 在可重複讀隔離級別下，為什麼可能會出現幻讀。今天的這篇文章來詳細描述一下其原理。（本文所有Demo都基於可重複讀隔離級別）

首先我們來建立一張表，並寫入幾條語句。

CREATE TABLE `t` （ `id` int（11） NOT NULL， `c` int（11） DEFAULT NULL， `d` int（11） DEFAULT NULL， PRIMARY KEY （`id`）， KEY `c` （`c`）） ENGINE=InnoDB；insert into t values（0，0，0），（5，5，5），（10，10，10），（15，15，15），（20，20，20），（25，25，25）；

解決幻讀

我們知道的是，在執行更新語句的時候，MySQL 會加行鎖。但是僅僅加行鎖的話，可能是鎖不住的（因為此時記錄中可能不存在被鎖的行），此時MySQL 裡面還有一個間隙鎖（Gap Lock）的概念。上面的建表語句會形成如下圖所示的間隙：

當我們執行如下SQL 語句時，就不止給已經存在的6 條記錄加行鎖，還要給 7 個間隙加鎖（因為 d 欄位沒有索引，SQL 中被掃描到的行都會加鎖，因此是所有記錄都會加鎖）。這樣就保證了無法再寫入 d=5 的記錄了，從而保證了SQL 的語義正確。

mysql> select * from t where d = 5 for update；

對於行鎖來講，我們知道讀鎖和寫鎖的互斥關係如下。而對於間隙鎖來講，兩個間隙鎖之間並不互斥，互斥的是間隙鎖和往間隙裡面插入記錄的操作。

比如我們執行如下的SQL，然後會發現後面執行的語句並不會被 Blocked。Session A 加的間隙鎖是（5，10），當Session B再加間隙鎖時，也是可以加上的。它們功能的目標就是保護這個間隙不被寫入資料，它們之間並不衝突。

間隙鎖和行鎖被統稱為 next-key lock，每個 next-key lock 都是一個前開後閉的區間。如上面的 Demo 就會有 7 個next-key lock：（-∞， 0］、（0， 5］、（5， 10］、（10， 15］、（15， 20］、（20， 25］、（25， +supremum］。間隙鎖解決了一些問題，但同時也帶來了其他的一些問題。

當 Session A 和 Session B 同時執行相同的事務處理時，可能會造成 B 的 insert 被A 的間隙鎖（5，10）擋住，A 的 insert 被 B 的間隙鎖（5，10）擋住，從而造成了死鎖。由此看來，間隙鎖造成了更大程度上面的鎖，從而會影響系統的併發度。

加鎖規則

原則1：加鎖的基本單位是 next-key lock （前開後閉）。

原則2：查詢過程中訪問到的物件才會被加鎖。

最佳化1：唯一索引上的等值查詢，next-key lock 會退化為行鎖。

最佳化2：普通索引上的等值查詢，向右遍歷最後一個不滿足等值條件時，next-key lock 會退化為間隙鎖。

一個 bug：唯一索引上的範圍查詢，會訪問到不滿足條件的第一個值為止。

Demo1：等值查詢間隙鎖

因為表 t 中沒有 id=7 的記錄，1、根據原則1 Session A 的加鎖範圍就是 next-key lock （5， 10］；2、根據最佳化2，id=10 不滿足條件，next-key 會退化為間隙鎖， Session A 最終的加鎖範圍就是（5， 10）。所以 Session B 寫入 id=8 的記錄會被鎖住，而 Session C 對 id=10 的更新會執行成功。

Demo2：非唯一索引等值鎖

這裡感覺是不是很怪，覺得 Session B 也應該被鎖住。（注意一下 Session A 裡面是對 c = 5加了鎖）

1、根據原則1，next-key lock 是（5， 10］；2、c 是普通索引，掃描到 c=5 時需要繼續往後掃描，next-key lock 是（5， 10］，因為是等值查詢，根據最佳化 2 此時會退化為間隙鎖（5， 10）；3、根據原則 2，只有訪問到的物件才會被加鎖，因為 Session A 使用到了覆蓋索引（只訪問了 id），因此主鍵上面並沒有加鎖。這就是為什麼 Session B 為什麼會成功，而 Session C 被（5， 10）的間隙鎖鎖住了。

在這個Demo 中還有一個需要注意的是，Session A 裡面使用的是 lock in share mode，它只鎖覆蓋索引。如果使用的是 for update，則滿足條件的主鍵也會被加鎖。

Demo3：主鍵索引範圍鎖

看Demo 之前我們先看一下下面 2 條語句的加鎖過程，從邏輯上看是相同的，但是加鎖過程它們是不同的。

mysql> select * from t where id=10 for update；mysql> select * from t where id>=10 and id<11 for update；

1、Session A 開始執行時，next-key lock 是（5， 10］，查詢在（5， 10］段是等值查詢，因此退化為行鎖；2、後續的範圍查詢，next-key lock 是（10， 15］。因此最終的加鎖是 id=10 的行鎖和（10， 15］的next-key lock。這裡面可能會看著有些怪，範圍查詢不是不最佳化的麼？這裡面需要注意的是，是範圍查詢還是等值查詢，也需要先按區間段去切割一下。

Demo4：非唯一索引範圍鎖

1、對於 Session A，首先有（5， 10］的next-key lock ，但是最後一個 c = 10 滿足條件，因此不會走最佳化2，還是（5， 10］的next-key lock；2、在（10， 15］上面是範圍查詢，因此還是（10， 15］的next-key lock。最後Session A 的加鎖就是（5， 10］和（10， 15］的 next-key lock。

Demo5：唯一索引範圍鎖bug

1、對於 Session A 來說，會有（10， 15］的 next-key lock，但是對於唯一索引來說應該不會繼續往後查詢了，但是還會繼續；2、其還會在（15， 20］上面加上 next-key lock。因此出現了上圖的結果。

Demo6：非唯一索引上存在“等值”的例子

在最上面給出的基礎資料之上，再增加下面的一條資料。

mysql> insert into t values（30，10，30）；

寫入之後，對於索引 c 來講有如下結構。可以看出資料之間的間隙又增加了。

對於 delete 語句，其執行加鎖的規則和 select 。。。 for update 是類似的。

1、Session A 開始，會有一個（（c=5，id=5），（c=10，id=10）］的 next-key lock，因為最後一個值滿足條件，因此不會退化；2、一直向後遍歷到（c=15，id=15）時，該值不滿足條件，因此（（c=10，id=30），（c=15，id=15）］的 next-key lock 會退化為間隙鎖；最終結果如下圖所示：