在上一篇文章 《鎖的類型以及加鎖原理》
主要總結了 MySQL 鎖的類型和模式以及基本的加鎖原理，今天我們就從原理走向實戰，分析常見 SQL 語句的加鎖場景。了解了這幾種場景，相信小夥伴們也能舉一反三，靈活地分析真實開發過程中遇到的加鎖問題。
如下圖所示，資料庫的隔離等級，SQL 語句和目前資料庫資料會共同影響該條 SQL 執行時資料庫生成的鎖模式，鎖類型和鎖數量。
隔離等級對加鎖的影響
MySQL 的隔離等級對加鎖有影響，是以在分析具體加鎖場景時，首先要确定目前的隔離等級。
- 讀未送出（Read Uncommitted 後續簡稱 RU）：可以讀到未送出的讀，基本上不會使用該隔離等級，是以暫時忽略。
- 讀已送出（Read Committed 後續簡稱 RC）：存在幻讀問題，對目前讀擷取的資料加記錄鎖。
- 可重複讀（Repeatable Read 後續簡稱 RR）：不存在幻讀問題，對目前讀擷取的資料加記錄鎖，同時對涉及的範圍加間隙鎖，防止新的資料插入，導緻幻讀。
- 序列化（Serializable）：從 MVCC 并發控制退化到基于鎖的并發控制，不存在快照讀，都是目前讀，并發效率急劇下降，不建議使用。
這裡說明一下，RC 總是讀取記錄的最新版本，而 RR 是讀取該記錄事務開始時的那個版本，雖然這兩種讀取的版本不同，但是都是快照資料，并不會被寫操作阻塞，是以這種讀操作稱為 快照讀（Snapshot Read）
MySQL 還提供了另一種讀取方式叫目前讀（Current Read），它讀的不再是資料的快照版本，而是資料的最新版本，并會對資料加鎖，根據語句和加鎖的不同，又分成三種情況：
- SELECT ... LOCK IN SHARE MODE：加共享(S)鎖
- SELECT ... FOR UPDATE：加排他(X)鎖
- INSERT / UPDATE / DELETE：加排他(X)鎖
目前讀在 RR 和 RC 兩種隔離級别下的實作也是不一樣的：RC 隻加記錄鎖，RR 除了加記錄鎖，還會加間隙鎖，用于解決幻讀問題。
不同 SQL 語句對加鎖的影響
不同的 SQL 語句當然會加不同的鎖，總結起來主要分為五種情況：
- SELECT ... 語句正常情況下為快照讀，不加鎖；
- SELECT ... LOCK IN SHARE MODE 語句為目前讀，加 S 鎖；
- SELECT ... FOR UPDATE 語句為目前讀，加 X 鎖；
- 常見的 DML 語句（如 INSERT、DELETE、UPDATE）為目前讀，加 X 鎖；
- 常見的 DDL 語句（如 ALTER、CREATE 等）加表級鎖，且這些語句為隐式送出，不能復原。
其中，目前讀的 SQL 語句的 where 從句的不同也會影響加鎖，包括是否使用索引，索引是否是唯一索引等等。
目前資料對加鎖的影響
SQL 語句執行時資料庫中的資料也會對加鎖産生影響。
比如一條最簡單的根據主鍵進行更新的 SQL 語句，如果主鍵存在，則隻需要對其加記錄鎖，如果不存在，則需要在加間隙鎖。
至于其他非唯一性索引更新或者插入時的加鎖也都不同程度的受到現存資料的影響，後續我們會一一說明。
具體場景分析
具體 SQL 場景分析主要借鑒何登成前輩的《MySQL 加鎖處理分析》文章和 aneasystone 的系列文章，在他們的基礎上進行了總結和整理。
我們使用下面這張 book 表作為執行個體，其中 id 為主鍵，ISBN（書号）為二級唯一索引，Author（作者）為二級非唯一索引，score（評分）無索引。
UPDATE 語句加鎖分析
下面，我們先來分析 UPDATE 相關 SQL 在使用較為簡單 where 從句情況下加鎖情況。其中的分析原則也适用于 UPDATE,DELETE 和 SELECT ... FOR UPDATE等目前讀的語句。
聚簇索引，查詢命中
聚簇索引就是 InnoDB 存儲引擎下的主鍵索引，具體可參考
《MySQL索引》。
下圖展示了使用 UPDATE book SET score = 9.2 WHERE ID = 10 語句命中的情況下在 RC 和 RR 隔離等級下的加鎖，兩種隔離等級下沒有任何差別，都是對 ID = 10 這個索引加排他記錄鎖。
聚簇索引，查詢未命中
下圖展示了 UPDATE book SET score = 9.2 WHERE ID = 16 語句未命中時 RR 隔離級别下的加鎖情況。
在 RC 隔離等級下，不需要加鎖；而在 RR 隔離級别會在 ID = 16 前後兩個索引之間加上間隙鎖。
值得注意的是，間隙鎖和間隙鎖之間是互不沖突的，間隙鎖唯一的作用就是為了防止其他事務的插入新行，導緻幻讀，是以加間隙 S 鎖和加間隙 X 鎖沒有任何差別。
二級唯一索引，查詢命中
下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0003' 在 RC 和 RR 隔離等級下命中時的加鎖情況。
在 InnoDB 存儲引擎中，二級索引的葉子節點儲存着主鍵索引的值，然後再拿主鍵索引去擷取真正的資料行，是以在這種情況下，二級索引和主鍵索引都會加排他記錄鎖。
二級唯一索引，查詢未命中
下圖展示了 UPDATE book SET score = 9.2 WHERE ISBN = 'N0008' 語句在 RR 隔離等級下未命中時的加鎖情況，RC 隔離等級下該語句未命中不會加鎖。
因為 N0008 大于 N0007，是以要鎖住 (N0007,正無窮)這段區間，而 InnoDB 的索引一般都使用 Suprenum Record 和 Infimum Record 來分别表示記錄的上下邊界。Infimum 是比該頁中任何記錄都要小的值，而 Supremum 比該頁中最大的記錄值還要大，這兩條記錄在建立頁的時候就有了，并且不會删除。
是以，在 N0007 和 Suprenum Record 之間加了間隙鎖。
為什麼不在主鍵上也加 GAP 鎖呢？歡迎留言說出你的想法。
二級非唯一索引，查詢命中
下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Tom' 語句在 RC 隔離等級下命中時的加鎖情況。
我們可以看到，在 RC 等級下，二級唯一索引和二級非唯一索引的加鎖情況是一緻的，都是在涉及的二級索引和對應的主鍵索引上加上排他記錄鎖。
但是在 RR 隔離等級下，加鎖的情況産生了變化，它不僅對涉及的二級索引和主鍵索引加了排他記錄鎖，還在非唯一二級索引上加了三個間隙鎖，鎖住了兩個 Tom 索引值相關的三個範圍。
那為什麼唯一索引不需要加間隙鎖呢？間隙鎖的作用是為了解決幻讀，防止其他事務插入相同索引值的記錄，而唯一索引和主鍵限制都已經保證了該索引值肯定隻有一條記錄，是以無需加間隙鎖。
需要注意的是，上圖雖然畫着 4 個記錄鎖，三個間隙鎖，但是實際上間隙鎖和它右側的記錄鎖會合并成 Next-Key 鎖。
是以實際情況有兩個 Next-Key 鎖，一個間隙鎖(Tom60,正無窮)和兩個記錄鎖。
二級非唯一索引，查詢未命中
下圖展示了 UPDATE book SET score = 9.2 WHERE Author = 'Sarah' 在 RR 隔離等級下未命中的加鎖情況，它會在二級索引 Rose 和 Tom 之間加間隙鎖。而 RC 隔離等級下不需要加鎖。
無索引
當 Where 從句的條件并不使用索引時，則會對全表進行掃描，在 RC 隔離等級下對所有的資料加排他記錄鎖。在RR 隔離等級下，除了給記錄加鎖，還會對記錄和記錄之間加間隙鎖。和上邊一樣，間隙鎖會和左側的記錄鎖合并成 Next-Key 鎖。
下圖就是 UPDATE book SET score = 9.2 WHERE score = 22 語句在兩種隔離等級下的加鎖情況。
聚簇索引，範圍查詢
上面介紹的場景都是 where 從句的等值查詢，而範圍查詢的加鎖又是怎麼樣的呢？我們慢慢來看。
下圖是 UPDATE book SET score = 9.2 WHERE ID <= 25 在 RC 和 RR 隔離等級下的加鎖情況。
RC 場景下與等值查詢類似，隻會在涉及的 ID = 10，ID = 18 和 ID = 25 索引上加排他記錄鎖。
而在 RR 隔離等級下則有所不同，它會加上間隙鎖，和對應的記錄鎖合并稱為 Next-Key 鎖。除此之外，它還會在(25, 30] 上分别加 Next-Key 鎖。這一點是十分特殊的，具體原因還需要再探究。
二級索引，範圍查詢
下圖展示了 UPDATE book SET ISBN = N0001 WHERE score <= 7.9 在 RR 級别下的加鎖情況。
修改索引值
UPDATE 語句修改索引值的情況可以分開分析，首先 Where 從句的加鎖分析如上文所述，多了一步 Set 部分的加鎖。
下圖展示了 UPDATE book SET Author = 'John' WHERE ID = 10 在 RC 和 RR 隔離等級下的加鎖情況。除了在主鍵 ID 上進行加鎖，還會對二級索引上的 Bob(就值) 和 John(新值) 上進行加鎖。
DELETE 語句加鎖分析
一般來說，DELETE 的加鎖和 SELECT FOR UPDATE 或 UPDATE 并沒有太大的差異。
因為，在 MySQL 資料庫中，執行 DELETE 語句其實并沒有直接删除記錄，而是在記錄上打上一個删除标記，然後通過背景的一個叫做 purge 的線程來清理。從這一點來看，DELETE 和 UPDATE 确實是非常相像。事實上，DELETE 和 UPDATE 的加鎖也幾乎是一樣的。
INSERT 語句加鎖分析
接下來，我們來看一下 Insert 語句的加鎖情況。
Insert 語句在兩種情況下會加鎖：
- 為了防止幻讀，如果記錄之間加有間隙鎖，此時不能 Insert；
- 如果 Insert 的記錄和已有記錄造成唯一鍵沖突，此時不能 Insert；
除了上述情況，Insert 語句的鎖都是隐式鎖。隐式鎖是 InnoDB 實作的一種延遲加鎖的機制來減少加鎖的數量。
隐式鎖的特點是隻有在可能發生沖突時才加鎖，減少了鎖的數量。另外，隐式鎖是針對被修改的 B+Tree 記錄，是以都是記錄類型的鎖，不可能是間隙鎖或 Next-Key 類型。
具體 Insert 語句的加鎖流程如下：
- 首先對插入的間隙加插入意向鎖（Insert Intension Locks）
- 如果該間隙已被加上了間隙鎖或 Next-Key 鎖，則加鎖失敗進入等待；
- 如果沒有，則加鎖成功，表示可以插入；
- 然後判斷插入記錄是否有唯一鍵，如果有，則進行唯一性限制檢查
- 如果不存在相同鍵值，則完成插入
- 如果存在相同鍵值，則判斷該鍵值是否加鎖
- 如果沒有鎖， 判斷該記錄是否被标記為删除
- 如果标記為删除，說明事務已經送出，還沒來得及 purge，這時加 S 鎖等待；
- 如果沒有标記删除，則報 duplicate key 錯誤；
- 如果有鎖，說明該記錄正在處理（新增、删除或更新），且事務還未送出，加 S 鎖等待；
- 如果沒有鎖， 判斷該記錄是否被标記為删除
- 插入記錄并對記錄加 X 記錄鎖；
後記
本文中講解的 SQL 語句都是十分簡單的，當 SQL 語句包含多個查詢條件時，加鎖的分析過程就往往更加複雜。我們需要使用 MySQL 相關的工具進行分析，并且有時甚至需要查詢 MySQL 相關的日志資訊來了解到底語句加了什麼鎖或者為什麼産生死鎖，下篇文章中我們就主要了解一下這些内容，請大家持續關注。
個人部落格，歡迎來玩