表和數(shù)據(jù)
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);
規(guī)則總結（這個規(guī)則只限于截止到現(xiàn)在的最新版本骂维，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13哮兰。）：
原則 1：加鎖的基本單位是 next-key lock毛萌。希望你還記得，next-key lock 是前開后閉區(qū)間喝滞。
原則 2：查找過程中訪問到的對象才會加鎖阁将。
優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時候右遭，next-key lock 退化為行鎖做盅。
優(yōu)化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候窘哈，next-key lock 退化為間隙鎖吹榴。
一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
案例一：等值查詢間隙鎖
- 根據(jù)原則 1滚婉，加鎖單位是 next-key lock图筹，session A 加鎖范圍就是 (5,10]；
- 同時根據(jù)優(yōu)化 2让腹，這是一個等值查詢 (id=7)远剩，而 id=10 不滿足查詢條件，next-key lock 退化成間隙鎖骇窍，因此最終加鎖的范圍是 (5,10)瓜晤。
案例二：非唯一索引等值鎖
根據(jù)原則1，優(yōu)化2腹纳，鎖的范圍是(0,5],(5,10)活鹰，但是根據(jù)原則2，只有訪問到的對象才加鎖只估，這個查詢使用了覆蓋索引，并不訪問主鍵索引着绷，所以主鍵上沒加鎖蛔钙。
需要注意，在這個例子中荠医，lock in share mode 只鎖覆蓋索引吁脱，但是如果是 for update 就不一樣了桑涎。 執(zhí)行 for update 時，系統(tǒng)會認為你接下來要更新數(shù)據(jù)兼贡，因此會順便給主鍵索引上滿足條件的行加上行鎖攻冷。
案例三：主鍵索引范圍鎖
- 開始執(zhí)行的時候，要找到第一個 id=10 的行遍希，因此本該是 next-key lock(5,10]等曼。 根據(jù)優(yōu)化 1， 主鍵 id 上的等值條件凿蒜，退化成行鎖禁谦，只加了 id=10 這一行的行鎖。
- 范圍查找就往后繼續(xù)找废封，找到 id=15 這一行停下來州泊，因此需要加 next-key lock(10,15]。
首次 session A 定位查找 id=10 的行的時候漂洋，是當做等值查詢來判斷的遥皂，而向右掃描到 id=15 的時候，用的是范圍查詢判斷
案例四：非唯一索引范圍鎖
這次 session A 用字段 c 來判斷刽漂，加鎖規(guī)則跟案例三唯一的不同是：在第一次用 c=10 定位記錄的時候演训，索引 c 上加了 (5,10] 這個 next-key lock 后，由于索引 c 是非唯一索引爽冕，沒有優(yōu)化規(guī)則仇祭，也就是說不會蛻變?yōu)樾墟i，因此最終 sesion A 加的鎖是颈畸，索引 c 上的 (5,10] 和 (10,15] 這兩個 next-key lock乌奇。
案例五：唯一索引范圍鎖 bug
- session A 是一個范圍查詢，按照原則 1 的話眯娱，應該是索引 id 上只加 (10,15] 這個 next-key lock礁苗，并且因為 id 是唯一鍵，所以循環(huán)判斷到 id=15 這一行就應該停止了徙缴。
- 但是實現(xiàn)上试伙，InnoDB 會往前掃描到第一個不滿足條件的行為止，也就是 id=20于样。而且由于這是個范圍掃描疏叨，因此索引 id 上的 (15,20] 這個 next-key lock 也會被鎖上。改成select * from t where id>10 and id<15 for update;則只會加new-key lock(10,15]
案例六：非唯一索引上存在"等值"的例子
表中插入一條mysql> insert into t values(30,10,30);
這時穿剖，session A 在遍歷的時候蚤蔓，先訪問第一個 c=10 的記錄。同樣地糊余，根據(jù)原則 1秀又，這里加的是 (c=5,id=5) 到 (c=10,id=10) 這個 next-key lock单寂。
然后，session A 向右查找吐辙，直到碰到 (c=15,id=15) 這一行宣决，循環(huán)才結束。根據(jù)優(yōu)化 2昏苏，這是一個等值查詢尊沸，向右查找到了不滿足條件的行，所以會退化成 (c=10,id=10) 到 (c=15,id=15) 的間隙鎖捷雕。
案例七：limit 語句加鎖
索引 c 上的加鎖范圍變成了從（c=5,id=5) 到（c=10,id=30) 這個前開后閉區(qū)間椒丧，如下圖所示：
案例八：一個死鎖的例子
- session A 啟動事務后執(zhí)行查詢語句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和間隙鎖 (10,15)救巷；
- session B 的 update 語句也要在索引 c 上加 next-key lock(5,10] 壶熏，進入鎖等待；
- 然后 session A 要再插入 (8,8,8) 這一行浦译，被 session B 的間隙鎖鎖住棒假。由于出現(xiàn)了死鎖，InnoDB 讓 session B 回滾精盅。
session B的行鎖c=10和session A有沖突帽哑，所以死鎖
在讀提交情況下，語句執(zhí)行過程中加上的行鎖叹俏，在語句執(zhí)行完成后妻枕，就要把“不滿足條件的行”上的行鎖直接釋放了，不需要等到事務提交粘驰。
費解的問題
1.由于是 order by c desc屡谐，第一個要定位的是索引 c 上“最右邊的”c=20 的行，所以會加上間隙鎖 (20,25) 和 next-key lock (15,20]蝌数。
2.在索引 c 上向左遍歷愕掏，要掃描到 c=10 才停下來，所以 next-key lock 會加到 (5,10]顶伞，這正是阻塞 session B 的 insert 語句的原因饵撑。
3.在掃描過程中，c=20唆貌、c=15滑潘、c=10 這三行都存在值，由于是 select *锨咙，所以會在主鍵 id 上加三個行鎖众羡。
