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 或是併發編程有興趣,也可以閱讀我的其他文章:

若是喜歡我的內容,可以訂閱我的粉絲團《程式猿吃香蕉🍌》,你的鼓勵可以幫助到我很多,在軟體開發的路上,我們一起分享交流,一起成長。

--

--

我們是一群軟體開發愛好者,喜歡將軟體知識以簡單生動的方式講給你聽,順口好消化,營養又健康!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jayden Lin

Yahoo 擔任 Lead Engineer,負責廣告系統,帶團隊做跨國開發。也是《程式猿吃香蕉》團隊創辦人,喜歡將實用的軟體知識以簡單生動的方式講給大家聽 😄😄😄