MySQL 鎖機制與競爭

主題：死鎖與避免方法

---

簡介

在多人同時存取資料庫的環境裡，**鎖定（Lock）**是保證資料一致性的關鍵機制。當多個交易（Transaction）同時對同一筆或多筆資料加鎖時，若取得鎖的順序不一致，就可能產生 死鎖（Deadlock）。
死鎖不僅會導致交易被系統自動回滾（Rollback），還會影響整體效能，甚至造成使用者操作失敗。了解死鎖的產生原理、偵測方式以及避免策略，是每位 MySQL 開發者必備的基礎功。

本篇文章將以 淺顯易懂 的方式說明 MySQL 中的死鎖概念，提供實作範例、常見陷阱與最佳實踐，幫助你在開發與維運階段有效降低死鎖風險。

---

核心概念

1. 鎖的類型

鎖類型	說明	典型使用情境
共享鎖 (S‑lock / READ LOCK)	允許多個交易同時讀取，但阻止寫入	SELECT ... LOCK IN SHARE MODE
排他鎖 (X‑lock / WRITE LOCK)	只允許單一交易寫入，其他交易無法讀寫	SELECT ... FOR UPDATE、UPDATE、DELETE
意向鎖 (Intention Lock)	父層資料表對子層資料的意向，協調表與行鎖的兼容性	InnoDB 自動產生，開發者不必直接使用

註：InnoDB 為 MySQL 預設的事務型儲存引擎，支援行級鎖（Row‑level lock）與多版本併發控制（MVCC），是最常見的死鎖場景來源。

2. 什麼是死鎖？

死鎖發生在 兩筆以上的交易，彼此互相等待對方持有的鎖而無法繼續執行。簡單的圖示如下：

TxA: 鎖 A → 等待 B
TxB: 鎖 B → 等待 A

MySQL 內部偵測到循環等待時，會選擇 回滾其中一筆交易（通常是成本最低的那筆），以打破死鎖。

3. 死鎖的偵測與資訊取得

MySQL 會把死鎖資訊寫入錯誤日誌（error log）與 INNODB STATUS。可以使用以下指令快速查看最近一次的死鎖細節：

SHOW ENGINE INNODB STATUS\G

在輸出中會看到類似：

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5 page no 3 n bits 72 index `PRIMARY` of table `test`.`orders` trx id 12345 lock_mode X locks gap before rec

技巧：將 SHOW ENGINE INNODB STATUS 的輸出導入檔案或使用監控工具（如 Percona Toolkit 的 pt-deadlock-logger）可協助長期追蹤與分析。

4. 產生死鎖的常見情境

情境	說明
交叉更新	兩筆交易分別先更新 A 再更新 B，另一筆則相反。
不一致的索引使用	有交易使用索引鍵鎖定行，有交易因全表掃描鎖住較大範圍，導致等待。
長時間持有鎖	在交易中執行大量計算或 I/O，未即時提交（COMMIT）或回滾（ROLLBACK）。
自增欄位與唯一鍵衝突	多筆插入同時爭奪自增鎖或唯一鍵鎖，產生循環等待。

---

程式碼範例

以下示範 4 個常見的死鎖情境與對應的避免寫法。所有範例皆使用 InnoDB，並以 transaction_isolation = REPEATABLE READ 為預設隔離層級。

範例 1：最簡單的交叉更新死鎖

-- 交易 A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 鎖住 id=1
-- 此時暫停，讓交易 B 執行
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;   -- 鎖住 id=2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 交易 B（同時執行）
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;   -- 鎖住 id=2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 嘗試鎖住 id=1 → 死鎖

避免方法：統一鎖定順序（例如：永遠先鎖 id 小的再鎖大的）：

START TRANSACTION;
SELECT * FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
-- 之後的 UPDATE 依舊使用相同順序
COMMIT;

範例 2：索引缺失導致全表掃描鎖

-- 假設 orders 表沒有在 `user_id` 上建立索引
START TRANSACTION;
SELECT * FROM orders WHERE user_id = 12345 FOR UPDATE;   -- 會掃描整張表，鎖住大量行

避免方法：加上適當的索引，讓 InnoDB 只鎖定必要的行：

CREATE INDEX idx_user_id ON orders(user_id);

之後同樣的查詢只會鎖住 user_id = 12345 的那幾筆記錄。

範例 3：長時間交易與延遲提交

