SQL語句加鎖分析

SQL語句加鎖分析

背景

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

唯一索引範圍查詢

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鎖。

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的提取。

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鎖。

原文位址

https://www.cnblogs.com/Aiapple/p/12751803.html