MySQL 鎖機制與競爭

共享鎖與排他鎖

---

簡介

在多使用者的資料庫環境中，資料一致性與併發效能往往是相互制衡的兩大課題。
當多個事務同時存取同一筆資料時，如果不加以控制，就可能發生「髒讀」或「遺失更新」等問題，導致資料變得不可靠。

MySQL 透過 鎖機制（Locking）來協調事務之間的競爭，其中最常見且最基礎的兩種鎖是 共享鎖（S‑Lock） 與 排他鎖（X‑Lock）。
了解這兩種鎖的行為、適用情境與使用方式，能讓開發者在設計交易（Transaction）時，兼顧 資料正確性 與 系統效能，避免不必要的死結或資源浪費。

本篇文章將以 淺顯易懂 的方式，從概念說明、實作範例、常見陷阱到最佳實踐，完整介紹 MySQL 中的共享鎖與排他鎖，適合初學者到中級開發者閱讀與實作。

---

核心概念

1. 鎖的基本原理

鎖的類型	讀取行為	寫入行為	其他事務的衝突
共享鎖 (S‑Lock)	允許 多個 事務同時 讀取 同一筆資料	不允許 任何事務寫入	其他事務只能再取得 共享鎖，不能取得排他鎖
排他鎖 (X‑Lock)	不允許 其他事務讀取	允許 事務寫入（自己）	任何其他事務（讀或寫）都必須等待鎖釋放

• 共享鎖：又稱讀鎖（Read Lock），用於 SELECT … LOCK IN SHARE MODE 或 SELECT … FOR SHARE（MySQL 8.0+）。
• 排他鎖：又稱寫鎖（Write Lock），用於 SELECT … FOR UPDATE、UPDATE、DELETE、INSERT 等會修改資料的語句。

重點：在 InnoDB 引擎中，鎖的粒度可以是 行鎖（Row‑level）、間隙鎖（Gap‑lock） 或 表鎖（Table‑level），而共享/排他屬性則決定了其他事務能否同時存取同一資源。

2. 共享鎖的工作流程

1. 事務 A 執行 SELECT ... LOCK IN SHARE MODE，InnoDB 為符合條件的每一筆記錄加上 共享鎖。
2. 事務 B 若也執行相同的查詢，會 成功取得共享鎖，兩者可以同時讀取。
3. 若事務 C 想要 修改（UPDATE/DELETE）同一筆記錄，必須先取得 排他鎖，但因已有共享鎖存在，C 必須等待 A、B 釋放鎖。

3. 排他鎖的工作流程

1. 事務 X 執行 SELECT ... FOR UPDATE 或直接執行 UPDATE，InnoDB 為符合條件的每一筆記錄加上 排他鎖。
2. 任何其他事務（無論是讀或寫）在同一時間都 被阻塞，直到 X 提交（COMMIT）或回滾（ROLLBACK）釋放鎖。
3. 若有大量讀取需求，過度使用排他鎖會導致 讀取阻塞，影響系統吞吐量。

4. 鎖的升級與降級

• 升級（Upgrade）：從共享鎖升為排他鎖。MySQL 不支援自動升級，必須在同一事務內先釋放共享鎖（COMMIT）再重新取得排他鎖，或使用 SELECT ... FOR UPDATE 直接取得排他鎖。
• 降級（Downgrade）：從排他鎖降為共享鎖。InnoDB 允許在同一事務內先取得排他鎖，完成寫入後再執行 SELECT ... LOCK IN SHARE MODE，此時會自動降級為共享鎖（但仍保持排他鎖的持續時間，除非手動釋放）。

---

程式碼範例

以下範例均使用 InnoDB 引擎的 employees 資料表（僅示範結構），請先確保資料表已建立：

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(50),
    salary   DECIMAL(10,2),
    dept_id  INT,
    INDEX idx_dept (dept_id)
) ENGINE=InnoDB;

範例 1：共享鎖的簡單使用

-- 事務 A：取得共享鎖，讀取部門 10 的員工
START TRANSACTION;
SELECT emp_id, name, salary
FROM employees
WHERE dept_id = 10
LOCK IN SHARE MODE;   -- 或在 MySQL 8.0+ 使用 FOR SHARE

-- 此時其他事務仍可執行相同的 SELECT ... LOCK IN SHARE MODE
COMMIT;   -- 釋放共享鎖

說明：LOCK IN SHARE MODE 讓事務 A 可以安全地讀取資料，同時阻止其他事務對同一筆資料的寫入（UPDATE/DELETE）。

範例 2：排他鎖防止競爭更新

-- 事務 B：想要將部門 20 所有員工的薪資調整 5%
START TRANSACTION;
SELECT emp_id, salary
FROM employees
WHERE dept_id = 20
FOR UPDATE;   -- 取得排他鎖

-- 取得鎖後進行更新
UPDATE employees
SET salary = salary * 1.05
WHERE dept_id = 20;

COMMIT;   -- 釋放排他鎖

說明：FOR UPDATE 確保在事務 B 完成調整前，其他事務無法同時讀取或修改相同的記錄，避免 遺失更新（Lost Update）問題。

範例 3：同時讀取與寫入的衝突示例

-- 事務 C：先取得共享鎖（只讀）
START TRANSACTION;
SELECT salary FROM employees WHERE emp_id = 5 LOCK IN SHARE MODE;
-- 此時事務 D 嘗試更新同一筆資料
-- 事務 D 會被阻塞，直到事務 C COMMIT 或 ROLLBACK
-- 事務 D:
START TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE emp_id = 5;  -- 等待排他鎖
COMMIT;

