catalog
- 加鎖規則
- 等值查詢間隙鎖
- 非唯一索引等值鎖
- 主鍵索引范圍鎖
- 非唯一索引范圍鎖
- 唯一索引范圍鎖 bug
- 非唯一索引上存在"等值"的例子
- limit語句加鎖
- 關于死鎖
總結
1、查詢過程中訪問到的對象才會加鎖，而加鎖的基本單位是next-key lock（前開后閉）；
2、等值查詢上MySQL的優化：索引上的等值查詢，如果是唯一索引，next-key lock會退化為行鎖，如果不是唯一索引，需要訪問到第一個不滿足條件的值，此時next-key lock會退化為間隙鎖；
3、范圍查詢：無論是否是唯一索引，范圍查詢都需要訪問到不滿足條件的第一個值為止；
加鎖規則
默認這里的隔離級別是可重復讀。
原則1：加鎖的基本單位是next-key lock。該鎖的區間是前開后閉
原則2：查找過程中訪問到的對象才會加鎖
優化1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖
優化2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock退化為間隙鎖
bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
建表語句：
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);
等值查詢間隙鎖
非唯一索引等值鎖
session A給索引c上c=5這一行加上讀鎖。
根據原則1，加鎖單位是next-key lock ，因此會給(0,5]加上next-key lock.
c是普通索引，因此訪問c=5后還需要向右遍歷，查到c=10才放棄。
根據原則2，訪問到的都要加鎖，因此要給(5,10]加上next-key lock;
同時符合優化2：等值判斷，向右遍歷，最后一個值不滿足c=5，于是退化為間隙鎖(5,10);
(前面分析的(0, 5]間隙鎖還是存在的，合起來存在(0, 5]和(5, 10)兩個間隙鎖 )
根據原則2，只有訪問到的對象才會加鎖，這個查詢使用的是覆蓋索引，并不需要訪問主鍵索引，所以主鍵索引上沒有加任何鎖，所以session B的update語句可以執行完成。
**訪問到的對象才會加鎖，這個“對象”指的是列，不是 記錄行。 補充一下： 加鎖，是加在索引上的。 列上，有索引，就加在索引上； 列上，沒有索引，就加在主鍵上； **
session C要插入(7,7,7)記錄，會被session A 的間隙鎖(5,10)鎖住。
lock in share mode 只鎖覆蓋索引，for update 會順便給主鍵索引上滿足條件的行加上行鎖。
總結：
鎖是加在索引上的；
用lock in share mode 來給行加讀鎖避免數據被更新的話，就必須繞過覆蓋索引的優化，在查詢字段中加入索引中不存在的字段。
如將session A的語句：
select id from t where c=5 lock in share mode;
修改為
select d from t where c = 5 lock in share mode;
數據行加讀鎖，如果查詢字段使用了覆蓋索引，訪問到的對象只有普通索引，并沒有訪問到主鍵索引，則不會鎖主鍵索引。如果沒有使用覆蓋索引，且當前查詢是for update ,update 和 delete 都是當前讀，則會回表查詢，訪問到主鍵索引，這樣主鍵索引也會加鎖。
主鍵索引范圍鎖
對于表t，有兩個查詢語句,這兩個語句加鎖范圍不同：
select * from t where id = 10 for update;
select * from t where id >= 10 and id < 11 for update;
這兩句話邏輯上等價，但是加鎖規則不一樣。
執行流程：
1、找到第一個id=10的行，因此本該是next-key lock (5,10]。根據優化1，主鍵id上的等值條件，退化成行鎖，只加id = 10這一行的行鎖
2、范圍查找繼續往后找，找到id=15這一行停下來，因此需要加next-key lock (10,15].
所以，session A這時候鎖的范圍就是主鍵索引上，行鎖id = 10和next-key lock(10,15]。 因為是范圍查詢，不是等值查詢，所以不會進行優化2； 所以會出現B C的block情況。
需要注意 首次session A 定位查找id=10的行時候，是當作等值查詢來判斷的，而向右掃描到id=15的時候，用的是范圍查詢判斷。
--引用：
1. 先走主鍵id索引, 拿出id=10的那一行, (注意這里是等值查詢) 2. 再從id=10的那一行開始, 不斷地往右遍歷拿出每一行, 直到它的 id 不滿足 大于等于10, 小于11 這個條件后, 再停止 (注意這里就是范圍查詢) 根據一開始的Creae table/ insert values等語句(10后面就是15), 還有再根據加鎖規則(原則1, 原則2, 優化1, 優化2, bug5): 執行步驟1時, 因為是等值查詢, 主鍵索引又是唯一索引, 根據原則1, 原則2, 優化1, 最終只加行鎖10; 執行步驟2時, 因為是范圍查詢, 主鍵索引又是唯一索引, 根據原則1, 原則2, Bug5, 而不滿足條件的第一個值就是15, 所以最終要加鎖(10, 15]; 這一塊相對還是比較繁瑣的
非唯一索引范圍鎖
唯一索引范圍鎖 bug
非唯一索引上存在"等值"的例子
給表t插入一條新紀錄
insert into t values(30,10,30);
此時表里面就有了兩個c=10的行。 因為主鍵是唯一的， 所以不存在完全相同的兩行 ，此時的索引c為：
兩個c=10，但是主鍵id不同(分別為10和30)，因此這兩個c=10的記錄之間也是有間隙的。
這里使用delete語句來驗證。delete原則和之前update原則一樣。
session A遍歷的時候，先訪問第一個c=10的記錄。根據原則1：這里加的是
(c = 5,id = 5) 到(c = 10,id = 10)這個next-key lock.
然后，session A向右查找，直到碰到(c=15,id=15)這一行，循環才結束。根據優化2，這是一個等值查詢，向右查找到了不滿足條件的行，所以會退化成(c=10,id=10)到(c=15,id=15)的間隙鎖。
也就是說，這個delete語句在索引c上的加鎖范圍，如下：
注意(c=5,id=5)和(c=15,id=15)這兩行都沒有鎖。
limit語句加鎖
關于死鎖
next-key lock實際上是間隙鎖和行鎖加起來的結果。
分析流程：
1、session A啟動事務執行查詢語句加lock in share mode，在索引c上加了next-key lock(5,10]和間隙鎖(10,15);
2、session B的update語句在索引c上加next-key lock(5,10]，進入鎖等待；
3、然后session A要再插入(8,8,8)這一行，被session B的間隙鎖鎖住。由于出現了死鎖，InnoDB讓session B回滾。
我們認為session B的加鎖還沒申請成功。
但是，其實session B的"加next-key lock(5,10]"操作實際上分成了兩步，先是加(5,10)的間隙鎖，加鎖成功；然后加c=10的行鎖，第二步才被鎖住。
也就是說我們分析加鎖的具體步驟時，需要分成間隙鎖和行鎖兩段來執行。
