深入淺出MySQL事務

Photo by Lukas Hartmann from Pexels

辭職這段時間以來看見了很多工作之外的東西，我認為這是值得的。同時也有時間和機會來好好整理所學所想，準備開啟下一段旅途。

事務的定義及特性

資料庫事務代表在資料庫管理系統（或類似系統）中對資料庫進行的一致可靠的工作單元，獨立於其他事務的處理。事務通常表示對資料庫的任何更改。資料庫環境中的事務有兩個主要目的：

1. 提供可靠的工作單元，以便在系統故障的情況下能夠正確恢復並保持資料庫的一致性。例如：當執行突然中止（完全或部分）時，許多對資料庫的操作未完成，狀態不明確。
2. 提供並行存取資料庫的程式之間的隔離。如果不提供這種隔離性，程式的結果可能是錯誤的。

在資料庫管理系統中，事務是邏輯或工作的單個單位，有時由多個操作組成。在資料庫中以一致的方式執行的任何邏輯計算都被稱為事務。一個例子是從一個銀行賬戶轉賬到另一個賬戶：完整的事務需要從一個賬戶中減去要轉賬的金額，並將同樣的金額新增到另一個賬戶中。

根據定義，資料庫事務必須具有原子性（要麼完全完成，要麼沒有任何影響）、一致性（必須符合資料庫中的現有約束）、隔離性（不能影響其他事務）和永續性（必須寫入持久儲存媒介）。資料庫專業人員經常使用ACID這個縮寫來提及資料庫事務的這些特性。

MySQL中的事務支援

MySQL是一個流行的關係型資料庫管理系統，它支援多種資料庫引擎，其中InnoDB是最常用的支援事務的引擎。MySQL提供了豐富的事務控制語句和隔離級別，讓使用者可以靈活地管理事務的開始、提交、回滾、巢狀等行為，並調整事務對並行性和資料一致性的影響。MySQL還提供了事務紀錄檔、鎖機制、隱式和顯式鎖定等功能，來保證事務的永續性、並行控制和效能優化。在本文中，我們將詳細介紹和分析MySQL中的事務相關的知識和技巧。

事務的ACID屬性

原子性（Atomicity）
事務通常由多個語句組成。原子性保證每個事務都被視為一個「單元」，它要麼完全成功，要麼完全失敗：如果構成事務的任何語句未能完成，則整個事務失敗，資料庫保持不變。原子系統必須保證每種情況下的原子性，包括電源故障、錯誤和崩潰。[5] 原子性的保證可以防止對資料庫的更新僅部分發生，這可能會導致比完全拒絕整個系列更大的問題。因此，另一個資料庫使用者端無法觀察到該事務正在進行中。在某一時刻，它還沒有發生，而在下一個時刻，它已經全部發生了（或者如果交易在進行中被取消，則什麼也沒發生）。

一致性（Consistency）
一致性確保事務只能將資料庫從一個一致狀態帶到另一個一致狀態，保持資料庫不變性：寫入資料庫的任何資料都必須根據所有定義的規則有效，包括約束、級聯、觸發器及其任意組合。這可以防止非法事務損壞資料庫。參照完整性保證主鍵-外來鍵關係。[6]

隔離性（Isolation）
事務通常同時執行（例如，多個事務同時讀取和寫入表）。隔離可確保事務的並行執行使資料庫處於按順序執行事務時獲得的相同狀態。隔離是並行控制的主要目標;根據所使用的隔離級別，不完整事務的影響可能對其他事務不可見。[注7]

永續性（Durability）
永續性保證一旦事務提交，即使在系統故障（例如，斷電或崩潰）的情況下，事務也會保持提交狀態。這通常意味著已完成的交易（或其影響）記錄在非易失性記憶體中。[需要參照]

事務隔離級別

隔離級別（Isolation Level）是一種用於在資料庫中控制事務之間的可見性和並行性的設定，它決定了一個事務可以看到其他事務對資料所做的修改的程度。隔離級別越高，資料的一致性和可重複性就越好，但是並行效能就越低；反之亦然。

SQL標準定義了四種隔離級別(https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html)：

• 讀未提交（Read Uncommitted）：最低的隔離級別，一個事務可以看到其他事務未提交的修改，可能導致髒讀（Dirty Read）、不可重複讀（Non-repeatable Read）和幻讀（Phantom Read）等問題
• 讀已提交（Read Committed）：一個事務只能看到其他事務已經提交的修改，可以避免髒讀，但是可能導致不可重複讀和幻讀等問題
• 可重複讀（Repeatable Read）：一個事務在開始時建立一個資料快照，並且在整個事務期間保持不變，可以避免髒讀和不可重複讀，但是可能導致幻讀等問題
• 可序列化（Serializable）：最高的隔離級別，一個事務在執行期間對其他事務不可見，並且對資料進行加鎖，可以避免所有的並行問題，但是並行效能最低

