MySQL🐬 InnoDB 教我的事：想鎖的沒鎖 ？不該鎖的被鎖了！
筆者曾任職 Yahoo，現在區塊鏈產業打滾，《軟體需求溝通 ─ 從外商公司學跨部門協作開發》線上課程講師，紛絲團《程式猿吃香蕉🍌》
在併發的情況下，MySQL InnoDB 的鎖常常讓人頭痛，一不小心就像被下了「統統石化」(Petrificus Totalus) 的魔法，一動也不能動 :)
在前一篇文章，我整理了 MySQL 的 InnoDB 如何處理鎖和資源的關係，也談了聚簇索引 (Clustered index) 以及二級索引 (Secondary index)。此外，還提到了覆蓋索引 (Covering index) 這項重要觀念。附上文章連結如下。
這篇文章我做了一個有趣的範例，來串連上一篇文章提到的各種觀念。也算是實際驗證一下 InnoDB 的行為 (笑)。這範例乍看之下，你會覺得 InnoDB 怎麼亂鎖一通？但其實 InnoDB 是有邏輯的。
實驗的內容我都有附上指令還有結果，如果有興趣也可以跟著這篇文章一起做做看，讓我們開始吧！
▍範例：想鎖的沒鎖 ？不該鎖的被鎖了！
以下範例基於 MySQL 8.0，採用 MySQL 預設的「可重複讀」(Repeatable Read) 隔離層級 (Isolation level)，我們用下列 SQL 語句建立一張資料表：
CREATE TABLE `my_table` (
`id` int NOT NULL,
`my_key` int DEFAULT NULL,
`other_data` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `my_key` (`my_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4其中 id 是主鍵 (Primary Key)，會產生聚簇索引，而 my_key 這個欄位使用了 KEY 語法，會建立二級索引。也是就是說，將會有兩個 B+tree 資料結構產生。
接下來我們寫入三筆資料，如下圖所示：
插入資料後，可以閉上眼睛具像化地想像：轟的一聲！有兩棵樹 (B+tree) 冒了出來 😄
接下來，有兩個不同的交易 (Transaction) 先後啟動：
這時候問題來了，Transaction 2 會執行成功嗎？
可能有人會想，my_key = 33 那一行已經被 Transaction 1 透過 for share 語法鎖定了(如下圖粉紅色框所示)，Transaction 2 要更新 id=3 那一行的資料order_data，應該會被卡 (block) 住吧？
然而，實際的執行結果是：Transaction 2 可以執行成功！下圖左邊為 Transaction 1，右邊為 Transaction 2 的執行結果。
怎麼好像該鎖的沒鎖？為什麼會這樣呢？
▍鎖與資源的關係
在併發編程的世界裡面，釐清「鎖」與「資源」的關係是非常重要的，這也是很重要的思維模型。因此這個例子同樣可以用「鎖」與「資源」的關係來看。
在前一篇文章我們從 MySQL 原始碼裡看到：
鎖透過索引找到對應資源 (數據)來鎖
甚至我們可以更形象化的去理解：
索引是找到資料的「路徑」，鎖只要把這段「路徑」封了，就可以避免資料被併發修改
以剛才的資料表來說，有哪些「路徑」 呢？從我們建立 my_table 的語法可以看出(如下)，我們有兩個不同的路徑，聚簇索引 id 和二級索引 my_key。
CREATE TABLE `my_table` (
`id` int NOT NULL,
`my_key` int DEFAULT NULL,
`other_data` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `my_key` (`my_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4這時候我們再來看 Transaction 1 和 Transaction 2 的行為，觀察他們實際走的路徑：
◎ 解析 Transaction 1 的鎖與資源關係
Transaction 1 執行 select id from my_table where my_key=33 for share，走的是 my_key 這個二級索引。並且：
因為選取的資料是 id ，資料在二級索引上面都能找齊全，會觸發覆蓋索引 (Covering Index)，不需要回到聚簇索引去撈資料。
我們可以推測，鎖有可能卡在二級索引上。我們可以透過以下指令驗證，看 InnoDB 上了哪些鎖，看鎖是怎麼封路的 (笑)。
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCKS\G;下圖為執行結果，我們可以看到 INDEX_NAME 欄位為 my_key 二級索引(粉紅框標示)。跟我們推測的一樣，鎖就「封」在 my_key 二級索引的路徑上，不給其他交易 (Transaction) 修改對應資源。
那鎖住的範圍有多大呢？我們仔細看一下 LOCK_MODE (藍色框標示)，分別是 S 以及 S, GAP。這表示第一個鎖是 Shared 模式的 Next-key Lock 臨鍵鎖，而第二個鎖是Shared 模式的間隙鎖 Gap Lock。畫在 B+tree 以圖像表示，如下圖：
my_key 的三筆資料分別為 11, 33, 55，存在葉子節點上，11 和 33 之間存在Next-key Lock 臨鍵鎖，臨鍵鎖的範圍特性是「左界不包含，右界包含」，以數學符號 (11,33] 表示，意思是範圍從 12 到 33。而 33 和 55 之間存在一個間隙鎖 Gap Lock ，間隙鎖的範圍特性是「左右界都不包含」，以數學符號 (33,55)表示， 意思是範圍從 34 到 54。
也就是說， my_key 二級索引在 (11,55)之間數值的寫入都被鎖住了：my_key 不允許插入大於 11且小於 55 的數值。
為什麼要鎖這麼大的範圍呢？
因為 MySQL 預設隔離層級：可重複讀 (Repeatable Read) ，要求同一個交易 (Transaction) 裏面多次 select 資料需要一致。因此 Transaction 1 執行 select id from my_table where my_key=33 for share 都要回一致的內容，也就是說：
如果沒有鎖好，當有另一個交易 Transaction，多插入一筆 my_key=33，Transaction 1 再次 select id from my_table where my_key=33 for share; 就會發生讀取不一致的問題。
以 B+tree 來說，在 my_key=33 葉子節點的前後，都可能被插入「另一筆」 my_key=33，因此這個範圍內都要鎖住。(如下圖所示)
這就是為什麼 Transaction 1 要鎖那麽大範圍的原因。接下來我們來看 Transaction 2。
◎ 解析 Transaction 2 的鎖與資源關係
Transaction 2 執行的是 update my_table set other_data=0 where id=3 InnoDB 會走聚簇索引找到該筆資料。又因為 InnoDB 在交易 (Transaction) 中，取資料來做更新 (update) 都是採用當前讀 (Current Read)。可以理解為 InnoDB 一定要取「最新」的資料來做更新。InnoDB 的鎖為了保護資料的一致性，會強制在聚簇索引上封路，確保取到最新的值更新時，不會有其他人中途做修改。我們同樣下指令來驗證 InnoDB 產生的鎖：
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCKS\G;下圖為執行結果，我們可以看到 INDEX_NAME 為 PRIMARY (粉紅色框標示) 聚簇索引，對 Transaction 2 來說，InnoDB 果然鎖在聚簇索引上。
解析完 Transaction 1 和 Transaction 2 的鎖與資源之後，我們來看最開始的問題：
Transaction 1 選中的那筆資料 (如下圖粉紅色框標示)， Transaction 2 怎麼能更新呢？
▍為什麼 Transaction 2 的更新沒被鎖住？
答案是：
因為 Transaction 1 鎖在二級索引，而 Transaction 2 請求的鎖是在聚簇索引！
兩者鎖的位置不一樣，當然就不會有衝突了，所以 Transaction 2 可以更新成功。可以想像成：你鎖你家的門，我鎖我家的門，兩把鎖各別鎖在兩個不一樣的位置，不用互相爭搶。Transaction 1 的鎖，當然擋不住 Transaction 2 的執行。
▍延伸討論
接下來我們來看幾種有趣的變化題 :)
◎ 如果不觸發覆蓋索引會怎樣？
範例中我們看到，因為 Transaction 1 撈的資料都是二級索引可以提供的，觸發了覆蓋索引，導致 Transaction 1 的鎖只卡在二級索引上。那麼如果 Transaction 1 改為撈 other_data 變成：
select other_data from my_table where my_key=33 for share;故意不要觸發覆蓋索引，會發生什麼事呢？
此時，Transaction 1 在二級索引無法撈到全部的資料，InnoDB 就會需要到聚簇索引去撈 other_data 的內容。這樣的話，鎖的範圍就會多一筆，在聚簇索引鎖住 id=3 這筆資料 (如下圖所示)，這麼一來，Transaction 2 就無法更新 id=3 那筆資料了。
◎ 如果改用 select … for update 會怎樣？
Transaction 1 在原範例是 select id from my_table where my_key=33 for share如果改為 select .. for update 語法，例如：
select id from my_table where my_key=33 for update;會發生什麼事呢？
InnoDB 會強制對聚簇索引上鎖，原因就像前面提到的，InnoDB 做更新操作時，需要取最新的資料更新，也就是當前讀 (Current Read)。為了確保當前讀 (Current Read) 資料是「最新」，也就是說不能有「更」新的資料出現，即使 Transaction 1 撈的內容在二級索引可以撈齊全，還是會在聚簇索引上鎖，讓 Transaction 2 無法更新 id=3 那筆資料。
◎ 如果 Transaction 2 改為更新 id 會怎樣？
既然 Transaction 1 的鎖在二級索引，那麽 Transaction 2 不更新 other_data 改為更新 id，應該也可以成功吧？例如：
update my_table set id=1000 where id=3;答案是：不會成功，會被卡住。
在上一篇文章我們有提到二級索引會保存它和聚簇索引的對應關係，以範例來說，my_key 這個二級索引就保存了 「my_key=33 對應到 id=3」，如果要把 id=3 改為 id=1000，等於是要把：
「my_key=33 對應到 id=3」改為「my_key=33 對應到 id=1000」
因此，Transaction 2 會動到二級索引的內容。而 Transaction 1 的鎖就鎖在了二級索引，會造成衝突，將會卡住 Transaction 2 更新 id 的執行。
我們同樣驗證一下 update my_table set id=1000 where id=3 執行時 InnoDB 的鎖 (如下圖)。我們可以看到 INDEX_NAME 是 my_key (粉紅框標示)，Transaction 2 果然請求了二級索引上的鎖，並且 LOCK_STATUS 是 WAITING (綠色框標示)，表示正在等待中。Transaction 2 確實被卡住了，無法更新 id。
◎ 如果再一個 Transaction 3 寫入資料會怎樣？
以剛才的範例，再加入一個 Transaction 3 要寫入 (2,22,222)這一筆資料 (如下圖)。乍看之下，好像是一個完全無關的插入，不會被其他 Transaction 卡住？但其實是會的。
你可能會想，怎麼亂鎖一通？而 InnoDB 其實是有邏輯的。
因為，Transaction 3 要寫入的位置 my_key=22 被 Transaction 1 在二級索引上的臨鍵鎖卡住了(如下圖所示)。因為 my_key=22 數值在 (11,33] 範圍之間。
實際驗證一下(如下圖)，可以看到 Transaction 3 要在二級索引上請求鎖：INDEX_NAME 是 my_key (粉紅框標示)，而 LOCK_STATUS 是 WAITING ，表示它被卡住了。從 LOCK_MODE 的值 (藍色框標示) X, GAP, INSERT_INTENTION 可以看出，它請求的鎖是排他的 (X) 、類似間隙鎖的 (GAP) 插入意向鎖 (INSERT_INTENTION)。
而 Transaction 3 它請求的鎖，要插入的範圍是哪裡呢？我們可以看 LOCK_DATA 33, 3 (綠色框標示)，這裡的 LOCK_DATA 表示鎖的右界，其中 33 表示 my_key=33，而後面的 3 表示主鍵的值 id=3。已知鎖的右界是 33，我們來查看左界，根據 B+tree 資料內容(上圖)，33 的左邊是 11，可以得知左界是 11。又因為鎖的性質是類似間隙鎖的 (GAP)，範圍特性是「左右界都不包含」，因此 Transaction 3 它請求的鎖範圍是 (11,33)。而這個範圍與 Transaction 2 的臨鍵鎖 (11,33] 範圍衝突。驗證後，這個結果跟我們推論的一致。
▍小結
從上述的範例，再次用到了上一篇文章提到的聚簇索引 (Clustered index) 、二級索引 (Secondary index) 以及覆蓋索引的觀念。並且
同樣套用「鎖」和「資源」的思維模型來解題。遇到在複雜的問題都能化繁為簡。
因此，了解鎖的特性和行為就很重要了。可以衍生很多有趣的題目：
- 鎖和鎖之間的關係？是互斥還是兼容？
- 鎖的範圍會升級或是降級嗎？
- 鎖可以重入 (Reentrant) 嗎？再次加鎖會出什麼事？
- 鎖和資源的關係？是建立在怎樣的對應關係上？
- 怎樣的情況下會發生死鎖？
在分散式的環境下，MySQL InnoDB 只是其中一部分，還有訊息隊列、快取等等，不同元件 (Component) 交互起來又會更加的複雜，之後我會再分享更多相關的內容。
如果你對 MySQL InnoDB 或是併發編程有興趣，也可以閱讀我的其他文章：
若是喜歡我的內容，可以訂閱我的粉絲團《程式猿吃香蕉🍌》，你的鼓勵可以幫助到我很多，在軟體開發的路上，我們一起分享交流，一起成長。
