1.加鎖規(guī)則
原則 1：加鎖的基本單位是 next-key lock。希望你還記得，next-key lock 是前開后閉區(qū)間。原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖。優(yōu)化 1：索引上（唯一索引）的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖。（也不會(huì)向右遍歷了,因此不會(huì)增加右側(cè)的間隙鎖）（必須是記錄匹配的情況下）優(yōu)化 2：索引上（唯一與非唯一索引）的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖。（記錄匹配或不匹配都可，匹配上了，如果是唯一索引，就加行鎖，如果是非唯一索引，就加next-key lock；匹配不上，無論是唯一索引還是非唯一索引，都加間隙鎖）一個(gè) bug：唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止（針對(duì)范圍鎖，無論是唯一索引還是非唯一索引，都要訪問到不滿足條件的第一個(gè)值為止）。
已知數(shù)據(jù)庫中執(zhí)行insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25)
案例一（主鍵索引等值查詢，加鎖的情況）
由于表 t 中沒有 id=7 的記錄，所以用我們上面提到的加鎖規(guī)則判斷一下的話：
根據(jù)原則 1，加鎖單位是 next-key lock，session A 加鎖范圍就是 (5,10]；（這里為啥是(5,10]其實(shí)沒有說出規(guī)則，只能猜測(cè)只有當(dāng)前這個(gè)next-key lock能鎖住id=7）同時(shí)根據(jù)優(yōu)化 2，這是一個(gè)等值查詢 (id=7)，由于不存在id=7，只能向右遍歷，而 id=10又不滿足查詢條件，next-key lock 退化成間隙鎖，因此最終加鎖的范圍是 (5,10)。
案例二（非唯一索引等值查詢，加鎖情況）
根據(jù)原則 1，加鎖單位是 next-key lock，因此會(huì)給 (0,5]加上 next-key lock。要注意 c 是普通索引，因此僅訪問 c=5 這一條記錄是不能馬上停下來的，需要向右遍歷，查到 c=10 才放棄。根據(jù)原則 2，訪問到的都要加鎖，因此要給 (5,10]加 next-key lock。但是同時(shí)這個(gè)符合優(yōu)化 2：等值判斷，向右遍歷，最后一個(gè)值不滿足 c=5 這個(gè)等值條件，因此退化成間隙鎖 (5,10)。根據(jù)原則 2 ，只有訪問到的對(duì)象才會(huì)加鎖，這個(gè)查詢使用覆蓋索引，并不需要訪問主鍵索引，所以主鍵索引上沒有加任何鎖，這就是為什么 session B 的 update 語句可以執(zhí)行完成。但 session C 要插入一個(gè) (7,7,7) 的記錄，就會(huì)被 session A 的間隙鎖 (5,10) 鎖住。需要注意，在這個(gè)例子中，lock in share mode 只鎖覆蓋索引，但是如果是 for update 就不一樣了。 執(zhí)行 for update 時(shí)，系統(tǒng)會(huì)認(rèn)為你接下來要更新數(shù)據(jù)，因此會(huì)順便給主鍵索引上滿足條件的行加上行鎖。這個(gè)例子說明，鎖是加在索引上的；同時(shí)，它給我們的指導(dǎo)是，如果你要用 lock in share mode 來給行加讀鎖避免數(shù)據(jù)被更新的話，就必須得繞過覆蓋索引的優(yōu)化，在查詢字段中加入索引中不存在的字段。比如，將 session A 的查詢語句改成 select d from t where c=5 lock in share mode。
案例三（主鍵（唯一）索引范圍鎖）
現(xiàn)在我們就用前面提到的加鎖規(guī)則，來分析一下 session A 會(huì)加什么鎖呢？開始執(zhí)行的時(shí)候，要找到第一個(gè) id=10 的行，因此本該是 next-key lock(5,10]。 根據(jù)優(yōu)化 1， 主鍵 id 上的等值條件，退化成行鎖（屬于唯一索引），只加了 id=10 這一行的行鎖。范圍查找就往后繼續(xù)找，找到 id=15 這一行停下來，因此需要加 next-key lock(10,15]。所以，session A 這時(shí)候鎖的范圍就是主鍵索引上，行鎖 id=10 和 next-key lock(10,15]。這樣，session B 和 session C 的結(jié)果你就能理解了。這里你需要注意一點(diǎn)，首次 session A 定位查找 id=10 的行的時(shí)候，是當(dāng)做等值查詢來判斷的，而向右掃描到 id=15 的時(shí)候，用的是范圍查詢判斷。
案例四（非唯一索引范圍鎖）
這次 session A 用字段 c 來判斷，加鎖規(guī)則跟案例三唯一的不同是：在第一次用 c=10 定位記錄的時(shí)候，索引 c 上加了 (5,10]這個(gè) next-key lock 后，由于索引 c 是非唯一索引，沒有優(yōu)化規(guī)則，也就是說不會(huì)蛻變?yōu)樾墟i，因此最終 sesion A 加的鎖是，索引 c 上的 (5,10] 和 (10,15] 這兩個(gè) next-key lock。所以從結(jié)果上來看，sesson B 要插入（8,8,8) 的這個(gè) insert 語句時(shí)就被堵住了。這里需要掃描到 c=15 才停止掃描，是合理的，因?yàn)?InnoDB 要掃到 c=15，才知道不需要繼續(xù)往后找了。
案例五（唯一索引范圍鎖 bug）
session A 是一個(gè)范圍查詢，按照原則 1 的話，應(yīng)該是索引 id 上只加 (10,15]這個(gè) next-key lock，并且因?yàn)?id 是唯一鍵，所以循環(huán)判斷到 id=15 這一行就應(yīng)該停止了。但是實(shí)現(xiàn)上，InnoDB 會(huì)往前掃描到第一個(gè)不滿足條件的行為止，也就是 id=20。而且由于這是個(gè)范圍掃描，因此索引 id 上的 (15,20]這個(gè) next-key lock 也會(huì)被鎖上。所以你看到了，session B 要更新 id=20 這一行，是會(huì)被鎖住的。同樣地，session C 要插入 id=16 的一行，也會(huì)被鎖住。照理說，這里鎖住 id=20 這一行的行為，其實(shí)是沒有必要的。因?yàn)閽呙璧?id=15，就可以確定不用往后再找了。但實(shí)現(xiàn)上還是這么做了，因此這是個(gè) bug。這里為什么沒有加(5,10]的next-key lock，因?yàn)?strong>"對(duì)訪問到的對(duì)象加鎖"，id>10 and id<=15訪問到的對(duì)象是15,20，因此加的鎖是(10,15],(15,20]; 不會(huì)訪問到id=10這一條記錄的，因此沒有(5,10]這個(gè)鎖。
案例六（非唯一索引上存在"等值"）
新插入的這一行 c=10、id=30，也就是說現(xiàn)在表里有兩個(gè) c=10 的行，如下圖。那么，這時(shí)候索引 c 上的間隙是什么狀態(tài)了呢？你要知道，由于非唯一索引上包含主鍵的值，所以是不可能存在“相同”的兩行的。
MySQL> insert into t values(30,10,30);
可以看到，雖然有兩個(gè) c=10，但是它們的主鍵值 id 是不同的（分別是 10 和 30），因此這兩個(gè) c=10 的記錄之間，也是有間隙的。圖中我畫出了索引 c 上的主鍵 id。為了跟間隙鎖的開區(qū)間形式進(jìn)行區(qū)別，我用 (c=10,id=30) 這樣的形式，來表示索引上的一行。現(xiàn)在，我們來看一下案例六。這次我們用 delete 語句來驗(yàn)證。注意，delete 語句加鎖的邏輯，其實(shí)跟 select ... for update 是類似的（要update和delete的時(shí)候，要“先讀后寫”，這個(gè)讀就開始加鎖了。insert的時(shí)候要有插入意向鎖（就是會(huì)跟gap lock沖突的那個(gè)），因此執(zhí)行 “delete from t where c=10;” 語句，索引c上的next-key lock是(5,10],(10,15)。那么主鍵索引上的鎖只有行鎖，鎖住的是 (10,10,10) 和 (30,10,30) 兩行，因此先“讀”，一旦讀就會(huì)回表掃描到主鍵索引），也符合上邊的原則，也就是我在文章開始總結(jié)的兩個(gè)“原則”、兩個(gè)“優(yōu)化”和一個(gè)“bug”。
這時(shí)，session A 在遍歷的時(shí)候，先訪問第一個(gè) c=10 的記錄。同樣地，根據(jù)原則 1，這里加的是 (c=5,id=5) 到 (c=10,id=10) 這個(gè) next-key lock（也就是(5,10]）。然后，session A 向右查找，直到碰到 (c=15,id=15) 這一行（也就是(10,15]），循環(huán)才結(jié)束。根據(jù)優(yōu)化 2，這是一個(gè)等值查詢，向右查找到了不滿足條件的行，所以會(huì)退化成 (c=10,id=10) 到 (c=15,id=15) 的間隙鎖（也就是(10,15)）。也就是說，這個(gè) delete 語句在索引 c 上的加鎖范圍，就是下圖中藍(lán)色區(qū)域覆蓋的部分。
案例七（limit 語句加鎖）
這個(gè)例子里，session A 的 delete 語句加了 limit 2。你知道表 t 里 c=10 的記錄其實(shí)只有兩條，因此加不加 limit 2，刪除的效果都是一樣的，但是加鎖的效果卻不同。可以看到，session B 的 insert 語句執(zhí)行通過了，跟案例六的結(jié)果不同。這是因?yàn)椋咐呃锏?delete 語句明確加了 limit 2 的限制，因此在遍歷到 (c=10, id=30) 這一行之后，滿足條件的語句已經(jīng)有兩條，循環(huán)就結(jié)束了。因此，索引 c 上的加鎖范圍就變成了從（c=5,id=5) 到（c=10,id=30) 這個(gè)前開后閉區(qū)間，如下圖所示：
可以看到，(c=10,id=30）之后的這個(gè)間隙并沒有在加鎖范圍里，因此 insert 語句插入 c=12 是可以執(zhí)行成功的。這個(gè)例子對(duì)我們實(shí)踐的指導(dǎo)意義就是，在刪除數(shù)據(jù)的時(shí)候盡量加 limit。這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全，還可以減小加鎖的范圍。
案例八（一個(gè)死鎖的例子）
session A 啟動(dòng)事務(wù)后執(zhí)行查詢語句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和間隙鎖 (10,15)；session B 的 update 語句也要在索引 c 上加 next-key lock(5,10] ，進(jìn)入鎖等待；然后 session A 要再插入 (8,8,8) 這一行，被 session B 的間隙鎖鎖住。由于出現(xiàn)了死鎖，InnoDB 讓 session B 回滾。你可能會(huì)問，session B 的 next-key lock 不是還沒申請(qǐng)成功嗎？其實(shí)是這樣的，session B 的“加 next-key lock(5,10] ”操作，實(shí)際上分成了兩步，先是加 (5,10) 的間隙鎖，加鎖成功；然后加 c=10 的行鎖，這時(shí)候才被鎖住的。（這也就是為什么sessionB無法加(10,15)間隙鎖的原因，因?yàn)榇藭r(shí)它已經(jīng)被阻塞，無法繼續(xù)掃描了）也就是說，我們?cè)诜治黾渔i規(guī)則的時(shí)候可以用 next-key lock 來分析。但是要知道，具體執(zhí)行的時(shí)候，是要分成間隙鎖和行鎖兩段來執(zhí)行的。
當(dāng)對(duì)索引加鎖時(shí)，如果這個(gè)索引所對(duì)應(yīng)的行記錄存在，則加行鎖+間隙鎖，如果這個(gè)記錄不存在，則對(duì)這個(gè)索引所在的記錄間隙加間隙鎖，如下。
update t set d= 10 where c = 7
如果7不存在，則加(5,10)，如果7存在，則加(5,7] 。