MySQL支援使用SET TRANSACTION語句來設定當前對談或者全域性的隔離級別。

例如：

-- 設定當前對談的隔離級別為可重複讀
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 設定全域性的隔離級別為讀已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

各個隔離級別下的事務並行問題及解決方案

這裡從分析問題和學習的角度出發，簡單的列舉了各個隔離級別下的事務並行問題，但是其實在實際開發中，基本不用考慮這些，大部分的時候都預設為可重複讀級別，因為隔離級別低了資料不安全，高了效能保證不了。

如果想要兼顧安全和效能，那應該從業務架構層面尋找優化點，不到萬不得已沒必要試圖死磕MySQL，所以如果大致理解了隔離級別就可以跳過本節。

讀未提交（Read Uncommitted）

讀未提交（Read Uncommitted）是最低的隔離級別，一個事務可以看到其他事務未提交的修改，可能導致以下並行問題：

• 髒讀（Dirty Read）：一個事務讀取到另一個事務未提交的資料，然後另一個事務回滾了，導致前一個事務讀取到了無效的資料
• 不可重複讀（Non-repeatable Read）：在同一個事務中，兩次讀取同一條記錄得到了不同的結果，因為其他事務在此期間修改或刪除了該記錄
• 幻讀（Phantom Read）：在同一個事務中，兩次讀取同一範圍的記錄集合得到了不同的結果，因為其他事務在此期間插入或刪除了記錄

下面用一個例子來說明：

假設有以下表：

create table user (
  id int primary key,
  name varchar(20),
  age int
) engine=innodb;

insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);

假設有兩個並行事務T1和T2，它們都使用讀未提交（Read Uncommitted）隔離級別，並且執行以下操作：

從上面的例子可以看到，T1和T2在讀未提交隔離級別下，會發生髒讀、不可重複讀和幻讀等並行問題，導致資料的不一致性和不可靠性。

解決方案：

• 不要使用讀未提交隔離級別，除非你對資料的一致性和可靠性沒有任何要求
• 使用更高的隔離級別，如讀已提交、可重複讀或者可序列化，來避免髒讀、不可重複讀或者幻讀等問題
• 使用鎖機制，如表級鎖或者行級鎖，來控制對資料的並行存取和修改

讀已提交（Read Committed）

讀已提交（Read Committed）是一種隔離級別，一個事務只能看到其他事務已經提交的修改，可以避免髒讀，但是可能導致以下並行問題：

• 不可重複讀（Non-repeatable Read）：在同一個事務中，兩次讀取同一條記錄得到了不同的結果，因為其他事務在此期間修改或刪除了該記錄
• 幻讀（Phantom Read）：在同一個事務中，兩次讀取同一範圍的記錄集合得到了不同的結果，因為其他事務在此期間插入或刪除了記錄

下面用一個例子來說明：

假設有以下表：

create table user (
  id int primary key,
  name varchar(20),
  age int
) engine=innodb;

insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);

假設有兩個並行事務T1和T2，它們都使用讀已提交（Read Committed）隔離級別，並且執行以下操作：

從上面的例子可以看到，T1和T2在讀已提交隔離級別下，會發生不可重複讀和幻讀等並行問題，導致資料的不一致性和不可靠性。

解決方案：

• 使用更高的隔離級別，如可重複讀或者可序列化，來避免不可重複讀或者幻讀等問題
• 使用鎖機制，如表級鎖或者行級鎖，來控制對資料的並行存取和修改

可重複讀（Repeatable Read）

可重複讀（Repeatable Read）是一種隔離級別，一個事務在開始時建立一個資料快照，並且在整個事務期間保持不變，可以避免髒讀和不可重複讀，但是可能導致以下並行問題：

• 幻讀（Phantom Read）：在同一個事務中，兩次讀取同一範圍的記錄集合得到了不同的結果，因為其他事務在此期間插入或刪除了記錄

注意：網上有部分文章說MySQL中的可重複讀利用MVCC解決了幻讀的問題，其實並沒有完全解決，MVCC並不能完全消除幻讀的可能性。
具體分析可以看本文的MVCC章節，這裡不重複說明。

解決方案：

• 使用更高的隔離級別，如可序列化，來避免幻讀等問題
• 使用鎖機制，如表級鎖或者行級鎖，來控制對資料的並行存取和修改

可序列化（Serializable）

可序列化（Serializable）是最高的隔離級別，一個事務在執行期間對其他事務不可見，並且對資料進行加鎖，並行效能最低，可以避免所有的並行問題，所以這裡就無需舉例了。

事務的基本操作

