MySQL加鎖原則

兩個(gè)原則

1. 原則 1：加鎖的基本單位是 next-key lock。next-key lock 是前開后閉區(qū)間（區(qū)間鎖和行鎖）。
2. 原則 2：查找過程中訪問到的對(duì)象（索引）才會(huì)加鎖。

兩個(gè)優(yōu)化

1.優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖（不會(huì)加行鎖）。

2.優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖（條件已經(jīng)不滿足，不需要加行鎖）。

一個(gè)BUG （8.0之前）

唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止。

一.等值查詢間隙鎖

初始化表與數(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);

由于表 t 中沒有 id=7 的記錄，所以用我們上面提到的加鎖規(guī)則判斷一下的話：根據(jù)原則 1，加鎖單位是 next-key lock，session A 加鎖范圍就是 (5,10]；同時(shí)根據(jù)優(yōu)化 2，這是一個(gè)等值查詢 (id=7)，而 id=10 不滿足查詢條件，next-key lock 退化成間隙鎖，因此最終加鎖的范圍是 (5,10)。所以，session B 要往這個(gè)間隙里面插入 id=8 的記錄會(huì)被鎖住，但是 session C 修改 id=10 這行是可以的。

二 .非唯一索引等值鎖

首先c是有索引的，根據(jù)原則 1，加鎖單位是 next-key lock，由于 c 不是唯一索引，需要鎖住 （0,5]這個(gè)區(qū)間，且僅訪問 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í)行完成。

三.主鍵索引范圍鎖

對(duì)于session A 來說，首先需要找到id=10這一列，根據(jù)原則一加區(qū)間鎖與行鎖（5，10]，由于 id 是主鍵索引，因此會(huì)退化成行鎖 只鎖住 id=10 這一行數(shù)據(jù)。然后往后遍歷直到找到第一個(gè)不滿足 > 10 且 < 11的數(shù)，也就是15.由于是范圍查詢并不是等值查詢，不滿足優(yōu)化一，二。因此此時(shí)是鎖區(qū)間（10,15）和 id=15這一行數(shù)據(jù)。最終鎖的范圍是 [10,15]。

四.非唯一索引范圍鎖

c 是索引，但不是唯一索引，首先等值查詢 c=10，然后再向后找到不滿足等值的第一個(gè)值,原則一，優(yōu)化二 鎖的范圍為 （5,15],優(yōu)化二 退化為 區(qū)間鎖 （5,15）。然后范圍查詢 根據(jù)原則一 加鎖為 （10,15]。