MySQL InnoDB 行鎖與表鎖（Row Lock / Table Lock）

簡介

在多使用者的資料庫環境中，資料一致性與併發效能往往是最關鍵的兩大課題。
InnoDB 作為 MySQL 的預設儲存引擎，提供了細緻的行鎖（Row Lock）與較粗糙的表鎖（Table Lock）機制，讓開發者可以在保證資料正確性的同時，盡可能提升系統的吞吐量。

本單元將深入探討 InnoDB 的鎖定行為，說明 何時會產生行鎖、表鎖，以及如何透過正確的寫法避免不必要的鎖爭用。無論你是剛入門的初學者，或是需要優化現有應用的中級開發者，都能從這篇文章中獲得實務上可直接套用的技巧。

核心概念

1. 鎖的基本類型

鎖的類型	作用範圍	主要用途	產生條件
行鎖（Row Lock）	單筆資料列	高併發讀寫、避免死鎖	`SELECT ... FOR UPDATE`、`SELECT ... LOCK IN SHARE MODE`、以及 `INSERT`、`UPDATE`、`DELETE` 等 DML 操作
間隙鎖（Gap Lock）	索引間的「空隙」	防止幻讀（Phantom Read）	可重複讀（REPEATABLE READ）隔離層級下的範圍查詢
表鎖（Table Lock）	整個資料表	大量寫入、DDL 操作、低併發環境	`LOCK TABLES`、`ALTER TABLE`、`DROP TABLE`、`TRUNCATE` 等
意向鎖（Intention Lock）	表層的元資料	告知其他事務「我打算在表上加什麼鎖」	自動產生，配合行鎖或間隙鎖使用

重點：InnoDB 會先在表上放置 意向鎖（IX 或 IS），再在實際的資料列或間隙上放行鎖或間隙鎖，這樣才能在同一時間允許多個事務對同一張表的不同列進行操作。

2. 行鎖的實作原理

基於索引：InnoDB 必須透過索引來定位要鎖定的行。如果查詢未使用索引，MySQL 會退化為 全表鎖（即表鎖），因為它無法精確定位行位置。
鎖定粒度：行鎖的粒度是 索引鍵（primary key 或 secondary index）。同一筆資料若同時被多個索引鎖定，會產生多個行鎖。

-- 以主鍵鎖定單筆資料
BEGIN;
SELECT * FROM orders WHERE order_id = 1001 FOR UPDATE;   -- 加排他行鎖
-- 此時其他事務只能讀取（若使用 LOCK IN SHARE MODE）或被阻塞
COMMIT;

3. 間隙鎖與幻讀防止

在 REPEATABLE READ（MySQL 預設）隔離層級下，InnoDB 會在範圍查詢（如 WHERE col BETWEEN 10 AND 20）時，同時加上 間隙鎖，防止其他事務在該範圍內插入新資料，從而避免幻讀。

BEGIN;
SELECT * FROM users WHERE age BETWEEN 20 AND 30 LOCK IN SHARE MODE;
-- 取得共享行鎖 + 間隙鎖
-- 其他事務無法在 age 20~30 的間隙中插入新資料
COMMIT;

4. 表鎖的產生時機

操作	是否會產生表鎖
`LOCK TABLES`	✅
`ALTER TABLE`、`DROP TABLE`、`TRUNCATE`	✅
大量 `INSERT ... SELECT`（無索引）	✅（退化為表鎖）
`SELECT ... FOR UPDATE`（無索引）	✅（退化為表鎖）

實務建議：盡量避免在高併發的交易中使用 LOCK TABLES，除非真的需要全表排他。

5. 觀察鎖定資訊

-- 查看當前正在等待或持有的鎖
SELECT 
    r.trx_id,
    r.trx_state,
    r.trx_started,
    l.lock_type,
    l.lock_mode,
    l.lock_table,
    l.lock_index,
    l.lock_data
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id;

此查詢能幫助你快速定位是哪個事務卡住了哪筆資料，對除錯與優化非常有幫助。

程式碼範例

以下提供 5 個實用範例，說明行鎖、間隙鎖、表鎖的具體使用方式與注意點。

範例 1：基本的排他行鎖（FOR UPDATE）

-- 假設有一筆訂單需要扣庫存
BEGIN;
SELECT product_id, stock 
FROM inventory 
WHERE product_id = 12345 
FOR UPDATE;          -- 加排他行鎖，防止其他事務同時修改

-- 扣除庫存
UPDATE inventory 
SET stock = stock - 1 
WHERE product_id = 12345;

COMMIT;

說明：FOR UPDATE 會對符合條件的行加上 X（排他）鎖，其他事務只能等到本事務提交或回滾後才能取得鎖。

範例 2：共享行鎖（LOCK IN SHARE MODE）

BEGIN;
SELECT * 
FROM accounts 
WHERE user_id = 789 
LOCK IN SHARE MODE;   -- 只取得共享鎖，允許其他事務讀取但阻止寫入

-- 此時可以安全地計算餘額
SELECT SUM(amount) INTO @total FROM transactions WHERE user_id = 789;

COMMIT;

說明：共享鎖允許多個讀取事務同時存在，但任何寫入（UPDATE、DELETE）都會被阻塞，適合「先讀後寫」的情境。

範例 3：使用索引避免全表鎖