在MySQL中，我們可以通過一些簡單的命令來操作事務，如：

1. 開始一個事務：使用START TRANSACTION語句或BEGIN語句來開始一個事務。事務開始後，MySQL將自動將後續的操作視為一個事務。
2. 提交一個事務：使用COMMIT語句來提交一個事務。提交操作將永久儲存對資料庫的更改，並結束當前的事務。
3. 回滾一個事務：使用ROLLBACK語句來回滾一個事務。回滾操作將取消對資料庫的更改，並復原當前事務中的所有操作。

以下是一個範例，展示瞭如何使用這些語句來操作一個事務：

-- 開始事務
START TRANSACTION;

-- 執行一系列的SQL操作
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;

-- 判斷是否滿足某些條件
IF condition THEN
    -- 執行其他操作
    INSERT INTO table_name (column1, column2) VALUES (value1, value2);
ELSE
    -- 回滾事務
    ROLLBACK;
END IF;

-- 提交事務
COMMIT;

MySQL預設使用自動提交模式（Auto-Commit Mode），即每個SQL語句都被視為一個單獨的事務並自動提交。如果要使用顯式事務控制，需要在執行任何DML操作之前顯式地開始一個事務，並在適當的時候選擇提交或回滾事務。

事務的巢狀

MySQL允許在一個事務中巢狀其他事務，即在一個事務內部啟動另一個事務。這樣的巢狀事務可以通過SAVEPOINT和ROLLBACK TO SAVEPOINT語句進行控制。巢狀事務的主要目的是在更細粒度的操作中實現事務的管理和控制。

儲存點是在事務中設定的一個標記，用於標識事務中的一個特定位置。通過設定儲存點，您可以在事務進行過程中建立一個可以回滾到該點的標記，以便在發生錯誤或其他情況時進行回滾操作。

以下是一個範例，展示了事務的巢狀：

START TRANSACTION;

-- 執行一些操作

SAVEPOINT savepoint1;

-- 執行更細粒度的操作

SAVEPOINT savepoint2;

-- 執行更細粒度的操作

ROLLBACK TO SAVEPOINT savepoint1;

-- 回滾到savepoint1，取消savepoint2後的操作

COMMIT;

在上述範例中，我們在一個事務中建立了兩個儲存點（savepoint1和savepoint2），並在這些儲存點之間執行了一些更細粒度的操作。如果需要回滾操作，可以選擇回滾到特定的儲存點，而不是完全回滾整個事務。

事務紀錄檔

事務紀錄檔是MySQL資料庫的重要組成部分，它們記錄了資料庫中資料的變化情況，保證了事務的永續性和一致性。

事務紀錄檔對事務永續性的重要性是顯而易見的。如果沒有事務紀錄檔，那麼當系統發生故障時，記憶體中的資料頁就會丟失，而磁碟上的資料頁可能還沒有被更新，這樣就會導致資料不一致和丟失。而有了事務紀錄檔，就可以在系統恢復後，根據紀錄檔中的記錄將資料頁恢復到最近一次提交的狀態，保證了資料的完整性和一致性。

Redo Log

Redo Log是重做紀錄檔，屬於InnoDB儲存引擎的紀錄檔。它是物理紀錄檔，記錄了資料頁的更改情況，比如向資料頁增加或刪除一條記錄。Redo Log包括兩部分：一個是記憶體中的紀錄檔緩衝區（Redo Log Buffer），另一個是磁碟上的紀錄檔檔案（Redo Log File）。Redo Log採用固定大小，迴圈寫入的方式，當寫到結尾時，會回到開頭繼續寫。

Redo Log的主要作用有兩個：

• 前滾操作：具備crash-safe能力，提供斷電重啟時解決事務丟失資料問題。當資料庫發生異常崩潰時，可以根據Redo Log恢復資料到最近一次提交的狀態。
• 提高效能：先寫Redo Log記錄更新。當等到有空閒執行緒、記憶體不足、Redo Log滿了時刷髒。寫Redo Log是順序寫入，刷髒是隨機寫，節省了隨機寫磁碟的IO消耗（轉成順序寫），所以效能得到提升。這種先寫紀錄檔，再寫磁碟的技術就是WAL（Write-Ahead Logging）技術。

Redo Log的工作原理如下：