START TRANSACTION;
SELECT * FROM products WHERE id = 10 FOR UPDATE;
-- 執行一段耗時的外部 API 呼叫（假設 30 秒）
SELECT SLEEP(30);
UPDATE products SET stock = stock - 1 WHERE id = 10;
COMMIT;

問題：在 SLEEP(30) 期間，id = 10 的排他鎖依舊持有，其他交易只能等候。

避免方法：將耗時操作搬到交易外，或使用 兩階段提交：

-- 先取得必要資訊，提交交易
START TRANSACTION;
SELECT stock FROM products WHERE id = 10;
COMMIT;

-- 執行外部 API
-- ...

-- 再次開啟交易，執行更新
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;
COMMIT;

範例 4：自增鎖與唯一鍵衝突

-- 多個執行緒同時插入相同的 email
START TRANSACTION;
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');
COMMIT;

若 email 欄位有唯一索引，當兩筆插入同時進行時，會產生 插入死鎖（因為 InnoDB 需要先取得唯一鍵鎖）。

避免方法：

1. 先檢查是否已存在（使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE）
2. 使用較寬鬆的隔離層級（如 READ COMMITTED）降低鎖衝突

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

---

常見陷阱與最佳實踐

陷阱	說明	最佳實踐
過度使用 SELECT ... FOR UPDATE	把所有查詢都加上排他鎖，會不必要地擴大鎖範圍。	僅在確實需要更新同一筆資料時使用。
忽略索引	索引缺失會導致全表掃描鎖，易發生死鎖。	事先分析查詢計畫（EXPLAIN），確保關鍵條件都有索引。
長時間交易	包含大量 I/O、外部呼叫或大量資料處理。	盡量縮短交易範圍，將非必要的工作搬出交易。
不一致的鎖定順序	多個交易以不同順序存取同一組資源。	統一資源存取順序（如依主鍵升序）。
忽視錯誤處理	死鎖回滾後未重新嘗試，導致業務失敗。	捕捉 ER_LOCK_DEADLOCK（錯誤碼 1213），實作 重試機制（最多 3 次）。

重試範例（使用 MySQL Connector/J）

int retry = 0;
while (retry < 3) {
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        // 執行交易...
        conn.commit();
        break;   // 成功則跳出迴圈
    } catch (SQLException ex) {
        if (ex.getErrorCode() == 1213) {   // Deadlock
            retry++;
            System.out.println("Deadlock detected, retry " + retry);
            // 可加上稍微的延遲
            Thread.sleep(100 * retry);
        } else {
            throw ex;   // 其他錯誤直接拋出
        }
    }
}

---

實際應用場景

1. 金融系統的轉帳

   • 必須同時扣除 A 帳戶、增加 B 帳戶的餘額。若兩筆轉帳同時執行且帳號順序相反，極易產生死鎖。
   • 解法：先對帳號做排序，或使用 兩階段提交（先檢查餘額，再執行更新）。

2. 電商平台的庫存扣減

   • 多個使用者同時下單同一商品，會同時嘗試更新 stock 欄位。
   • 解法：在 UPDATE 時加入條件 WHERE stock > 0，若受影響列數為 0，表示庫存不足，可直接回覆失敗，避免長時間鎖住。

3. 內容管理系統（CMS）的多筆批次更新

   • 批次匯入大量文章，同時更新分類計數。
   • 解法：先將計數寫入暫存表，最後一次性更新主表，減少鎖的持有時間。

4. 即時分析系統的資料搬移

   • 從 OLTP 資料庫搬移資料到 OLAP，若使用 SELECT ... LOCK IN SHARE MODE，會阻塞寫入。
   • 解法：使用 讀取已提交（READ COMMITTED） 或 快照讀取（Snapshot），避免持有共享鎖。

---

總結

• 死鎖 是多筆交易因鎖定資源順序不一致而互相等待的狀況，MySQL 會自動偵測並回滾其中一筆交易。
• 透過 SHOW ENGINE INNODB STATUS、錯誤代碼 1213，可以快速定位死鎖根源。
• 預防死鎖 的關鍵在於：
  1. 統一鎖定順序（依主鍵或固定欄位排序）
  2. 建立適當索引，避免全表掃描鎖
  3. 縮短交易時間，將耗時操作搬出交易範圍
  4. 正確使用隔離層級，在可接受的情況下降低鎖衝突
  5. 實作重試機制，對 ER_LOCK_DEADLOCK 進行自動重試

掌握以上概念與實務技巧，你就能在 MySQL 應用程式中有效降低死鎖發生率，提升系統的可靠性與使用者體驗。祝開發順利！