MySQL的并發處理機制，有MVCC及鎖機制來處理，上篇簡要說明了 MVCC及隔離級别：mysql的并發處理機制_上篇 ，這篇來說說mysql下的鎖。
1 Innodb的鎖
在innodb中，有4種類型的鎖：IX、X、IS及S鎖，其說明如下：
| 類型 | 說明 | 場景 |
| S | 共享鎖 | 針對于RS隔離級别的查詢或者添加Lock in share mode的SELECT查詢而産生的鎖 |
| X | 排它鎖 | 針對于update、delete、insert操作而産生的鎖 |
| IS | 意向共享鎖 | 表級别的鎖，在添加S鎖之前對表格添加IS鎖 |
| IX | 意向排他鎖 | 表級别的鎖，在添加X鎖之前對表格添加IX鎖 |
1.1 鎖定相容情況
四個鎖之間的相容性，需要分成兩種情況來讨論，鎖粒度小于表級别的鎖的相容情況，表級的鎖相容情況。
- 鎖粒度小于表級别的鎖的相容情況
-
mysql的并發處理機制_下篇 - 對于這兩行鎖的相容說明如下：
- 假設有一行資料，添加了行鎖S鎖，那麼這個行資料，可以提供給其他事務進行S鎖的申請和添加，但是不支援其他事務對這一行進行X鎖的申請和添加。比如，事務A，對 pk100 這一行進行了 查詢操作并添加了S鎖，那麼其他事務仍然可以對這一行資料進行查詢，但是不能對這行資料進行 UPDATE 跟 DELETE 操作，會處于鎖等待情況，直到該事務A結束并釋放S鎖；
- 假設有一行資料，添加了X鎖，那麼這個行資料，不允許其他事務對這一行資料進行加鎖。比如，事務A，對pk100這一行進行了UPDATE操作，那麼其他事務在事務A沒有結束之前，都無法對這一行資料申請 S鎖。
-
mysql的并發處理機制_下篇 - 對于表級别的鎖相容性如下：
- 當一個表格持有S表鎖時，不需要其他事務對該表格申請X鎖跟IX鎖，但是允許申請S跟IS鎖。比如，事務A對表格tba全表讀，加了S表鎖，期間支援其他事務對tba全表讀（申請S表鎖成功）、支援其他事務對tba行資料查詢（申請IS表鎖成功），但是不支援對表格全表的修改操作（申請X表鎖等待）跟不支援對表格行資料修改操作（申請IX表鎖等待）；
- 當一個表格持有X表鎖時，持有鎖期間，不支援其他所有鎖的申請；
- 當一個表格持有IS表鎖時，允許申請 S表鎖、IS表鎖、IX表鎖，但是不支援X表鎖申請。
- 比如，事務A對表格tba 查詢了 id = 10（id為主鍵）這一行資料，這個時候，表格tba持有IS表鎖，id = 10 這一行持有 S 行鎖，期間，支援其他事務對 tba 全表查詢（申請表鎖S成功）或者 基于索引查找（申請表鎖IS成功）
- 如果需要對行 id = 20 進行資料修改，則會先申請 tba 的表鎖 IX（申請成功），然後再申請id=20行鎖X （申請成功）；如果需要對 id = 10 這一行資料進行修改，則會申請 tba的表鎖 IX（表鎖申請成功），然後申請 id = 10 的行鎖X（申請堵塞，因為 id = 10 正持有S鎖）；
- 如果需要對表格進行全表修改，需要申請表鎖（X鎖），這個時候，IS鎖的優勢來了，當檢視表格是否有其他事務在通路操作時，一看表鎖IS就知道有其他事務對表格内部某些資料持有S鎖，并且還沒有釋放，那麼這個時候，申請X鎖就會處于等待狀态，而不需要一行一行去查詢每一行資料有沒有被其他事務持有鎖，可以大規模的減少查詢 鎖申請情況；
- 當一個表格持有IX表鎖時，支援申請IS、IX表鎖，但是不相容S、X表鎖。
- 比如，事務A對表格 tba 中 id=10 （id為主鍵）進行進行 資料修改，這個時候，會對表格 tba 先申請一個 IX 表鎖（申請成功），然後申請 id =10 的 X 行鎖，申請成功，則 事務A 持有 IX 表鎖、id=10的X 行鎖，此時事務B 查詢 id=20的行，申請表鎖 IS 成功，申請 id=20的 S 行鎖成功；事務C 修改 id=30的行資料，申請表鎖 IX 成功，申請 id=30的行鎖 X成功；但是，事務D中，對整個表格發起update或者全表SELECT操作，需要申請 X表鎖或者S表鎖，正常情況下，應該要對表格的每一行資料進行檢視，確定每一行資料的行鎖情況，但是因為有了意向鎖，事務D一看到 tba 持有 了IX鎖，則明白，tba 中某些行持有X鎖，則會不相容其他事務對tba 表鎖S ，表鎖X的申請。
- 為什麼要引入意向表鎖？
- 在沒有意向鎖的時候，如果事務T 需要給表格 A 添加 一個S 表鎖，那麼就意味這這個表格内部的每一行資料，都不能有X鎖，才能夠申請 S 表鎖成功，如果表格資料很多，一行行查找非常浪費加鎖時間，這個時候，就出現了表格意向鎖，當表格内部某些行發生 UPATE DELETE INSERT操作，則會對表格 加上 一個意向 IX 表鎖，這樣 事務T在申請 表格A的 S 表鎖時，隻需要檢查 表格 A 是否有 IX表鎖，如果有，則意味内部有 部分行資料持有X鎖，則直接進入等待情況，如果表格沒有 IX表鎖，則直接申請S表鎖成功，這是一個多麼節約加鎖時間的操作！
1.2 鎖的級别
- Table Lock
- 表鎖，如果沒有where條件、無可用索引或者擷取的行記錄過多，則會使用 table full scan，添加表鎖
- Record Lock
- 記錄鎖，如果執行計劃使用了索引，則會根據索引的查找情況添加行鎖
- Gap Lock
- 在RR、RS隔離級别，發生在索引值之間，在連續的兩個索引值之間添加鎖，加鎖後，這兩個索引值之間，無法插入新的索引值，不包含行記錄
- Next-Key Lock
- Record Lock 跟Gap Lock的組合，合體成為Next-KEY Lock
表鎖、行鎖都相對好了解，這裡嘗試簡單說明下 GAP LOCK。
假設目前隔離級别為RR，表格 tbgap( id int auto_increment primary key not null , name varchar(50) , sort int , key ix_sort (sort)) engine=innodb;
表格資料如下：
在索引ix_sort上，一共有7個間隙，分别為（-∞，(1,6)），（(1,6)，(2,5)），（(2,5)，(3,2)），（(3,2)，(5,4)），（(5,4)，(6,1)），（(6,1)，(7,3)），（(7,3)，+∞），而根據實際的隔離級别及鎖申請情況，加在這些間隙上的鎖，則成為 GAP LOCK 。
1.3 鎖與隔離級别（不考慮 lock in shar mode跟for update ）
- RU，讀未送出記錄，不加鎖讀，正常寫鎖；
- RC，快照讀，無鎖；目前讀，加 Record Lock
- RR，快照讀，無鎖；目前讀，對讀取到的記錄加 Record Lock，同時為了確定where條件範圍内的資料無變化，會增加Next key lock
- RS，讀寫均為目前讀，不支援快照讀。包括select 在内，對讀取到的記錄加 Record Lock，同時為了確定where條件範圍内的資料無變化，會增加Next key lock。
2 鎖的申請與釋放過程
看SQL語句的鎖情況，需要結合隔離級别、執行計劃、表結構等，同一個SQL，不同的隔離級别、表結構、執行計劃，其鎖情況不一定是一樣的！
本次模拟這3個表格，age列分别：無索引、有一般索引、有唯一索引。表結構結束及資料如下：
CREATE TABLE tb_no_index ( id int primary key not null auto_increment, age int not null, name varchar(100) );
CREATE TABLE tb_index ( id int primary key not null auto_increment, age int not null, name varchar(100) KEY ix_age(age) );
CREATE TABLE tb_unique_index ( id int primary key not null auto_increment, age int not null,name varchar(100) UNIQUE KEY ix_age(age) );
INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25);
INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25);
INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25);
每個表格IX_age的索引行數就據如下圖展示：
| age | 2 | 4 | 7 | 9 | 21 | 25 |
| id | 1 | 5 | 3 | 6 |
每個表格主鍵上面的行數就據如下圖展示：
| name | null |
2.1 Read Uncommitted
所有事務隔離級别設定： set session transaction isolation level read Uncommitted ;
RU是讀未送出，不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 語句，均為讀未送出，不加鎖，存在髒讀、不可重複讀及幻讀。
所有UPDATE、DELETE、INSERT擷取目前讀記錄，加鎖。
| 表格 SQL | select * from tbname where age/id ... | update tbname set name=... where id = 4 | where age = 21 | where age between 5 and 15 |
| tb_no_index | 讀不加鎖，讀未送出資料 可能有髒讀、不可重複讀及幻讀 | 目前讀，根據主鍵修改資料 tbname 加意向表鎖 IX id=4 加 行鎖 X | 表格的age列無索引，是以update過程中，全表加X鎖 支援semi-constent-read，如果有其他update語句修改其他行不堵塞，但是不支援 select ... for update | 同左 |
| tb_index | 表格的age列有索引，update過程中 tb_index 加 表格意向鎖 IX age索引上面，age=21 行添加行鎖 X 再在主鍵上，給id=3 這一行資料，添加行數 X | 表格的age列有索引，update過程涉及age=7,9 兩行資料 tb_index 加表格意向鎖 IX age索引上面，age=7，age=9 行添加行鎖 X 再在主鍵上，給id=2，id=5 這一行資料，添加行數 X | ||
| tb_unique_index | 同上 |
2.2 Read Committed
所有事務隔離級别設定： set session transaction isolation level read committed ;
RC是讀已送出，不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 語句，均為 快照讀，不加鎖，同個事務内讀取同一個版本的資料，可能非最新資料，但是不存在髒讀、不可重複讀及幻讀情況。
下表中，黃綠色 字型 是RC與RU隔離級别不同的地方，仔細閱讀分析結果可以知道，在 RU 跟 RC 間，最大的差別在于 SELECT 的查詢模式，RU 為 讀未送出，而 RC 為快照讀。UPATE/DELETE/INSERT的加鎖模式類同。
| 表格 SQL | ||||
| 快照讀，不加鎖 讀取的資料不一定是最新版本，但是事務内的所有查詢讀取資料都是同一版本的行資料，不存在髒讀、不可重複讀及幻讀的情況 | | |||
| | | |||
2.3 Read Repeatable
所有事務隔離級别設定： set session transaction isolation level repeatable read ;
RR隔離級别中，SELECT操作支援快照讀，所有的UPDATE/DELETE/INSERT加鎖，鎖類型會新增一個GAP LOCK。
| 表格 SQL | ||||
| | 表格的age列無索引，是以update過程中 全表加X鎖，期間全表堵塞UPDATE\DELETE\INSERT | |||
| 你以為結束了！并沒有，這裡有趣了！ 還會添加兩個gap lock （(9,2) ,（21,3)），（(21,3), (21,25)） 這裡我們單獨拎出小表格來分析。 | 同時會在索引 age的值上添加 3個 gap lock，分别為 （(4,4)，(7,5)）、（(7,5)，(9,2)）、（(9,2)，(21,3)） | |||
| 以為跟上面的加鎖範圍一樣，no no no 唯一索引列上 每一個age都是唯一的，也就是age=21隻有一個，不會再INSERT一個新的 age =21進來，故在這裡不需要加gap lock，加鎖情況如下： age=21 行添加行鎖 X | 但是，範圍查詢添加到gap lock在其他情況下跟非唯一索引會有一些差别，可以看下表的例子。 |
這裡做亮點補充說明：
2.3.1 RR下的非唯一索引加鎖情況
update tbname set name=... where age = 21
還記得上篇文章說過 RR隔離級别可以防止 幻讀嗎？因為在RR隔離級别中，加多了next-key = record lock + gap lock，gap lock是加在索引值之間的鎖。也就是 當修改 age=21 的行資料時,除了 在 age=21 這一行添加 X record lock ， 還在 （(9,2) ,（21,3)），（(21,3), (21,25)）這兩個age值得範圍内添加 gap lock。加鎖的情況是：tb_index添加 IX意向鎖，age索引上添加age=21的 x record lock，再在主鍵上的行記錄 id=5 添加 X record lock，同時在 age 值上添加兩個 gap lock，分别為（(9,2) ,（21,3)），（(21,3), (21,25)）。
注意這裡有個誤區，很多小夥伴會認為，那麼這麼加gap鎖，則意味着，當update age=21這一列時， 9<age<25 ，這個範圍内，是不允許進行 UPATE/DELETE/INSERT的。這種推測實際上是不完整的，因為它沒考慮到跟主鍵！！！
注意，每次寫gap lock的時候，都是有加上主鍵值的。比如這裡，當更新 age=21這列時，加了 （(9,2) ,（21,3)），（(21,3), (21,25)） 這兩個範圍的 GAP LOCK，那麼在目前update age=21的事務還沒有結束的情況下，假設有兩條修改SQL的語句：
update tbname set age=9 where id = 1;
update tbname set age=9 where age = 4;
這兩條SQL，是能夠正常執行，還是堵塞呢？
innodb中，索引按照二叉樹排列順序，而這兩條SQL修改後在IX_AGE上的索引值分别為：（9，1）、（9，4），可以發現（9，1）在鍵值（9，2）的左邊，不在GAP LOCK的範圍内，是以，可以正常執行；而（9，4）在鍵值的右邊，剛好在GAP LOCK的範圍内，會被堵塞！總結：第一條UPDATE SQL，正常秩序；第二條UPDATE SQL會被堵塞。
是以，考慮GAP LOCK的時候，一定要注意結合整個索引鍵值來分析，而索引鍵值=索引值+主鍵。
2.3.2 RR下的唯一索引加鎖情況
update tbname set name=... where age between .. and ...
因為唯一索引上面的索引鍵值都是唯一的，故不會出現重複值的插入的情況，下表羅列了同樣的 範圍查詢修改語句，在唯一索引及非唯一索引上加 GAP_LOCK的情況。
加GAP_LOCK的情況如下（注意注意，友善檢視，省略了主鍵值，實際上是需要添加上主鍵鍵值的）：
| where age between 1 and 7 | where age between 2 and 7 | where age between 5 and 10 | where age between 15 and 50 | |
| (-∞,2),(2,4),(4,7),(7,9) | (4,7),(7,9),(9,21) | (9,21),(21,25),(25,+∞) | ||
| (-∞,2),(2,4),(4,7) | (2,4),(4,7) |
2.4 Read Serializable
所有事務隔離級别設定： set session transaction isolation level Serializable ;
| 不支援快照讀，所有SELECT都是目前讀，所有SELECT操作都需要加S鎖,除主鍵定值查找\唯一索引定值查找外,其他基于索引或者主鍵的範圍查找都會添加 S GAP LOCK。并發度是四個隔離級别中性能最差的。 | ||||
| 在age索引上 添加兩個gap lock （(9,2) ,（21,3)），（(21,3), (21,25)） | 同時會在索引 age的值上添加 3個 gap lock，分别為 | |||
這裡詳細的來分析下 RS 隔離級别下的SELECT操作加的鎖：
| 表格 SQL | where id=5 | where id betwee 5 and 15 | where age=21 | where age betwee 5 and 9 |
| 主鍵定值查找 表格tbname 添加 IS 意向鎖 id=5 添加 S鎖 | 主鍵範圍查找 id=5,id=6 兩行資料 添加 S鎖 同時添加2個 S GAP LOCK ,分别為 （(5,7),(6,25)）跟（(6,25),+∞） | 全表查找 表格 tbname 添加 IS 意向鎖 由于全表查找，整個表格 再次添加 S 表鎖 | 由于全表查找，整個表格 再次添加S 表鎖 | |
| ix_age索引查找 表格tbname 添加 IS 意向鎖 索引上 age = 21 添加 S 行鎖 主鍵上 id=3 添加 S 行鎖 同時添加 2個 S GAP LOCK ，分别為 （(9,2) ,（21,3)），（(21,3), (21,25)） | age索引上面，age=7，age=9 行添加行鎖 S 再在主鍵上，給id=2，id=5 這一行資料，添加行數 S 同時會在索引 age的值上添加 3個 S gap lock，分别為 | |||
| 由于age列唯一，故不需要添加GAP LOCK | 同時會在索引 age的值上添加 2 個 S gap lock，分别為 （(4,4)，(7,5)）、（(7,5)，(9,2)） |
至此，已說明了四個隔離級别是如何加鎖，那麼，釋放鎖呢？
在MySQL INNODB中，遵循的是 strong strict 2-PL，也就是所有的write lock 跟read lock 都是在 事務 commit後才釋放。
3 SQL分析
考慮到下文的例子，這裡補充兩個概念。
ICP：
MRR：
假設表格 tb_lock ( id int primary key not null, age int,score int,name varchar(10), key ix_age_score ( age, score ) ) 資料修改如下 ：
假設MySQL目前的隔離級别為 RR，執行 UPDATE tb_index WHERE age between 5 and 22 and score between 1 and 10 and name is not null，其執行計劃如下：
那麼，是如何加鎖的呢？
首先，可以看到是根據索引 ix_age_score 查找，那麼分為兩種情況來分析，第一種，資料庫支援ICP；第二種，資料不支援ICP。
3.1 支援ICP情況
當資料庫支援ICP的時候，根據複合索引第一列 age 查找 age between 5 and 22，然後在索引内部過濾 score between 1 and 10後，取得索引值後，如果資料庫支援 MRR，則會把取得的索引值放到buffer中，對主鍵進行排序，然後可以根據順序的主鍵值去 主鍵中查找行資料，如果不支援，則跳過這一步排序步驟，直接根據索引值内部的主鍵值，查找主鍵行數，最後過濾 name is not null 。
加鎖過程如下，tb_lock添加 IX 意向鎖，在索引 ix_age_score 給索引值（7，10，5），（21，4，3）添加上 X record lock，并添加4個 X GAP LOCK，如圖檔紅色素箭頭展示，分别為（(4,7,4), (7,10,5)）,（(7,10,5), (9,15,2)）,（(9,15,2),(21,4,3)）,（(21,4,3)，(25,1,6)）,最後在主鍵上給id=3及id=5 兩行數就添加X record lock。
3.2 不支援ICP情況
當資料庫不支援ICP的時候，根據複合索引第一列 age 選擇 age between 5 and 22，然後根據篩選的索引值 （7，10，5），（9，15，2），（21，4，3）中的主鍵 5、2、3，找到對應的行資料，再在行資料中 過濾 score between 1 and 10 and name is not null。
加鎖過程如下，tb_lock添加 IX 意向鎖，在索引 ix_age_score 給索引值（7，10，5），（9，15，2），（21，4，3）添加上 X record lock，并添加4個 X GAP LOCK，如圖檔紅色素箭頭展示，分别為（(4,7,4), (7,10,5)）,（(7,10,5), (9,15,2)）,（(9,15,2),(21,4,3)）,（(21,4,3)，(25,1,6)）,最後在主鍵上給id=2、id=3、id=5 兩行數就添加X record lock。
參考文檔：http://hedengcheng.com/?p=771#_Toc374698321
如果轉載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！