• 當InnoDB執行一條DML語句時（比如INSERT、UPDATE、DELETE），首先會將該語句對應的記錄寫入Redo Log Buffer中。
• 當事務提交時（或者達到刷盤時機），會將Redo Log Buffer中的記錄批次寫入OS Buffer中，並呼叫fsync()函數將其刷入Redo Log File中。
• 當InnoDB需要將記憶體中修改過的資料頁重新整理到磁碟時（比如記憶體不足、系統空閒、檢查點觸發等），會先檢查該資料頁對應的LSN（邏輯序列號）是否小於Redo Log File中已經刷入磁碟的LSN。如果小於，則說明該資料頁已經被Redo Log保護了，可以直接重新整理；如果大於，則說明該資料頁還沒有被Redo Log保護，需要先將Redo Log Buffer中該資料頁相關的記錄刷入磁碟。
• 當資料庫發生異常崩潰時，會導致記憶體中的資料頁丟失，此時需要根據Redo Log File中的記錄進行恢復。恢復的過程是從最近一個檢查點開始，掃描Redo Log File中的記錄，將已經提交的事務對應的記錄重做到資料頁上，將未提交的事務對應的記錄忽略。這樣就可以將資料頁恢復到最近一次提交的狀態。

Redo Log的工作原理可以用以下mermaid流程圖表示：

Undo Log

Undo Log是回滾紀錄檔，屬於InnoDB儲存引擎的紀錄檔。它是邏輯紀錄檔，記錄了資料頁的逆向操作，比如一條修改+3的語句，Undo Log會記錄對應一條-3的語句，一條插入語句則會記錄一條刪除語句。這樣發生錯誤時，根據執行Undo Log就可以回滾到事務之前的資料狀態。

Undo Log的主要作用有兩個：

• 回滾資料：當程式發生異常錯誤時等，根據執行Undo Log就可以回滾到事務之前的資料狀態，保證原子性，要麼成功要麼失敗。
• MVCC一致性檢視：通過Undo Log找到對應的資料版本號，是保證MVCC檢視的一致性的必要條件。

Undo Log的工作原理如下：

• 當InnoDB執行一條DML語句時（比如INSERT、UPDATE、DELETE），首先會將該語句對應的逆向操作記錄寫入Undo Log中。Undo Log是儲存在回滾段（Rollback Segment）中的，回滾段是InnoDB儲存引擎的一個特殊區域，它包含了多個回滾段槽（Rollback Slot），每個回滾段槽又包含了多個回滾指標（Roll Pointer），每個回滾指標指向一個Undo Log。
• 當事務需要回滾時（比如執行ROLLBACK語句或者發生異常錯誤），會根據Undo Log中的記錄逐條執行逆向操作，將資料頁恢復到事務之前的狀態。
• 當事務提交時（或者達到清理時機），會將Undo Log中的記錄標記為可清理，並釋放佔用的空間。清理時機有兩種：一種是當事務提交後，如果該事務沒有影響其他事務的MVCC檢視，則可以立即清理；另一種是當事務提交後，如果該事務影響了其他事務的MVCC檢視，則需要等待所有依賴該事務的MVCC檢視消失後才能清理。
• 當資料庫發生異常崩潰時，會導致部分未提交或未清理的Undo Log殘留在回滾段中，此時需要根據Redo Log File中的記錄進行恢復。恢復的過程是從最近一個檢查點開始，掃描Redo Log File中的記錄，將已經提交但未清理的Undo Log標記為可清理，並釋放佔用的空間；將未提交但已寫入Undo Log的事務回滾，並釋放佔用的空間。

Undo Log的工作原理可以用以下mermaid流程圖表示：

並行控制與鎖

在MySQL中，有幾種常見的並行控制方式用於處理多個並行事務之間的資料存取衝突。下面是一些常用的並行控制方式：

1. 鎖定（Locking）：MySQL使用鎖機制來確保在某個事務修改資料時其他事務不能同時存取或修改同一資料。鎖可以分為共用鎖（Shared Lock）和排他鎖（Exclusive Lock），分別用於讀取和修改操作。通過鎖定機制，MySQL可以保證資料的一致性和並行性，但可能會導致鎖衝突和效能問題。
2. 樂觀並行控制（Optimistic Concurrency Control）：樂觀並行控制假設事務之間的衝突很少發生，並且在提交之前不進行實際的資料鎖定。每個事務在提交之前會檢查其他事務是否已修改了它要修改的資料，如果檢測到衝突，則會回滾事務並重試。樂觀並行控制通常使用版本號或時間戳來跟蹤資料的變化。
3. 多版本並行控制（Multi-Version Concurrency Control，MVCC）：MVCC通過為每個事務建立獨立的資料版本來實現並行控制。每個事務都可以在自己的資料版本上進行讀取和修改，而不會與其他事務產生衝突。MVCC使用讀檢視（Read View）來確定每個事務可以看到的資料版本，從而提供了一致的讀取和隔離性。
4. 時間戳排序（Timestamp Ordering）：時間戳排序是一種基於時間戳的並行控制方法，通過為每個事務分配唯一的時間戳來確定事務的執行順序。根據時間戳的順序，事務按照先後順序進行提交，從而避免了衝突和死鎖。

