事務與鎖#

事務就是要保證一組資料庫操作，要麼全部成功，要麼全部失敗。理解事務的隔離性和鎖機制，是掌握 MySQL 並行控制的關鍵。

事務的 ACID 特性#

隔離級別#

SQL 標準定義了四種事務隔離級別：

隔離級別示例#

假設資料表 T 中有一列資料 c=1：

事務 A                        事務 B
─────────────────────────────────────────
開始事務
查詢 c (V1)
                              開始事務
                              將 c 改為 2
查詢 c (V2)
                              提交
查詢 c (V3)
提交

Oracle 預設隔離級別是「讀提交」，MySQL 預設是「可重複讀」。從 Oracle 遷移到 MySQL 時要注意設定。

-- 查看當前隔離級別
SHOW VARIABLES LIKE 'transaction_isolation';

-- 設定隔離級別
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔離級別異常現象矩陣#

SQL 標準是用「允許出現哪些異常現象」來反向定義隔離級別的。先精確界定四種異常，再用矩陣對照四個級別，最後看 InnoDB 在「可重複讀」下如何用 Gap Lock 額外解決幻讀。

四種異常現象的定義與時序#

髒讀（Dirty Read）：讀到另一個事務「尚未提交」的修改，而該事務之後可能回滾——讀到的是根本不存在的資料。

sequenceDiagram
    participant A as 事務 A
    participant B as 事務 B
    B->>B: UPDATE account SET bal=200 WHERE id=1<br/>(未提交)
    A->>A: SELECT bal → 讀到 200 (髒值)
    B->>B: ROLLBACK
    Note over A,B: A 讀到的 200 從未真實存在

不可重複讀（Non-repeatable Read）：同一事務內，對同一列的兩次讀取得到不同值，因為中間有另一事務「修改該列並提交」。針對的是 UPDATE。

sequenceDiagram
    participant A as 事務 A
    participant B as 事務 B
    A->>A: SELECT bal WHERE id=1 → 100
    B->>B: UPDATE bal=200 WHERE id=1; COMMIT
    A->>A: SELECT bal WHERE id=1 → 200
    Note over A,B: 同一列前後讀到不同值

幻讀（Phantom Read）：同一事務內，兩次執行同一範圍查詢，第二次多出（或少了）若干列，因為另一事務在該範圍內「INSERT/DELETE 並提交」。針對的是列的數量（集合）變化。

sequenceDiagram
    participant A as 事務 A
    participant B as 事務 B
    A->>A: SELECT COUNT(*) WHERE age>30 → 5 列
    B->>B: INSERT 一筆 age=40; COMMIT
    A->>A: SELECT COUNT(*) WHERE age>30 → 6 列
    Note over A,B: 範圍內憑空多出一列「幻影」

序列化異常（Serialization Anomaly）：多個事務各自看似正確，但其「並行交錯」的結果無法對應到任何一種「依序逐一執行」的結果。例如兩事務各自讀總和後依規則寫入，交錯後破壞了原本應維持的全域不變式（如寫偏斜 write skew）。

前三種異常都針對「讀到了不該讀的東西」；序列化異常則更高一層——即使每次讀寫都合法，事務間的相對順序本身仍可能產生不一致。唯有串列化（Serializable）級別能完全杜絕。

四級別 × 異常 對照矩陣#

下表中 ✗ 表示該級別「可能發生」此異常，✓ 表示「保證不發生」：

注意「幻讀」這一格：SQL 標準允許可重複讀出現幻讀，但 InnoDB 的可重複讀透過 Gap Lock 在當前讀下消除了幻讀，這是 MySQL 與標準的著名差異。隔離級別越高並非越好——它以並行度與鎖開銷為代價，多數 OLTP 系統用讀提交或可重複讀即可。

可重現的 SQL 實驗#

開兩個 session 親自觀察不可重複讀（在讀提交下）：

-- 前置
CREATE TABLE acct(id INT PRIMARY KEY, bal INT);
INSERT INTO acct VALUES (1, 100);

-- Session A
SET SESSION transaction_isolation = 'READ-COMMITTED';
BEGIN;
SELECT bal FROM acct WHERE id = 1;   -- 100

-- Session B（此時切過去執行）
BEGIN;
UPDATE acct SET bal = 200 WHERE id = 1;
COMMIT;

-- Session A（回來再讀）
SELECT bal FROM acct WHERE id = 1;   -- 200 → 不可重複讀重現
COMMIT;

把 Session A 的隔離級別改為 REPEATABLE-READ 重跑，第二次仍讀到 100——一致性視圖（見下節 MVCC）讓快照讀凍結在事務開始的版本。

InnoDB 如何用 Gap Lock 解決幻讀#

幻讀的根源是「新插入的列」，而列鎖（Record Lock）只能鎖住已存在的列，鎖不住「尚未插入的縫隙」。InnoDB 在可重複讀下對當前讀（SELECT ... FOR UPDATE、UPDATE、DELETE，見前述當前讀小節）引入兩種鎖：

• Gap Lock（間隙鎖）：鎖住索引記錄之間的「開區間縫隙」，阻止其他事務在縫隙中 INSERT。
• Next-Key Lock（臨鍵鎖）：Record Lock + 其前方的 Gap Lock，鎖住「左開右閉」區間，這是 RR 下當前讀的預設加鎖單位。

sequenceDiagram
    participant A as 事務 A (RR)
    participant B as 事務 B
    A->>A: SELECT * WHERE age>30 FOR UPDATE<br/>(對 age>30 範圍加 next-key lock)
    B-->>B: INSERT age=40 → 阻塞！<br/>(目標落在被鎖縫隙中)
    A->>A: 再次 SELECT WHERE age>30<br/>結果不變，無幻影
    A->>A: COMMIT (釋放 gap lock)
    B->>B: INSERT 此時才放行

Gap Lock 只在可重複讀生效；讀提交級別會關閉大部分 Gap Lock（更少阻塞、但允許幻讀）。Gap Lock 之間是「相容」的——兩個事務可同時持有同一縫隙的 Gap Lock，卻都在等對方放手才能 INSERT，因此間隙鎖是死鎖的常見來源（見後文死鎖一節）。此外，快照讀（普通 SELECT）靠 MVCC 而非 Gap Lock 避免幻讀，兩條路徑要分清。

MVCC 實作原理#

InnoDB 透過 MVCC（多版本並行控制） 實作事務隔離。本節為完整講解；後續「05 高可用」章在討論複製延遲時會引用這裡的視角。

資料版本鏈#

每條記錄在更新時都會生成新版本，並記錄事務 ID：

flowchart LR
    subgraph VersionChain["資料版本鏈"]
        V1["當前版本<br/>k=22<br/>trx_id=102"] --> V2["undo log<br/>k=11<br/>trx_id=101"]
        V2 --> V3["undo log<br/>k=1<br/>trx_id=90"]
    end

    style V1 fill:#c8e6c9
    style V2 fill:#fff3e0
    style V3 fill:#e3f2fd

一致性視圖 (Read View)#

事務啟動時會建立一致性視圖，記錄當前活躍的事務 ID：

flowchart LR
    subgraph RV["一致性視圖 (Read View)"]
        direction LR
        A["已提交<br/>(可見)"] --- B["低水位"]
        B --- C["活躍事務陣列"]
        C --- D["高水位"]
        D --- E["未來事務<br/>(不可見)"]
    end

    style A fill:#c8e6c9
    style C fill:#fff3e0
    style E fill:#ffcdd2

活躍事務陣列中的版本不可見，不在陣列中的版本可見

可見性規則：

InnoDB 利用「所有資料都有多個版本」的特性，實作了「秒級建立快照」的能力。

當前讀 vs 一致性讀#

-- 一致性讀
SELECT * FROM t WHERE id = 1;

-- 當前讀
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;  -- 讀鎖
SELECT * FROM t WHERE id = 1 FOR UPDATE;          -- 寫鎖
UPDATE t SET k = k + 1 WHERE id = 1;              -- 隱式寫鎖

更新資料時都是先讀後寫，這個讀只能讀當前最新值（當前讀），否則會丟失其他事務的更新。

列鎖機制#

MySQL 的列鎖是在引擎層實作的，InnoDB 支援列鎖，MyISAM 不支援。

兩階段鎖協議#

在 InnoDB 事務中，列鎖是在需要的時候才加上的，但要等到事務結束時才釋放。

flowchart TD
    A["事務開始"] --> B["UPDATE ... WHERE id=1<br/>(加鎖 id=1)"]
    B --> C["UPDATE ... WHERE id=2<br/>(加鎖 id=2)"]
    C --> D["... 其他操作 ..."]
    D --> E["事務提交"]
    E --> F["釋放所有列鎖"]

    style A fill:#e3f2fd
    style E fill:#fff3e0
    style F fill:#c8e6c9

鎖順序最佳化#

根據兩階段鎖協議，把最可能造成鎖衝突的操作放在事務最後。

BEGIN;
INSERT INTO transaction_log ...;  -- 無衝突
UPDATE customer SET balance = balance - 100 WHERE id = 'A';  -- 低衝突
UPDATE theater SET balance = balance + 100 WHERE id = 'B';   -- 高衝突，放最後
COMMIT;

死鎖與死鎖檢測#

什麼是死鎖#

sequenceDiagram
    participant A as 事務 A
    participant B as 事務 B

    A->>A: UPDATE ... WHERE id=1<br/>(持有 id=1 的鎖)
    B->>B: UPDATE ... WHERE id=2<br/>(持有 id=2 的鎖)

    A--xB: UPDATE ... WHERE id=2<br/>(等待 id=2 的鎖)
    B--xA: UPDATE ... WHERE id=1<br/>(等待 id=1 的鎖)

    Note over A,B: 死鎖！相互等待

死鎖處理策略#

超時時間不能設太短（如 1s），否則會把正常的鎖等待也誤殺。正常情況下應該使用主動死鎖檢測。

死鎖檢測的效能問題#

死鎖檢測的時間複雜度是 $O(n)$，當 1000 個事務同時更新同一列時，檢測操作達到 100 萬量級。

表現：CPU 使用率很高，但每秒執行不了幾個事務。

解決熱點列更新的方法#

方法一：關閉死鎖檢測（不推薦）

• 風險大，可能導致大量超時

方法二：控制並行度

• 在中介軟體或資料庫伺服器端控制
• 相同列的更新在進入引擎前排隊

方法三：拆分熱點列

原來：影院帳戶 1 列
拆分後：影院帳戶 10 列，總額 = sum(各列)

每次加款：隨機選一列更新
衝突機率：降為 1/10

如果帳戶可能減少（如退票），需要特殊處理某列變成 0 的情況。

長事務的危害#

為什麼要避免長事務#

MySQL 5.5 及之前版本，回滾日誌放在 ibdata 檔案中，即使事務提交，檔案也不會變小。

如何避免長事務#

應用端

• 確認 autocommit=1，不要關閉自動提交
• 去掉不必要的唯讀事務（多個 SELECT 不需要事務包裹）
• 設定 MAX_EXECUTION_TIME 限制語句執行時間

資料庫端

• 監控 information_schema.innodb_trx 表
• 設定長事務閾值，超過就報警或 kill
• 使用 pt-kill 工具

-- 查找持續超過 60 秒的事務
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

事務啟動方式#

本章小結#

關鍵設定：autocommit = 1（預設開啟自動提交）、innodb_deadlock_detect = ON（開啟死鎖檢測）、監控長事務並及時處理。