REPEATABLE READ隔離級別下
採用加鎖的方式解決併發事務產生的問題時，REPEATABLE READ隔離級別與READ UNCOMMITTED和READ COMMITTED這兩個隔離級別相比，最主要的就是要解決幻讀問題，幻讀問題的解決還得靠我們之前講過的gap鎖。
對於使用主鍵進行等值查詢的情況
使用
SELECT ... LOCK IN SHARE MODE來為記錄加鎖，比方說：SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;我們知道主鍵具有唯一性，如果在一個事務中第一次執行上述語句時將得到的結果集中包含一條記錄，第二次執行上述語句前肯定不會有別的事務插入多條
number值為8的記錄（主鍵具有唯一性），也就是說一個事務中兩次執行上述語句並不會發生幻讀，這種情況下和READ UNCOMMITTED／READ COMMITTED隔離級別下一樣，我們只需要為這條number值為8的記錄加一個S型正經記錄鎖就好了，如圖所示：
但是如果我們要查詢主鍵值不存在的記錄，比方說：
SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;由於
number值為7的記錄不存在，為了禁止幻讀現象（也就是避免在同一事務中下一次執行相同語句時得到的結果集中包含number值為7的記錄），在當前事務提交前我們需要預防別的事務插入number值為7的新記錄，所以需要在number值為8的記錄上加一個gap鎖，也就是不允許別的事務插入number值在(3, 8)這個區間的新記錄。畫個圖表示一下：
如果在
READ UNCOMMITTED／READ COMMITTED隔離級別下一樣查詢了一條主鍵值不存在的記錄，那麼什麼鎖也不需要加，因為在READ UNCOMMITTED／READ COMMITTED隔離級別下，並不需要禁止幻讀問題。其餘語句使用主鍵進行等值查詢的情況與
READ UNCOMMITTED／READ COMMITTED隔離級別下的情況類似，這裡就不贅述了。
對於使用主鍵進行範圍查詢的情況
使用
SELECT ... LOCK IN SHARE MODE語句來為記錄加鎖，比方說：SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;因為要解決幻讀問題，所以需要禁止別的事務插入
number值符合number >= 8的記錄，又因為主鍵本身就是唯一的，所以我們不用擔心在number值為8的前邊有新記錄插入，只需要保證不要讓新記錄插入到number值為8的後邊就好了，所以：為
number值為8的聚簇索引記錄加一個S型正經記錄鎖。為
number值大於8的所有聚簇索引記錄都加一個S型next-key鎖（包括Supremum偽記錄）。
畫個圖就是這樣子：
小貼士： 為什麼不給Supremum記錄加gap鎖，而要加next-key鎖呢？其實設計InnoDB的大叔在處理Supremum記錄上加的next-key鎖時就是當作gap鎖看待的，只不過為了節省鎖結構（我們前邊說鎖的型別不一樣的話不能被放到一個鎖結構中）才這麼做的而已，大家不必在意。
與READ UNCOMMITTED/READ COMMITTED隔離級別類似，在REPEATABLE READ隔離級別下，下邊這個範圍查詢也是有點特殊：
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;這個語句的執行過程我們在之前嘮叨過，在READ UNCOMMITTED/READ COMMITTED隔離級別下，這個語句會為number值為1、3、8、15這4條記錄都加上S型正經記錄鎖，然後由於number值為15的記錄不滿足邊界條件number <= 8，隨後便把這條記錄的鎖釋放掉。在REPEATABLE READ隔離級別下的加鎖過程與之類似，不過會為1、3、8、15這4條記錄都加上S型next-key鎖，但是有一點需要大家十分注意：REPEATABLE READ隔離級別下，在判斷number值為15的記錄不滿足邊界條件 number <= 8 後，並不會去釋放加在該記錄上的鎖！！！ 所以在REPEATABLE READ隔離級別下，該語句的加鎖示意圖就如下所示：
這樣如果別的事務想要插入的新記錄的number值在(-∞, 1)、(1, 3)、(3, 8)、(8, 15)之間的話，是會進入等待狀態的。
小貼士： 很顯然這麼粗暴的做法導致的一個後果就是別的事務竟然不允許插入number值在(8, 15)這個區間中的新記錄，甚至不允許別的事務再獲取number值為15的記錄上的鎖，而理論上只需要禁止別的事務插入number值在(-∞, 8)之間的新記錄就好。
使用
SELECT ... FOR UPDATE語句來為記錄加鎖：和
SELECT ... LOCK IN SHARE MODE語句類似，只不過需要將上邊提到的S型next-key鎖替換成X型next-key鎖。使用
UPDATE ...來為記錄加鎖：如果
UPDATE語句未更新二級索引列，比方說：UPDATE hero SET country = '漢' WHERE number >= 8;這條
UPDATE語句並沒有更新二級索引列，加鎖方式和上邊所說的SELECT ... FOR UPDATE語句一致。如果
UPDATE語句中更新了二級索引列，比方說：UPDATE hero SET name = 'cao曹操' WHERE number >= 8;對聚簇索引記錄加鎖的情況和
SELECT ... FOR UPDATE語句一致，也就是對number值為8的聚簇索引記錄加X型正經記錄鎖，對number值15、20的聚簇索引記錄以及Supremum記錄加X型next-key鎖。但是因為也要更新二級索引idx_name，所以也會對number值為8、15、20的聚簇索引記錄對應的idx_name二級索引記錄加X型正經記錄鎖，畫個圖表示一下：
如果是下邊這個語句：
UPDATE hero SET name = 'cao曹操' WHERE number <= 8;則會對
number值為1、3、8、15的聚簇索引記錄加X型next-key，其中number值為15的聚簇索引記錄不滿足number <= 8的邊界條件，雖然在REPEATABLE READ隔離級別下不會將它的鎖釋放掉，但是也並不會對這條聚簇索引記錄對應的二級索引記錄加鎖，也就是說只會為number值為1、3、8的聚簇索引記錄對應的idx_name二級索引記錄加X型正經記錄鎖，加鎖示意圖如下所示：
使用
DELETE ...來為記錄加鎖，比方說：DELETE FROM hero WHERE number >= 8;和
DELETE FROM hero WHERE number <= 8;這兩個語句的加鎖情況和更新帶有二級索引列的
UPDATE語句一致，就不畫圖了。
對於使用唯一二級索引進行等值查詢的情況
由於hero表並沒有唯一二級索引，我們把原先的idx_name修改為一個唯一二級索引uk_name：
ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);使用
SELECT ... LOCK IN SHARE MODE語句來為記錄加鎖，比方說：SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;由於唯一二級索引具有唯一性，如果在一個事務中第一次執行上述語句時將得到一條記錄，第二次執行上述語句前肯定不會有別的事務插入多條
name值為'c曹操'的記錄（二級索引具有唯一性），也就是說一個事務中兩次執行上述語句並不會發生幻讀，這種情況下和READ UNCOMMITTED／READ COMMITTED隔離級別下一樣，我們只需要為這條name值為'c曹操'的二級索引記錄加一個S型正經記錄鎖，然後再為它對應的聚簇索引記錄加一個S型正經記錄鎖就好了，我們畫個圖看看：
注意加鎖順序，是先對二級索引記錄加鎖，再對聚簇索引加鎖。
如果對唯一二級索引列進行等值查詢的記錄並不存在，比如：
SELECT * FROM hero WHERE name = 'g關羽' LOCK IN SHARE MODE;為了禁止幻讀，所以需要保證別的事務不能再插入
name值為'g關羽'的新記錄。在唯一二級索引uk_name中，鍵值比'g關羽'大的第一條記錄的鍵值為l劉備，所以需要在這條二級索引記錄上加一個gap鎖，如圖所示：
注意，這裡只對二級索引記錄進行加鎖，並不會對聚簇索引記錄進行加鎖。
使用
SELECT ... FOR UPDATE語句來為記錄加鎖，比如：和
SELECT ... LOCK IN SHARE MODE語句類似，只不過加的是X型正經記錄鎖。使用
UPDATE ...來為記錄加鎖，比方說：與
SELECT ... FOR UPDATE的加鎖情況類似，不過如果被更新的列中還有別的二級索引列的話，這些對應的二級索引記錄也會被加X型正經記錄鎖。使用
DELETE ...來為記錄加鎖，比方說：與
SELECT ... FOR UPDATE的加鎖情況類似，不過如果表中還有別的二級索引列的話，這些對應的二級索引記錄也會被加X型正經記錄鎖。
對於使用唯一二級索引進行範圍查詢的情況
使用
SELECT ... LOCK IN SHARE MODE語句來為記錄加鎖，比方說：SELECT * FROM hero FORCE INDEX(uk_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;這個語句的執行過程其實是先到二級索引中定位到滿足
name >= 'c曹操'的第一條記錄，也就是name值為c曹操的記錄，然後就可以沿著由記錄組成的單向連結串列一路向後找。從二級索引idx_name的示意圖中可以看出，所有的使用者記錄都滿足name >= 'c曹操'的這個條件，所以所有的二級索引記錄都會被加S型next-key鎖，它們對應的聚簇索引記錄也會被加S型正經記錄鎖，二級索引的最後一條Supremum記錄也會被加S型next-key鎖。不過需要注意一下加鎖順序，對一條二級索引記錄加鎖完後，會接著對它響應的聚簇索引記錄加鎖，完後才會對下一條二級索引記錄進行加鎖，以此類推～ 畫個圖表示一下就是這樣：
稍等一下，不是說
uk_name是唯一二級索引麼？唯一二級索引本身就能保證其自身的值是唯一的，那為啥還要給name值為'c曹操'的記錄加上S型next-key鎖，而不是S型正經記錄鎖呢？其實我也不知道，按理說只需要給這條二級索引記錄加S型正經記錄鎖就好了，我也沒想明白設計InnoDB的大叔是怎麼想的，有知道的小夥伴趕緊新增我微信：xiaohaizi4919聯絡我哈（聊八卦的同學請勿新增）～再來看下邊這個語句：
SELECT * FROM hero WHERE name <= 'c曹操' LOCK IN SHARE MODE;這個語句先會為
name值為'c曹操'的二級索引記錄加S型next-key鎖以及它對應的聚簇索引記錄加S型正經記錄鎖。然後還要給name值為'l劉備'的二級索引記錄加S型next-key鎖，name值為'l劉備'的二級索引記錄不滿足索引條件下推的name <= 'c曹操'條件，壓根兒不會釋放掉該記錄的鎖就直接報告server層查詢完畢了。這樣可以禁止其他事務插入name值在('c曹操', 'l劉備')之間的新記錄，從而防止幻讀產生。所以這個過程的加鎖示意圖如下：
這裡大家要注意一下，設計
InnoDB的大叔在這裡給name值為'l劉備'的二級索引記錄加的是S型next-key鎖，而不是簡單的gap鎖。使用
SELECT ... FOR UPDATE語句來為記錄加鎖：和
SELECT ... LOCK IN SHARE MODE語句類似，只不過加的是X型正經記錄鎖。使用
UPDATE ...來為記錄加鎖，比方說：UPDATE hero SET country = '漢' WHERE name >= 'c曹操';假設該語句執行時使用了
uk_name二級索引來進行鎖定讀（如果二級索引掃描的記錄太多，也可能因為成本過大直接使用全表掃描的方式進行鎖定讀），而這條UPDATE語句並沒有更新二級索引列，那麼它的加鎖方式和上邊所說的SELECT ... FOR UPDATE語句一致。如果有其他二級索引列也被更新，那麼也會為這些二級索引記錄進行加鎖，就不贅述了。不過還需要強調一種情況，比方說：UPDATE hero SET country = '漢' WHERE name <= 'c曹操';我們前邊說的
索引條件下推這個特性只適用於SELECT語句，也就是說UPDATE語句中無法使用，無法使用索引條件下推這個特性時需要先進行回表操作，那麼這個語句就會為name值為'c曹操'和'l劉備'的二級索引記錄加X型next-key鎖，對它們對應的聚簇索引記錄進行加X型正經記錄鎖。不過之後在判斷邊界條件時，雖然name值為'l劉備'的二級索引記錄不符合name <= 'c曹操'的邊界條件，但是在REPEATABLE READ隔離級別下並不會釋放該記錄上加的鎖，整個過程的加鎖示意圖就是：
使用
DELETE ...來為記錄加鎖，比方說：DELETE FROM hero WHERE name >= 'c曹操';和
DELETE FROM hero WHERE name <= 'c曹操';如果這兩個語句採用二級索引來進行
鎖定讀，那麼它們的加鎖情況和更新帶有二級索引列的UPDATE語句一致，就不畫圖了。
對於使用普通二級索引進行等值查詢的情況
我們再把上邊的唯一二級索引uk_name改回普通二級索引idx_name：
ALTER TABLE hero DROP INDEX uk_name, ADD INDEX idx_name (name);使用
SELECT ... LOCK IN SHARE MODE語句來為記錄加鎖，比方說：SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;由於普通的二級索引沒有唯一性，所以一個事務在執行上述語句之後，要阻止別的事務插入
name值為'c曹操'的新記錄，設計InnoDB的大叔採用下邊的方式對上述語句進行加鎖：對所有
name值為'c曹操'的二級索引記錄加S型next-key鎖，它們對應的聚簇索引記錄加S型正經就鎖。對最後一個
name值為'c曹操'的二級索引記錄的下一條二級索引記錄加gap鎖。
所以整個加鎖示意圖就如下所示：
如果對普通二級索引等值查詢的值並不存在，比如：
SELECT * FROM hero WHERE name = 'g關羽' LOCK IN SHARE MODE;加鎖方式和我們上邊嘮叨過的唯一二級索引的情況是一樣的，就不贅述了。
使用
SELECT ... FOR UPDATE語句來為記錄加鎖，比如：和
SELECT ... LOCK IN SHARE MODE語句類似，只不過加的是X型正經記錄鎖。使用
UPDATE ...來為記錄加鎖，比方說：與
SELECT ... FOR UPDATE的加鎖情況類似，不過如果被更新的列中還有別的二級索引列的話，這些對應的二級索引記錄也會被加鎖。使用
DELETE ...來為記錄加鎖，比方說：與
SELECT ... FOR UPDATE的加鎖情況類似，不過如果表中還有別的二級索引列的話，這些對應的二級索引記錄也會被加鎖。
對於使用普通二級索引進行範圍查詢的情況
與唯一二級索引的加鎖情況類似，就不多嘮叨了哈～
全表掃描的情況
比方說：
SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;由於country列上未建索引，所以只能採用全表掃描的方式來執行這條查詢語句，儲存引擎每讀取一條聚簇索引記錄，就會為這條記錄加鎖一個S型next-key鎖，然後返回給server層，如果server層判斷country = '魏'這個條件是否成立，如果成立則將其傳送給客戶端，否則會向InnoDB儲存引擎傳送釋放掉該記錄上的鎖的訊息，不過在REPEATABLE READ隔離級別下，InnoDB儲存引擎並不會真正的釋放掉鎖，所以聚簇索引的全部記錄都會被加鎖，並且在事務提交前不釋放。畫個圖就像這樣：
大家看到了麼：全部記錄都被加了next-key鎖！此時別的事務別說想向表中插入啥新記錄了，就是對某條記錄加X鎖都不可以，這種情況下會極大影響訪問該表的併發事務處理能力，所以如果可能的話，儘可能為表建立合適的索引吧～
使用SELECT ... FOR UPDATE進行加鎖的情況與上邊類似，只不過加的是X型正經記錄鎖，就不贅述了。
對於UPDATE ...語句來說，加鎖情況與SELECT ... FOR UPDATE類似，不過如果被更新的列中還有別的二級索引列的話，這些對應的二級索引記錄也會被加X型正經記錄鎖。
和DELETE ...的語句來說，加鎖情況與SELECT ... FOR UPDATE類似，不過如果表中還有別的二級索引列的話，這些對應的二級索引記錄也會被加X型正經記錄鎖。
文章系轉載
文章來源：我們都是小青蛙（微信公眾號）
本作品採用《CC 協議》，轉載必須註明作者和本文連結