這些並行控制方式在MySQL中都有廣泛的應用，並根據不同的場景和需求進行選擇和設定。每種方式都有其優缺點，具體使用哪種方式取決於應用程式的特性和效能需求。

MySQL中的鎖機制

鎖（Lock）是一種用於在資料庫中控制並行的機制，通過對錶或者行進行加鎖，可以限制對資料的非法存取或者修改。MySQL允許使用者端對談顯式地獲取表鎖，以便與其他對談共同作業存取表中的資料。

MySQL中的鎖主要分為兩種：

• 表級鎖（Table-level Lock）：對整個表進行加鎖，可以是寫鎖或者讀鎖
• 行級鎖（Row-level Lock）：對錶中的單個行或者多個行進行加鎖，可以是共用鎖或者排他鎖

不同的儲存引擎使用不同的鎖機制，例如：

• MyISAM, MEMORY, MERGE等儲存引擎使用表級鎖
• InnoDB, NDB等儲存引擎使用行級鎖
• Archive儲存引擎使用插入鎖（Insert Lock），一種特殊的寫鎖

表級鎖

表級鎖是一種最簡單的鎖機制，它對整個表進行加鎖，只允許一個對談在同一時間對錶進行更新操作。這種鎖機制使得這些儲存引擎更適合於唯讀、讀多寫少或者單使用者的應用場景。

這些儲存引擎通過總是一次性請求所有需要的鎖，並且總是按照相同的順序對錶進行加鎖來避免死鎖。這種策略的代價是降低了並行性；其他想要修改表的對談必須等待當前的資料修改語句完成。

表級鎖有以下優點：

• 相對佔用較少的記憶體（行級鎖需要為每行或者每組行佔用記憶體）
• 當對錶的大部分資料進行操作時比較快，因為只涉及到一個單一的鎖
• 當經常對大部分資料進行GROUP BY操作或者需要頻繁掃描整個表時比較快

MySQL按照以下規則給表分配寫鎖：

• 如果表上沒有任何鎖，就給它加上寫鎖
• 如果表上有讀鎖，就等待所有讀操作完成後再給它加上寫鎖
• 如果表上有寫鎖，就等待所有寫操作完成後再給它加上寫鎖

MySQL按照以下規則給表分配讀鎖：

• 如果表上沒有任何寫操作或者寫請求，就給它加上讀鎖
• 如果表上有寫操作或者寫請求，就等待所有寫操作完成後再給它加上讀鎖

可以使用LOCK TABLES語句顯式地給一個或多個表加上讀或者寫的表級別的排他性質的共用模式（S）或者排他模式（X）。

例如：

-- 給user表加上讀模式的共用模式（S）的排他性質的共用模式（S）的排他性質的共用模式（S）的排他性質的共用模式（S）的排他性質的共用模式（S）的排他性質
LOCK TABLE user READ;

-- 給user和order兩個表分別加上不同模式的排他性質
LOCK TABLE user WRITE, order READ;

-- 釋放所有已經獲取到的表級別排他性
UNLOCK TABLES;

表級鎖有以下缺點：

• 並行效能低：表級鎖會阻塞其他對談對同一張表的讀寫操作，導致並行效能降低
• 死鎖風險高：表級鎖如果不按照相同的順序對錶進行加鎖，就可能導致死鎖的發生
• 無法解決幻讀（Phantom Read）問題：表級鎖只能保證在同一個事務中，多次讀取同一條記錄得到的結果是一致的，但不能保證在同一個事務中，兩次讀取同一範圍的記錄集合是一致的，因為可能有其他事務在此期間插入或刪除了記錄，這就導致了幻讀的問題

行級鎖

行級鎖是一種更細粒度的鎖機制，它對錶中的單個行或者多個行進行加鎖，可以提高並行效能，減少死鎖風險，解決幻讀問題。MySQL中使用InnoDB儲存引擎，並且將隔離級別設定為可重複讀（Repeatable Read）或者讀已提交（Read Committed）時，預設使用行級鎖。

行級鎖主要分為兩種：

• 共用鎖（Shared Lock）：又稱為讀鎖（Read Lock），允許多個事務同時對同一行資料進行讀取操作，但不允許其他事務對該行資料進行寫入操作
• 排他鎖（Exclusive Lock）：又稱為寫鎖（Write Lock），只允許一個事務對同一行資料進行寫入操作，同時不允許其他事務對該行資料進行讀取或者寫入操作

MySQL中使用以下語句可以顯式地給一個或多個行加上共用鎖或者排他鎖：

-- 給user表中id為1的行加上共用鎖
select * from user where id = 1 lock in share mode;

-- 給user表中id為2的行加上排他鎖
select * from user where id = 2 for update;