結果：事務 D 必須等到事務 C 釋放共享鎖後才能取得排他鎖，完成更新。

範例 4：使用 Gap Lock 防止幻影讀（Phantom Read）

-- 設定事務隔離級別為 REPEATABLE READ（InnoDB 預設）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
-- 透過範圍查詢取得排他鎖（包括間隙鎖）
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 6000 FOR UPDATE;
-- 此時即使有其他事務 INSERT 一筆 salary 為 5500 的新紀錄，也會被阻塞
COMMIT;

說明：FOR UPDATE 在範圍查詢時會產生 間隙鎖（Gap Lock），防止其他事務在此範圍內插入新資料，避免幻影讀的發生。

範例 5：手動釋放鎖（僅在特定情況下使用）

-- 事務 E：先取得排他鎖，完成寫入後想提前釋放鎖
START TRANSACTION;
UPDATE employees SET salary = salary * 0.9 WHERE dept_id = 30;
-- 提前釋放鎖
COMMIT;   -- 立即釋放所有鎖
-- 若仍需進一步查詢，可在同一事務內使用 SAVEPOINT

提示：在 MySQL 中 沒有 UNLOCK TABLES 釋放行鎖的指令，唯一的方式是 提交（COMMIT）或回滾（ROLLBACK） 事務。

---

常見陷阱與最佳實踐

陷阱	可能的後果	解決方案 / 最佳實踐
過度使用排他鎖	讀取被阻塞、系統吞吐量下降	只在 必須寫入 時使用 FOR UPDATE，讀取時盡量使用 LOCK IN SHARE MODE 或 不加鎖（依需求）
長時間持有鎖	產生 死結 或 鎖等待超時（Lock wait timeout）	儘量 縮短事務範圍，在必要的最小程式碼區塊內開始與結束事務
忽略隔離級別	可能出現 幻影讀、不可重複讀 等問題	了解 READ COMMITTED、REPEATABLE READ、SERIALIZABLE 的差異，根據業務需求選擇合適的隔離級別
在同一事務內混用共享與排他鎖	可能導致 鎖升級失敗，產生等待或死結	若需要先讀後寫，直接使用 SELECT ... FOR UPDATE 取得排他鎖，或分兩個事務處理
不當的索引設計	鎖範圍過大（表鎖）	為查詢條件建立適當的 索引，讓 InnoDB 能夠使用 行鎖 而非 表鎖

具體最佳實踐

1. 最小化事務持續時間

   START TRANSACTION;
   -- 必要的 SELECT ... FOR UPDATE
   UPDATE ... ;
   COMMIT;   -- 立即釋放鎖
   

2. 使用索引避免全表鎖

   CREATE INDEX idx_salary ON employees(salary);
   SELECT * FROM employees WHERE salary > 5000 FOR UPDATE;  -- 只會鎖住符合條件的行
   

3. 合理設定 innodb_lock_wait_timeout
   • 依系統需求調整超時時間，避免長時間等待導致應用程式卡住。

   SET GLOBAL innodb_lock_wait_timeout = 50;   -- 單位為秒
   

4. 監控與診斷
   • 使用 SHOW ENGINE INNODB STATUS\G 或 performance_schema 觀察鎖等待與死結資訊。

---

實際應用場景

場景	為何需要共享鎖	為何需要排他鎖
庫存管理系統（減少庫存）	多個使用者同時查詢庫存餘額，只讀 時使用共享鎖，避免寫入衝突。	當使用者下單扣除庫存時，需要 排他鎖 確保庫存不被同時扣除兩次。
金融交易（帳戶餘額）	查詢帳戶餘額時使用共享鎖，讓多筆查詢同時進行。	轉帳或扣款時使用排他鎖，確保餘額更新的原子性，避免 超額扣款。
報表產生（大量讀取）	報表產生時只需要讀取資料，可使用共享鎖或 不加鎖（READ COMMITTED）以提升效能。	若報表同時需要 寫入暫存表，則對暫存表使用排他鎖，避免其他報表同時寫入衝突。
商品價格調整（批次更新）	在調整前先以共享鎖檢查價格條件，確保所有讀取的價格是一致的。	調整價格時使用排他鎖，避免其他事務同時修改同一商品的價格。

實務建議：在設計交易流程時，先思考「讀」與「寫」的先後順序，決定是使用共享鎖還是排他鎖，並根據 業務容忍度（例如可接受的等待時間）調整隔離級別與鎖超時設定。

---

總結

• 共享鎖（S‑Lock）允許多個事務同時讀取，但阻止寫入；適用於需要 一致性讀取 而不改變資料的情境。
• 排他鎖（X‑Lock）保證事務在修改資料期間，其他事務無法讀取或寫入；是 防止遺失更新 與 保持資料完整性 的關鍵。
• 正確使用鎖需要配合 索引設計、事務範圍控制、適當的隔離級別，以及 及時監控 鎖等待與死結情況。
• 在實務開發中，先以 共享鎖 確保讀取安全，再根據業務需求切換到 排他鎖 完成寫入，能在 效能 與 資料正確性 之間取得最佳平衡。

透過本文的概念說明、程式碼範例與最佳實踐，您應該已能在 MySQL 中熟練運用共享鎖與排他鎖，為系統的併發控制與資料一致性奠定堅實基礎。祝開發順利，資料永遠正確！