解決MySQL可重復讀——詳解間隙鎖
間隙鎖(Gap Lock)是Innodb在可重復讀提交下為了解決幻讀問題時引入的鎖機制，(下面的所有案例沒有特意強調(diào)都使用可重復讀隔離級別)幻讀的問題存在是因為新增或者更新操作，這時如果進行范圍查詢的時候(加鎖查詢)，會出現(xiàn)不一致的問題，這時使用不同的行鎖已經(jīng)沒有辦法滿足要求，需要對一定范圍內(nèi)的數(shù)據(jù)進行加鎖，間隙鎖就是解決這類問題的。在可重復讀隔離級別下，數(shù)據(jù)庫是通過行鎖和間隙鎖共同組成的(next-key lock)，來實現(xiàn)的。
加鎖規(guī)則有以下特性，我們會在后面的案例中逐一解釋：
- 1.加鎖的基本單位是(next-key lock),他是前開后閉原則
- 2.插敘過程中訪問的對象會增加鎖
- 3.索引上的等值查詢--給唯一索引加鎖的時候，next-key lock升級為行鎖
- 4.索引上的等值查詢--向右遍歷時最后一個值不滿足查詢需求時，next-key lock 退化為間隙鎖
- 5.唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止
案例數(shù)據(jù)
以上數(shù)據(jù)為了解決幻讀問題，更新的時候不只是對上述的五條數(shù)據(jù)增加行鎖，還對于中間的取值范圍增加了6間隙鎖，(-∞，5](5，10](10，15](15，20](20，25](25，+supernum] (其中supernum是數(shù)據(jù)庫維護的最大的值。為了保證間隙鎖都是左開右閉原則。)
案例一：間隙鎖簡單案例
當有如下事務A和事務B時，事務A會對數(shù)據(jù)庫表增加(10，15]這個區(qū)間鎖，這時insert id = 12 的數(shù)據(jù)的時候就會因為區(qū)間鎖(10，15]而被鎖住無法執(zhí)行。
案例二： 間隙鎖死鎖問題
不同于寫鎖相互之間是互斥的原則，間隙鎖之間不是互斥的，如果一個事務A獲取到了(5,10]之間的間隙鎖，另一個事務B也可以獲取到(5,10]之間的間隙鎖。這時就可能會發(fā)生死鎖問題，如下案例。
事務A獲取到(5,10]之間的間隙鎖不允許其他的DDL操作，在事務提交，間隙鎖釋放之前，事務B也獲取到了間隙鎖(5,10]，這時兩個事務就處于死鎖狀態(tài)
案例三： 等值查詢—唯一索引
1.加鎖的范圍是(5,10]的范圍鎖
2.由于數(shù)據(jù)是等值查詢，并且表中最后數(shù)據(jù)id = 10 不滿足id= 7的查詢要求，故id=10 的行級鎖退化為間隙鎖，(5,10)
3.所以事務B中id=8會被鎖住，而id=10的時候不會被鎖住
案例四： 等值查詢—普通索引
1.加鎖的范圍是(0,5]，(5,10]的范圍鎖
2.由于c是普通索引，根據(jù)原則4，搜索到5后繼續(xù)向后遍歷直到搜索到10才放棄，故加鎖范圍為(5,10]
3.由于查詢是等值查詢，并且最后一個值不滿足查詢要求，故間隙鎖退化為(5,10)
4.因為加鎖是對普通索引c加鎖，而且因為索引覆蓋，沒有對主鍵進行加鎖，所以事務B執(zhí)行正常
5.因為加鎖范圍(5,10)故事務C執(zhí)行阻塞
6.需要注意的是，lock in share mode 因為覆蓋索引故沒有鎖主鍵索引，如果使用for update 程序會覺得之后會執(zhí)行更新操作故會將主鍵索引一同鎖住
案例五： 范圍查詢—唯一索引
next-key lock 增加范圍鎖(5,10]
根據(jù)原則5，唯一索引的范圍查詢會到第一個不符合的值位置，故增加(10，15]
因為等值查詢有id =10 根據(jù)原則3間隙鎖升級為行鎖，故剩余鎖[10,15]
因為查詢并不是等值查詢，故[10,15]不會退化成[10,15)
故事務B(13,13,13)阻塞，事務C阻塞
案例六： 范圍查詢—普通索引
next-key lock 增加范圍鎖(5,10]，(10，15]
因為c是非唯一索引，故(5,10]不會退化為10
因為查詢并不是等值查詢，故[10,15]不會退化成[10,15)
所以事務B和事務C全部堵塞
案例七： 普通索引-等值問題
上面的數(shù)據(jù)增加一行(30,10,30)，這樣在數(shù)據(jù)庫中存在的c=10的就有兩條記錄
next-key lock 增加范圍鎖(5,10]，(10，15]
因為是等值查詢故退化為(5,10]，(10，15)，故事務B阻塞，事務C執(zhí)行成功
加鎖的范圍如下圖
案例八： 普通索引-等值Limit問題
根據(jù)上面案例8改造，將delete增加limit操作2的操作
因為知道了數(shù)據(jù)加鎖值加2條，故在加鎖(5，10]之后發(fā)現(xiàn)已經(jīng)有兩條數(shù)據(jù)，故后面不在向后匹配加鎖。所以事務B執(zhí)行成功，加鎖范圍如下
作者：小亮__
鏈接：https://www.jianshu.com/p/32904ee07e56
來源：簡書