MySQL中使用以下規則來分配共用鎖和排他鎖：

• 如果一個事務要獲取共用鎖，並且該行沒有被其他事務持有排他鎖，則獲取成功
• 如果一個事務要獲取排他鎖，並且該行沒有被其他事務持有任何型別的鎖，則獲取成功
• 否則，獲取失敗，並且根據不同的隔離級別採取不同的策略：
  • 如果隔離級別為可重複讀（Repeatable Read），則等待直到獲取成功或者超時
  • 如果隔離級別為讀已提交（Read Committed），則返回最新已提交的資料版本

下面用一個例子來說明：

假設有以下表：

create table user (
  id int primary key,
  name varchar(20),
  age int
) engine=innodb;

insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);

假設有兩個並行事務T1和T2，它們都使用可重複讀（Repeatable Read）隔離級別，並且執行以下操作：

從上面的例子可以看到，T1和T2在可重複讀隔離級別下，使用行級鎖來保證資料的一致性和可重複性。當它們對同一行資料進行讀或者寫操作時，就會發生鎖衝突，導致其中一個事務被阻塞，直到另一個事務釋放了鎖。這樣可以避免不可重複讀和幻讀的問題，但是也會降低並行效能和增加死鎖風險。

多版本並行控制（MVCC）

MVCC（Multi-Version Concurrency Control）是一種用來在資料庫中控制並行的方法，實現對資料庫的並行存取，在程式語言中實現事務記憶體。

MVCC在MySQL中，主要是為了提高資料庫並行效能，用更好的方式去處理讀-寫衝突，做到即使有讀寫衝突時，也能做到不加鎖，非阻塞並行讀。

MVCC通過維護多個資料版本和使用事務識別符號和時間戳來實現並行控制。它能夠提供高並行性和讀一致性，避免了讀寫衝突和資料不一致的問題。每個事務都可以獲得一個獨立的一致性檢視，從而實現了更高的並行性和隔離性。

MVCC有以下優點：

• 提高並行效能：MVCC允許多個讀操作同時進行，而不需要等待寫操作完成，從而提高了資料庫的吞吐量。
• 減少鎖競爭：MVCC避免了讀寫之間以及讀讀之間產生鎖衝突，從而降低了死鎖和超時發生的概率。
• 簡化程式設計模型：MVCC保證了每個事務都能讀取到一致性的資料快照片，從而簡化了程式設計模型和事務管理。

MVCC也有以下缺點：

• 佔用更多的儲存空間：MVCC需要為每個記錄版本儲存額外的資訊，如DB_TRX_ID, DB_ROLL_PTR等，這會增加資料庫的儲存開銷。
• 增加了GC（Garbage Collection）的複雜度：MVCC需要定期清理過期的記錄版本，這需要額外的執行緒和演演算法來完成，這會增加資料庫的執行負擔。
• 可能導致幻讀（Phantom Read）：MVCC只能保證讀取到的記錄版本是一致的，但不能保證在同一個事務中，兩次讀取的記錄集合是一致的，因為可能有其他事務在此期間插入或刪除了記錄，這就導致了幻讀的問題。

MVCC的原理主要是依賴記錄中的三個隱式欄位、undo紀錄檔、Read View來實現的。

隱式欄位

每行記錄除了我們自定義的欄位外，還有資料庫隱式定義的DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID等欄位。

• DB_TRX_ID：6 byte，最近修改（修改/插入）事務ID：記錄建立這條記錄/最後一次修改該記錄的事務ID
• DB_ROLL_PTR：7 byte，回滾指標，指向這條記錄的上一個版本（儲存於rollback segment裡）
• DB_ROW_ID：6 byte，隱含的自增ID（隱藏主鍵），如果資料表沒有主鍵，InnoDB會自動以DB_ROW_ID產生一個聚簇索引

實際還有一個刪除flag隱藏欄位, 既記錄被更新或刪除並不代表真的刪除，而是刪除flag變了。

例如：

這是一個初始狀態的記錄，name為Smith, age為24歲，隱式主鍵是1，事務ID和回滾指標我們假設為NULL。

undo紀錄檔

undo log主要分為兩種：

• insert undo log：代表事務在insert新記錄時產生的undo log, 只在事務回滾時需要，並且在事務提交後可以被立即丟棄
• update undo log：事務在進行update或delete時產生的undo log; 不僅在事務回滾時需要，在快照讀時也需要；所以不能隨便刪除，只有在快速讀或事務回滾不涉及該紀錄檔時，對應的紀錄檔才會被purge執行緒統一清除

purge從前面的分析可以看出，為了實現InnoDB的MVCC機制，更新或者刪除操作都只是設定一下老記錄的deleted_bit，並不真正將過時的記錄刪除。

為了節省磁碟空間，InnoDB有專門的purge執行緒來清理deleted_bit為true的記錄。

為了不影響MVCC的正常工作，purge執行緒自己也維護了一個read view（這個read view相當於系統中最老活躍事務的read view）;如果某個記錄的deleted_bit為true，並且DB_TRX_ID相對於purge執行緒的read view可見，那麼這條記錄一定是可以被安全清除的。

對MVCC有幫助的實質是update undo log ，undo log 實際上就是存在rollback segment中舊記錄鏈。

例如：

假設事務2執行了以下語句：

update user set age = 25 where name = 'Smith';

那麼會產生以下變化：

可以看到，原來的記錄被標記為刪除，並且DB_ROLL_PTR指向了新的記錄版本，而新的記錄版本的DB_TRX_ID為2，表示是由事務2修改的。

這樣，如果有其他事務要讀取這條記錄，就可以根據自己的Read View來判斷應該讀取哪個版本。

Read View

Read View是InnoDB實現MVCC的核心資料結構，用於提供在給定時間點上的一致性檢視，並儲存當前事務可見的其他活躍事務ID列表。

在MVCC中，每個事務都可以看到一個特定的資料庫狀態，該狀態是在事務開始時確定的。ReadView就是為了幫助事務獲取這個一致性檢視而存在的。

Read View主要包含以下幾個屬性：

• creator_trx_id：建立該Read View的事務ID
• low_limit_id：系統中已經建立過的最大事務ID+1
• up_limit_id：系統中活躍事務中最小的事務ID
• trx_ids：系統中活躍且大於up_limit_id小於low_limit_id的事務ID列表

Read View用來判斷一個記錄版本是否對當前事務可見，具體規則如下：

• 如果記錄版本的DB_TRX_ID等於creator_trx_id，則可見
• 如果記錄版本的DB_TRX_ID大於或等於low_limit_id，則不可見
• 如果記錄版本的DB_TRX_ID小於up_limit_id，則可見
• 如果記錄版本的DB_TRX_ID在trx_ids列表中，則不可見
• 否則，可見

例如：

假設系統中有四個活躍事務，分別為T1, T2, T3, T4, 它們的ID分別為1, 2, 3, 4。假設T4建立了一個Read View，那麼它的屬性如下：

• creator_trx_id：4
• low_limit_id：5
• up_limit_id：1
• trx_ids：[2, 3]

假設有以下記錄版本：

那麼對於T4來說，它能看到的記錄版本是：

因為只有DB_TRX_ID為4的記錄版本滿足了Read View的可見規則。其他版本要麼太新（DB_TRX_ID >= low_limit_id），要麼太舊（DB_TRX_ID < up_limit_id），要麼正在被其他活躍事務修改（DB_TRX_ID in trx_ids）。

讀已提交級別中的執行流程

讀已提交（Read Committed）級別保證了一個事務只能讀取到已經提交的資料，而不能讀取到未提交或回滾的資料。這樣可以避免髒讀（Dirty Read）的問題，即一個事務讀取到另一個事務未提交的資料，然後另一個事務回滾了，導致前一個事務讀取到了無效的資料。

在MySQL中，如果使用InnoDB儲存引擎，並且將隔離級別設定為讀已提交（Read Committed），那麼MVCC的工作方式如下：

• 每個事務在執行每個SQL語句之前都會生成一個新的Read View，這樣可以保證每個語句都能讀取到最新的已提交資料。
• 每個事務在執行插入、更新或刪除操作時，都會為被修改的記錄生成一個新的版本，並且將DB_TRX_ID設定為當前事務的ID，同時將DB_ROLL_PTR指向上一個版本。
• 每個事務在執行查詢操作時，都會根據自己的Read View來判斷哪些記錄版本對自己可見，具體規則如下：
  • 如果記錄版本的DB_TRX_ID等於當前事務的ID，則可見
  • 如果記錄版本的DB_TRX_ID大於或等於當前事務生成Read View時的系統最大事務ID+1，則不可見
  • 如果記錄版本的DB_TRX_ID小於當前事務生成Read View時的系統最小活躍事務ID，則可見
  • 如果記錄版本的DB_TRX_ID在當前事務生成Read View時的系統活躍事務ID列表中，則不可見
  • 否則，可見

下面用一個例子來說明：

假設有以下表：

create table user (
  id int primary key,
  name varchar(20),
  age int
) engine=innodb;

insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);

假設有兩個並行事務T1和T2，它們都使用讀已提交（Read Committed）隔離級別，並且執行以下操作：

