淺談MySQL隔離層級為RR(可重復讀)時不能避免Phantom Read(幻讀)

SQL資料庫
Andy Wu在〈淺談MySQL隔離層級為RR(可重復讀)時不能避免Phantom Read(幻讀)〉中有 1 則留言

情境模擬

我們先來破冰一下，幫大家再回憶一次什麼是Phantom Read(幻讀)。假設今天水果行採用了一個庫存系統，其中商品庫存資料表如下:

CREATE TABLE `items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(16)),
  `remaining` INT NOT NULL DEFAULT 0,
  `is_enabled` INT NOT NULL DEFAULT 0
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

插入幾筆水果庫存的測試資料:

INSERT INTO `items`
  (`name`, `remaining`, `is_enabled`)
VALUES
  ('apple', 20, 1),
  ('banana', 10, 0),
  ('cherry', 0, 0),
  ('guava', 0, 0),
  ('peach', 30, 1);

偷看一下資料表:

mysql> SELECT * FROM items;
+----+--------+-----------+------------+
| id | name   | remaining | is_enabled |
+----+--------+-----------+------------+
|  1 | apple  |        20 |          1 |
|  2 | banana |        10 |          0 |
|  3 | cherry |         0 |          0 |
|  4 | guava  |         0 |          0 |
|  5 | peach  |        30 |          1 |
+----+--------+-----------+------------+
5 rows in set (0.00 sec)

某天，老闆在貨車抵達門口進完貨後，走回自己辦公桌的電腦前面，想看看目前上架的水果狀態。

(水果行老闆為什麼會SQL不是本篇重點)

老闆(交易ID=1) 工讀生小明(交易ID=2)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
START TRANSACTION;
SELECT * FROM items WHERE is_enabled = 1;

此外，老闆知道等等可能會對資料修改，所以還特別用transaction包起來，並指定隔離層級為REPEATABLE READ。

+----+-------+-----------+------------+
| id | name  | remaining | is_enabled |
+----+-------+-----------+------------+
|  1 | apple |        20 |          1 |
|  5 | peach |        30 |          1 |
+----+-------+-----------+------------+
2 rows in set (0.00 sec)

老闆看了看架上的庫存，突然想到蘋果與桃子其實很充足，畢竟剛剛蘋果與桃子才各進貨了一箱，於是想將架上販售的蘋果與桃子庫存修改為50。

而就在此時，在店鋪裡剛補完貨的工讀生小明也正好在操作資料庫，將今天熱騰騰剛上架的香蕉設定為公開販售中。

老闆(交易ID=1) 工讀生小明(交易ID=2)
UPDATE items SET is_enabled = 1 WHERE name = ‘banana’;
COMMIT;

老闆在操作前為了避免自己下錯SQL，於是再SELECT一次確認資料無誤。

老闆(交易ID=1) 工讀生小明(交易ID=2)
SELECT * FROM items WHERE is_enabled = 1; 
+----+-------+-----------+------------+
| id | name  | remaining | is_enabled |
+----+-------+-----------+------------+
|  1 | apple |        20 |          1 |
|  5 | peach |        30 |          1 |
+----+-------+-----------+------------+
2 rows in set (0.00 sec)

(因為隔離層級為REPEATABLE READ，所以就算工讀生小明已經更改了香蕉的上架狀態，對老闆的transaction來說是看不見的)

此時老闆放心的將架上販售的蘋果與桃子庫存修改為50。修改後，老闆又再SELECT一次看看更新後的結果。

老闆(交易ID=1) 工讀生小明(交易ID=2)
UPDATE items SET remaining = 50 WHERE is_enabled = 1;
SELECT * FROM items WHERE is_enabled = 1; 
+----+--------+-----------+------------+
| id | name   | remaining | is_enabled |
+----+--------+-----------+------------+
|  1 | apple  |        50 |          1 |
|  2 | banana |        50 |          1 |
|  5 | peach  |        50 |          1 |
+----+--------+-----------+------------+
3 rows in set (0.00 sec)

老闆揉了揉眼睛，抓了抓沒剩幾根毛的頭髮，實在想不透為什麼明明在隔離層級為REPEATABLE READ的交易下，下了兩次SELECT後緊接著一個UPDATE，最後再SELECT一次出來的東西會跟前兩次不一致，而且是在還沒commit的狀態，也就是還在REPEATABLE READ保護的交易內發生，其實這就是典型幻讀的情境。

 
以上故事是我自己腦補的小劇場，如果略過文字故事的操作大致是這樣，有興趣的可以操作看看:

步驟 老闆(交易ID=1) 工讀生小明(交易ID=2)
1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2 START TRANSACTION;
3 START TRANSACTION;
4 SELECT * FROM items WHERE is_enabled = 1;
5 UPDATE items SET is_enabled = 1 WHERE name = ‘banana’;
6 COMMIT;
7 SELECT * FROM items WHERE is_enabled = 1;
8 UPDATE items SET remaining = 50 WHERE is_enabled = 1;
9 SELECT * FROM items WHERE is_enabled = 1;

底層講解

故事說完了，我們先來想想老闆跟工讀生小明為什麼會下SQL，都知道在REPEATABLE READ的隔離層級下解決了不可重複讀的問題，可是既然都解決了不可重複讀的問題，為什麼我們在MySQL裡對這些重複讀出的資料做更新結果最後會發生幻讀呢？

這個問題其實疑惑了我好幾個月，直到最近在看《資料庫解剖學:從內部深解MySQL運作原理》這本書時，讀到書中快結束的第22.4.1章才突然豁然開郎。為了要回答這個問題，我們必須理解MySQL記錄中的trx_id欄位與ReadView概念。

 

trx_id欄位

在採用InnoDB類型的表內的每筆記錄中的都會有trx_id這個隱藏欄位給MySQL底層使用的，我們app層下的SQL存取不到。而這個trx_id存的值是最後一次修改該row的交易ID，所以實際上我們的table會長這樣:

+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        20 |          1 |        0 |
|  2 | banana |        10 |          0 |        0 |
|  3 | cherry |         0 |          0 |        0 |
|  4 | guava  |         0 |          0 |        0 |
|  5 | peach  |        30 |          1 |        0 |
+----+--------+-----------+------------+----------+

我們假設那五條row是伴隨創表時插入的，沒有被水果行操作過，所以一開始初始為0。

 

ReadView

MySQL為了在REPEATABLE READ的隔離層級下為了做到可重複讀，會在交易的第一個SELECT語句執行當下產生一個ReadView。其中，ReadView包含以下幾個重要欄位:

  • m_ids: 當前活躍的交易ID清單
  • min_trx_id: 當前活躍的交易ID清單內的最小的交易ID(交易ID有遞增的特性，也就是當前活躍交易中最早的交易)
  • max_trx_id: MySQL打算分配給下一個交易的交易ID
  • creator_trx_id: 創建此ReadView的交易ID

這個ReadView也就是網路上很多文章把他解釋成的snapshot，記錄了目前當下交易的狀態，並且活存至整個交易commit或rollback結束。從ReadView產生後的所有SELECT語句，都會先透過以下4個判斷，決定每一條row該不該被當前交易看到:

  1. 如果該row記錄的trx_id與ReadView中的creator_trx_id相同，表示當前交易在存取它自己修政過的記錄，所以該row可以被當前交易存取。

  2. 如果該row記錄的trx_id小於ReadView中的min_trx_id，表示生成該row的交易在當前交易生成ReadView前已經提交，所以該row可以被當前交易存取。

  3. 如果該row記錄的trx_id大於或等於ReadView中的max_trx_id，表示生成該row的交易在當前交易生成ReadView後才開啟，所以該row不可以被當前交易存取。

  4. 如果該row記錄的trx_id在ReadView的min_trx_id和max_trx_id之間，則需要判斷trx_id是否在m_ids清單中。如果存在，說明創建ReadView時生成該row的交易還是活躍的，該row不可以被存取；如果不存在，說明創建ReadView時生成該row的交易己經被提交，該row可以被存取。

(以上截錄自書中第21.3.2章)

 

幻讀是如何發生的

介紹完trx_id欄位與ReadView包含的資訊以及SELECT怎麼使用ReadView來獲取該顯示的row後，我們終於具備回答為什麼REPEATABLE READ的隔離層級下還是會發生幻讀的知識了。

在上面步驟4老闆的交易1內第一次下了SELECT語句，一個新的ReadView被創建了，此時ReadView記錄了以下資訊:

  • m_ids: [交易ID=1, 交易ID=2]
  • min_trx_id: 交易ID=1
  • max_trx_id: 交易ID=3
  • creator_trx_id: 交易ID=1

然後SELECT語句取得了符合條件is_enabled = 1的兩條記錄。

+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        20 |          1 |        0 |
|  5 | peach  |        30 |          1 |        0 |
+----+--------+-----------+------------+----------+
2 rows in set (0.00 sec)

步驟5，工讀生小明更新了剛上架的香蕉設定is_enabled = 1，此時真實的table如下:

+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        20 |          1 |        0 |
|  2 | banana |        10 |          1 |        2 |
|  3 | cherry |         0 |          0 |        0 |
|  4 | guava  |         0 |          0 |        0 |
|  5 | peach  |        30 |          1 |        0 |
+----+--------+-----------+------------+----------+

可以注意到香蕉的那條row除了is_enabled被更新外，隱藏的trx_id欄位也一併被更新成工讀生小明的交易ID=2。

步驟6，小明提交了該交易持久化了香蕉的那條row的變動。

步驟7，老闆在交易1內第二次下了SELECT語句，此時對交易1來說已經有現成的ReadView了，所以SELECT會參考前次創建的ReadView來使用。(題外話: 如果隔離層級小於REPEATABLE READ則會每次SELECT都創建新的ReadView，聰明的你可以想想看這跟ReadView的哪個特性有關係。)

前次創建的ReadView:

  • m_ids: [交易ID=1, 交易ID=2]
  • min_trx_id: 交易ID=1
  • max_trx_id: 交易ID=3
  • creator_trx_id: 交易ID=1
+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        20 |          1 |        0 |
|  2 | banana |        10 |          1 |        2 | <--
|  3 | cherry |         0 |          0 |        0 |
|  4 | guava  |         0 |          0 |        0 |
|  5 | peach  |        30 |          1 |        0 |
+----+--------+-----------+------------+----------+

當MySQL搜尋到這條row時，雖然他有符合查詢條件，但並沒有符合ReadView要讀出該row的判斷4:

  • trx_id:2 在min_trx_id: 1與max_trx_id: 3之間，且
  • trx_id不在m_ids清單中

所以該條香蕉的row不會被MySQL抓出來，其餘的蘋果與桃子因為trx_id是0，符合了判斷2讀出的條件，這也是為什麼MySQL可以在步驟7實現REPEATABLE READ。

+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        20 |          1 |        0 |
|  5 | peach  |        30 |          1 |        0 |
+----+--------+-----------+------------+----------+
2 rows in set (0.00 sec)

步驟8，老闆想利用is_enabled = 1這個條件一次更新符合的row(蘋果與桃子)的庫存為50，但因為ReadView顧名思義只會在read時作動判斷，不會阻止UPDATE或DELETE改動那筆新符合is_enabled = 1的香蕉的row，所以實際上MySQL的UPDATE語句影響了三筆記錄，並且將三筆記錄隱藏的trx_id欄位也一併被更新成老闆目前的交易ID=1。

(步驟6中小明已經commit了將香蕉設定為公開販售中，所以步驟8的UPDATE並不會造成blocking)

+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        50 |          1 |        1 | <--
|  2 | banana |        50 |          1 |        1 | <--
|  3 | cherry |         0 |          0 |        0 |
|  4 | guava  |         0 |          0 |        0 |
|  5 | peach  |        50 |          1 |        1 | <--
+----+--------+-----------+------------+----------+

步驟9，老闆在交易1內第三次下了SELECT語句，MySQL依照過往模式將先前建立過的ReadView拿出來加入判斷。只不過這次蘋果、香蕉跟桃子這三筆記錄都符合ReadView要讀出記錄的判斷1條件，這就是步驟9為什麼會發生幻讀的理由。

+----+--------+-----------+------------+----------+
| id | name   | remaining | is_enabled | (trx_id) |
+----+--------+-----------+------------+----------+
|  1 | apple  |        50 |          1 |        1 |
|  2 | banana |        50 |          1 |        1 |
|  5 | peach  |        50 |          1 |        1 |
+----+--------+-----------+------------+----------+
3 rows in set (0.00 sec)

結論

透過上面一步步的DB操作流程分解，我們了解了為什麼MySQL隔離層級設為REPEATABLE READ時還是會發生幻讀。至於為什麼UPDATE或DELETE也不使用ReadView加入判斷書中並沒有提到，不過我自己覺得可能是REPEATABLE READ顧名思義要防的是不可重複讀，至於其他情境因為規範沒有要求所以MySQL只做到規範的標準就好。如果有大神知道MySQL當初設計的哲學的話歡迎跟大家分享，我還滿想知道:)

最後還是要推薦一下《資料庫解剖學:從內部深解MySQL運作原理》這本書，從去年2021/07上市當月就買了，但因為這段時間工作及出差的緣故，一直到這週才把這本書看完。書中用敘述+圖表的方式介紹完MySQL底層的資料結構及運作原理，很適合對於想更深一層了解資料庫底層但又不想翻c++原始碼的人。

One Comment

發佈回覆給「Damon King」的留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *