事務與鎖#

事務就是要保證一組資料庫操作，要麼全部成功，要麼全部失敗。理解事務的隔離性和鎖機制，是掌握 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;

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（開啟死鎖檢測）、監控長事務並及時處理。