從上面的例子可以看到，T1在讀已提交隔離級別下，每次執行查詢語句都會生成一個新的Read View，因此它能讀取到T2提交後的資料變化。這樣可以保證資料的一致性，但是也可能導致不可重複讀（Non-repeatable Read）的問題，即在同一個事務中，兩次讀取同一條記錄得到了不同的結果。

可重複讀級別中的執行流程

可重複讀（Repeatable Read）保證了在同一個事務中，多次讀取同一條記錄得到的結果是一致的，而不會受到其他事務的影響。這樣可以避免不可重複讀（Non-repeatable Read）的問題，即在同一個事務中，兩次讀取同一條記錄得到了不同的結果。
需要注意的是，在MySQL中，MVCC（多版本並行控制）確實可以幫助解決可重複讀（REPEATABLE READ）級別下的幻讀問題，但並非徹底解決。MVCC使用了間隙鎖（Gap Locks）來避免幻讀。然而，MVCC並不能完全消除幻讀的可能性。在可重複讀級別下，如果一個事務在讀取資料之後，另一個事務插入了滿足原始查詢條件的新行，那麼第一個事務再次執行相同的查詢時，就會發現新增了一個幻讀行。
在MySQL中，如果使用InnoDB儲存引擎，並且將隔離級別設定為可重複讀（Repeatable Read），那麼MVCC的工作方式如下：

• 每個事務在開始時生成一個Read View，並且在整個事務期間保持不變，這樣可以保證每次查詢都能讀取到相同的資料快照。
• 每個事務在執行插入、更新或刪除操作時，都會為被修改的記錄生成一個新的版本，並且將DB_TRX_ID設定為當前事務的ID，同時將DB_ROLL_PTR指向上一個版本。
• 每個事務在執行查詢操作時，都會根據自己的Read View來判斷哪些記錄版本對自己可見，具體規則如下：
  • 如果記錄版本的DB_TRX_ID等於當前事務的ID，則可見
  • 如果記錄版本的DB_TRX_ID大於或等於當前事務生成Read View時的系統最大事務ID+1，則不可見
  • 如果記錄版本的DB_TRX_ID小於當前事務生成Read View時的系統最小活躍事務ID，則可見
  • 如果記錄版本的DB_TRX_ID在當前事務生成Read View時的系統活躍事務ID列表中，則不可見
  • 否則，可見

下面舉例來說明，

範例1

我們來詳細描述一個場景，展示MVCC如何解決幻讀問題。
假設在資料庫中有一個表格orders，其中包含訂單資訊，有一個唯一索引列order_id。在事務開始之前，資料庫中存在以下資料：

orders 表：
---------------------------------
| order_id |   customer_name    |
---------------------------------
|    1     |    John Doe        |
|    2     |    Jane Smith      |
|    3     |    Alice Johnson   |
---------------------------------

現在，我們來描述一個事務執行的過程：

1. 事務A開始時，系統為該事務分配一個唯一的事務識別符號（Transaction ID）。同時，事務A建立一個空的ReadView。
2. 事務A執行一個查詢語句：SELECT * FROM orders WHERE order_id >= 2;。這個查詢使用事務A的ReadView來確定可見的資料版本。
3. 事務A的ReadView會根據事務A開始時的時間戳和資料庫中的事務版本資訊來確定可見的資料。在這個例子中，事務A開始時的時間戳為T1，所以它只能看到T1之前已經存在的資料。
4. 事務A完成查詢後，它返回的結果是order_id >= 2的資料行。由於事務A的ReadView只能看到T1之前的資料，所以它只能返回order_id = 2和order_id = 3的資料行。
5. 最後，事務A提交併將修改持久化到資料庫。

在事務A執行期間，其他事務可能會對資料庫進行一些操作，例如插入新的訂單或刪除現有的訂單。然而，由於事務A的ReadView只能看到T1之前的資料，所以它不會受到其他事務的影響。即使其他事務在事務A執行期間插入了order_id = 1的新訂單，事務A也不會看到該新訂單，從而避免了幻讀的問題。
通過MVCC的機制，事務A在可重複讀級別下可以獲得一致性的資料檢視，並避免了幻讀的情況。

範例2

然後是可能出現幻讀的場景，假設有以下表：

create table user (
  id int primary key,
  name varchar(20),
  age int
) engine=innodb;

insert into user values (1, 'Alice', 20), (2, 'Bob', 21), (3, 'Charlie', 22);

假設有兩個並行事務T1和T2，它們都使用可重複讀（Repeatable Read）隔離級別，並且執行以下操作：

從上面的例子可以看到，T1在可重複讀隔離級別下，整個事務期間使用同一個Read View，因此它不能讀取到T2提交後的資料變化。這樣可以保證資料的可重複性，但是也可能導致幻讀（Phantom Read）的問題，即在同一個事務中，兩次讀取同一範圍的記錄集合得到了不同的結果。