SQL語句加鎖分析
- 2020 年 4 月 22 日
- 筆記
背景
MySQL中SQL加鎖的情況十分複雜，不同隔離級別、不同索引類型、索引是否命中的SQL加鎖各不相同。
然而在分析死鎖過程當中，熟知各種情況的SQL加鎖是分析死鎖的關鍵，因此需要將MySQL的各種SQL情況加鎖進行分析總結。
基礎知識
MVCC
- 快照讀
- 讀取歷史版本，從undo log中讀取行記錄的快照；這樣讀行就不需要等待鎖資源，提高了並發；
- 當前讀
- 讀取最新版本，並且當前讀返回的記錄，都會加上鎖，保證其他事務不會再並發修改這條記錄。
- 加鎖讀、插入、更新、刪除等操作均屬於當前讀
將插入，更新，刪除歸為當前讀是因為這些操作均包含讀取當前記錄的操作。拿update table set ? where ？來講，
當Update SQL被發給MySQL後，MySQL Server會根據where條件，讀取第一條滿足條件的記錄，然後InnoDB引擎會將第一條記錄返回，並加鎖 (current read)。
待MySQL Server收到這條加鎖的記錄之後，會再發起一個Update請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有滿足條件的記錄為止。
因此，Update操作內部，就包含了一個當前讀。同理，Delete操作也一樣。Insert操作會稍微有些不同，簡單來說，就是Insert操作可能會觸發Unique Key的衝突檢查，也會進行一個當前讀。
注意：
Innodb當前讀加鎖是一條一條進行，先對一條滿足條件的記錄加鎖，返回給MySQL Server做一些DML操作，然後在讀取下一條加鎖，直至讀取完畢。
Two-phase locking
Two-Phase Locking，說的是鎖操作分為兩個階段：加鎖階段與解鎖階段，並且保證加鎖階段與解鎖階段不相交。 加鎖階段：只加鎖，不放鎖。解鎖階段：只放鎖，不加鎖。
隔離級別
資料庫的隔離現象與隔離級別如下圖所示
臟讀現象：即A連接，未提交的事務，B連接的事務可以看到；
NONREPEATABLE READ(不可重複讀)現象：
A連接，提交的事務，B連接的事務中可以看到，這樣在B連接中的事務，就可以看到A連接事務提交前，和提交後兩種狀態；
注意：不可重複讀針對update，delete
PHANTOM READ(幻讀)現象：
A連接，提交的事務，B連接的事務可以看到，這樣在B連接中的事務，就可以看到A連接事務提交前，和提交後兩種狀態；
注意：幻讀針對insert；
innodb事務引擎，通過間隙鎖 粗暴 將RR隔離級別的幻讀現象消除，這也是RR與RC的主要區別。
基礎SQL組合分析
使用下面這張 students 表作為實例，其中 id 為主鍵，no（學號）為二級唯一索引， score（學分）為二級非唯一索引，age（年齡）無索引。
我們只分析基礎SQL，它只包含一個 WHERE 條件，等值查詢或範圍查詢，根據條件類型以及隔離級別不同(主要分析最常用的RR,RC)我們主要分析一下情況：
聚簇索引，索引命中
SQL select * from students where id = 20 for update, 在 RC 和 RR 隔離級別下加鎖情況一樣，都是對 id 這個聚簇索引加 X 鎖，如下：
唯一索引，索引命中
SQL:select * from students where num = 135 for update;
若檢索唯一索引，那麼SQL需要加兩個X鎖，一個對應唯一索引上的num = 135的記錄，另一把鎖對應於聚簇索引上的[id=35]的記錄。
二級索引，索引命中
SQL:select * from students where score= 91 for update;
如此例子當中如法插入score 值有[77,99),注意邊界值。前邊界77是無法插入的，後邊界99則可以插入。
此外 select * from students where score= 77 for update，是不用等待鎖的。
無索引
SQL: select * from students where age = 22 for update;
無索引時如何是RR還是RC均會將行鎖升級為表鎖，具體表現就是全表update，delete。
此外因為RR隔離級別有next-key，RR除了不能update，delete外連insert都不可以，而RC則可以進行insert 操作。
索引未命中
聚簇索引，索引未命中
SQL select * from students where id = 30 for update;
RR隔離級別下，當查找聚簇索引但索引未命中時，此時聚簇索引加鎖狀態與二級索引狀態相同，原本行鎖變為gap鎖，鎖範圍如下：
- （25,35)
當然此時收主鍵唯一性約束，任何插入id=25或35的操作均會失敗，這一點與二級索引不同。
RC隔離級別下，由於id索引未命中即聚簇索引中沒有相關記錄，則不加任何鎖。
唯一索引，索引未命中
SQL select * from students where num = 130 for update;
唯一索引，索引未命中的情況與上面聚簇索引，索引未命中的情況 相似。區別在於聚簇索引gap鎖載入聚簇表中，唯一索引則在唯一索引自身的索引表中。
同樣是沒有行鎖，僅有gap鎖，其表現出來的現象就是在gap範圍內如法插入數據，不影響其餘DML操作。
二級索引，索引未命中
SQL select * from students where score = 70 for update;
範圍查詢
- 聚簇索引範圍查詢
- select * from students where id <=25 for update;
RR隔離級別時，聚簇索引範圍查詢時加鎖情況如下圖。
如果where 條件為id<25 則在25-35間不會加GAP鎖，但也會在25上加X鎖，然後再在相應範圍加GAP鎖。
如果where 條件為id>25,並不會在25處加X鎖，僅會在（25，+）加GAP鎖以及對應索引項加X鎖。
注意：在RR隔離級別時，條件y<id<x, 則MySQL選擇比y,x大的索引項來加X鎖，稱為向右擴展。
- 唯一索引範圍查詢
- select * from students where num >125 for update;
唯一索引範圍查詢整體與聚簇索引範圍查詢相似，RC僅在範圍內的索引列上加X鎖。RR則除在索引列上在X鎖外，還會在範圍內索引列之間加GAP鎖。
此外RR的邊界值是否加X鎖，有向右擴展原則即向索引值大的方向擴展加X鎖。當num<125時，向右擴展的第一個索引值為125 則會在125上加X鎖。
當num<=125時，向右擴展的第一個索引值為135，則會在135上加X鎖。當num>125時，向右擴展的第一個索引值為135，包含在範圍之內因此無特殊表現。
- 二級索引範圍查詢
select * from students where score <= 50 for update;
由下圖可見二級索引範圍查詢其實與唯一索引以及聚簇索引的範圍查詢的加鎖原理相同。RC僅在範圍內的索引項上加X鎖。
RR則除範圍內索引項加X鎖外，並在索引項間加GAP鎖，且邊界值是否加X鎖遵循向右擴展原則。
小結
- 索引等值查詢，且索引命中
- 主鍵、唯一索引無論RR或RC均在索引項及其聚簇索引對應記錄上加X鎖。
- 二級索引RC隔離級別與主鍵、唯一索引相同
- 二級索引RR隔離級別，除對應索引項及其記錄上加X鎖外，在各索引項間加GAP鎖
- 索引等值查詢，且索引未命中
- RR主鍵，與唯一索引會在包含條件值得兩個索引項間 加GAP鎖
- 二級索引與主鍵、唯一索引相似，也會在包含條件值的兩個索引間加GAP鎖並在左側索引項上加X鎖
- RC不加任何鎖
- 索引範圍查詢
- 主鍵索引，唯一索引，二級索引加鎖原理相同。
- RC僅在範圍內的索引項上加X鎖。
- RR則除範圍內索引項加X鎖外，並在索引項間加GAP鎖，且邊界值是否加X鎖遵循向右擴展原則
- 主鍵索引，唯一索引，二級索引加鎖原理相同。
另一個角度總結
- RC
- RC隔離級別沒有GAP鎖(唯一索引insert情況除外，僅指select情況)，僅在符合條件的索引項上加X鎖
- RR
- RR隔離基本多了GAP鎖，但在主鍵或唯一索引存在時僅在索引項及其記錄上加X鎖，不加GAP鎖。
- 二級鎖索引則除索引項及其記錄上加X鎖外，並在包含X鎖記錄的兩側索引項之間加GAP鎖。
- 若索引值未命中
- 主鍵，唯一索引，二級索引均會在包含未命中索引值得兩側索引項之間加GAP鎖。
- 若是二級索引還會在左側索引項上加X鎖。
- RR隔離基本多了GAP鎖，但在主鍵或唯一索引存在時僅在索引項及其記錄上加X鎖，不加GAP鎖。
where 條件提取
給定一條SQL，索引項是如何影響查詢過程的，非索引項的條件是如何過濾數據，只有清楚掌握每個細節才能寫出性能較高的SQL語句。
SQL的where條件大約分為3類
- index key
- index filter
- table filter
Index Key
確定索引掃描的範圍,其包括起始位置與終止位置， 因此Index Key也被拆分為Index First Key和Index Last Key，
分別用於定位索引查找的起始，以及索引查詢的終止條件。
Frist Key
用於確定索引查詢的起始範圍。
提取規則：從索引的第一個鍵值開始，檢查其在where條件中是否存在，若存在並且條件是=、>=，則將對應的條件加入Index First Key之中，繼續讀取索引的下一個鍵值，使用同樣的提取規則；若存在並且條件是>，則將對應的條件加入Index First Key中，同時終止Index First Key 提取；若不存在，同樣終止Index First Key 提取。
例如
idx_c1_c2_c3(c1,c2,c3)
where c1>=1 and c2>2 and c3=1
–> first key (c1,c2)
–> c1為 ‘>=’ ，加入下邊界界定，繼續匹配下一個
–> c2 為 ‘>’， 加入下邊界界定，停止匹配
Last Key
用於確定索引查詢的終止範圍.
提取規則：從索引的第一個鍵值開始，檢查其在where條件中是否存在，若存在並且條件是=、<=，則將對應條件加入到Index Last Key中，繼續提取索引的下一個鍵值，使用同樣的提取規則；若存在並且條件是 < ，則將條件加入到Index Last Key中，同時終止提取；若不存在，同樣終止Index Last Key的提取。
例如
idx_c1_c2_c3(c1,c2,c3)
where c1<=1 and c2=2 and c3<3
–> last key (c1,c2,c3)
–> c1為 ‘<=’，加入上邊界界定，繼續匹配下一個
–> c2為 ‘=’加入上邊界界定，繼續匹配下一個
–> c3 為 ‘<‘，加入上邊界界定，停止匹配
注意：提取過程中 如果比較符號中包含’=’號，’>=’也是包含’=’，那麼該索引鍵是可以被利用的，可以繼續匹配後面的索引鍵值；如果不存在’=’，也就是’>’,'<‘，這兩個，後面的索引鍵值就無法匹配了。
Index Filter
字面理解就是可以用索引去過濾。也就是欄位在索引鍵值中，但是無法用去確定Index Key的部分。
exp:
idex_c1_c2_c3
where c1>=1 and c2<=2 and c3 =1
index key –> c1
index filter–> c2 c3
這裡為什麼index key 只是c1呢？因為c2 是用來確定上邊界的，但是上邊界的c1沒有出現(<=,=)，而下邊界中，c1是>=,c2沒有出現，因此index key 只有c1欄位。c2,c3 都出現在索引中，被當做index filter.
MySQL 是 5.6 之前的版本，Index Filter 和 Table Filter 沒有區別，統統將 Index First Key 與 Index Last Key 範圍內的索引記錄，回表讀取完整記錄，然後返回給 MySQL Server 層進行過濾。而在 MySQL 5.6 之後，Index Filter 與 Table Filter 分離，Index Filter 下降到 InnoDB 的索引層面進行過濾，減少了回表與返回 MySQL Server 層的記錄交互開銷，提高了SQL的執行效率，這就是 ICP（Index Condition Pushdown）。
Table Filter
無法利用索引完成過濾，就只能用table filter。此時引擎層會將行數據返回到server層，然後server層進行table filter。
舉例來說
Index Key ： pubtime
Index Filter: userid
Table Filter: comment
若使用5.6之前的版本則紅色箭頭線所指的記錄會加X鎖，因為5.6之前Index Filter與Table Filter作用一樣，都需要根據Index Key 的掃描範圍回表，到server層再過濾。
若使用5.6及其之後的版本則紅色箭頭線縮指的記錄不會加X鎖，因為ICP(Index Condition Pushdown)特性,在Index Key 掃描完範圍後，根據Index Filter過濾掉不符合要求的然後在回表到server層去過濾Table Filter 即找到comment not null的記錄在該條記錄上加X鎖。