-- 錯誤寫法：未使用索引的範圍查詢會退化為表鎖
BEGIN;
SELECT * FROM orders WHERE order_date > '2024-01-01' FOR UPDATE;
COMMIT;

-- 正確寫法：確保 order_date 有索引
CREATE INDEX idx_order_date ON orders(order_date);

BEGIN;
SELECT * FROM orders WHERE order_date > '2024-01-01' FOR UPDATE;  -- 只會鎖住符合條件的行
COMMIT;

重點：建立適當的索引 能讓 InnoDB 精準鎖定行，避免不必要的表鎖與性能下降。

範例 4：間隙鎖防止幻讀

BEGIN;
-- 取得 20~30 歲使用者的共享鎖 + 間隙鎖
SELECT * FROM users WHERE age BETWEEN 20 AND 30 LOCK IN SHARE MODE;

-- 其他事務若嘗試在此年齡區間插入新使用者，會被阻塞
INSERT INTO users (name, age) VALUES ('NewUser', 25);  -- 會等到本事務提交

COMMIT;

說明：LOCK IN SHARE MODE 會自動在搜尋條件的範圍內加上間隙鎖，確保同一範圍內不會出現「幻影」資料。

範例 5：手動表鎖的使用與釋放

-- 需求：在批次匯入大量資料前，先排除其他寫入
LOCK TABLES sales WRITE;   -- 取得表寫入鎖（排他）

-- 大量插入（不使用索引會更快，但必須自行確保資料正確性）
INSERT INTO sales (product_id, qty, amount) VALUES 
(101, 10, 500),
(102, 5, 250),
(103, 20, 1000);

UNLOCK TABLES;   -- 釋放表鎖

警告：使用 LOCK TABLES 時，所有的連線都會被阻塞，請務必在非高峰時段或確定不會影響其他事務時使用。

常見陷阱與最佳實踐

陷阱	可能的影響	解決方案 / 最佳實踐
未加索引的範圍查詢導致全表鎖	併發效能急遽下降，甚至造成死鎖	為所有 `WHERE`、`JOIN`、`ORDER BY` 欄位建立適當索引
長時間交易持有行鎖	其他事務被長時間阻塞，系統吞吐量下降	盡量縮短 transaction 的範圍，只在需要的地方 `BEGIN … COMMIT`
過度使用 `SELECT … FOR UPDATE`	不必要的排他鎖，降低讀取效能	僅在確定「先讀後寫」且需要保證資料一致性的情況下使用
在 REPEATABLE READ 下忘記使用 `LOCK IN SHARE MODE`	可能產生幻讀，導致業務邏輯錯誤	需要防止幻讀的查詢，務必加上 `LOCK IN SHARE MODE` 或 `FOR UPDATE`
在高併發寫入時使用 `LOCK TABLES`	整張表被鎖住，所有寫入請求排隊	儘量改用行鎖或批次寫入，只有在 DDL 或特定維護作業才使用表鎖

其他實務建議

監控與分析：使用 performance_schema、information_schema.innodb_locks 或 Percona Toolkit 的 pt-deadlock-logger 監控鎖爭用情形。
適當的隔離層級：若對幻讀要求不高，可將會話的隔離層級調整為 READ COMMITTED，減少間隙鎖的產生。
分離讀寫：將大量的報表查詢與寫入操作分散到不同的 replica，降低主庫的鎖爭用。
批次寫入：使用 INSERT … ON DUPLICATE KEY UPDATE 或 LOAD DATA INFILE，一次寫入多筆資料，減少鎖的次數。

實際應用場景

場景	使用的鎖類型	為什麼選擇
電商訂單扣庫存	`SELECT … FOR UPDATE`（排他行鎖）	必須保證同一時間只有一個事務能扣除同一筆庫存，避免超賣
會員等級升級（批次）	`LOCK TABLES … WRITE`（表鎖）	批次更新所有會員等級，期間不允許其他寫入，確保資料一致性
即時排行榜查詢	`LOCK IN SHARE MODE`（共享行鎖） + READ COMMITTED	允許多個讀者同時查詢，同時阻止排行榜更新造成的競爭
金融交易流水（高併發）	行鎖 + 意向鎖	多筆交易同時對不同帳戶進行寫入，行鎖粒度最小化衝突
資料遷移 / 大量匯入	`INSERT …` + 無索引（暫時退化表鎖） → 事後重建索引	匯入速度最快，完成後再建立索引以恢復查詢效能

總結

InnoDB 的行鎖與表鎖為 MySQL 提供了彈性的併發控制機制。掌握以下要點，即可在開發與維運中有效避免鎖爭用與死鎖：

永遠使用索引：讓 InnoDB 能精準定位行鎖，避免全表鎖。
最小化交易範圍：只在必要的程式碼區塊內 BEGIN … COMMIT，減少鎖持有時間。
根據需求選擇鎖類型：FOR UPDATE 用於「先讀後寫」的排他需求，LOCK IN SHARE MODE 用於共享讀取，LOCK TABLES 僅在DDL或批次維護時使用。
監控與調整：利用 performance_schema、information_schema 觀測鎖資訊，必要時調整隔離層級或重構查詢。

透過正確的鎖定策略與日常的監控，你的 MySQL 系統將在 高併發 與 資料一致性 之間取得最佳平衡，為業務提供穩定可靠的資料基礎。祝開發順利，資料永遠「不會被鎖住」！