MySQL Transaction、Lock 與並行控制完整指南

Jesse Yen - 9月 05, 2026
mysql lock transaction

單一使用者操作資料庫時，事情很簡單。真正複雜的是：很多人同時修改資料時，MySQL 怎麼確保資料不會亂掉？

例如銀行帳戶有 10,000 元，同一時間發生提款；或者購物網站只剩最後一件商品，卻有兩個使用者同時下單。

這些問題都牽涉到資料庫最核心的幾個概念：

  • Transaction（交易）
  • ACID (Atomicity/Consistency/Isolation/Durability)
  • Isolation Level（隔離層級）
  • MVCC (多版本並行控制)
  • Lock（鎖定）
  • Lock Wait
  • Deadlock（死結）

它們共同構成 MySQL 的 Transaction 與 並行控制（Concurrency Control）。


Transaction 是什麼？

Transaction 可以理解為：一組必須被視為「同一件事情」的資料庫操作。

最常見的例子就是轉帳。

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

如果中途發現問題，可以：

ROLLBACK;

取消這次 Transaction。


ACID 是什麼？

Atomicity 原子性

一個 Transaction 內的操作應該全部成功，或全部失敗。

Consistency 一致性

Transaction 前後，資料必須符合資料庫規則。

Isolation 隔離性

多個 Transaction 同時執行時，不應該任意互相干擾。

Durability 持久性

COMMIT 完成後，資料應具有持久性。

其中跟並行控制關係最密切的，就是 Isolation。


為什麼需要 Isolation？

想像兩個 Transaction 同時讀到庫存為 1，兩邊都認為可以購買，就可能造成超賣。

因此 Database 必須決定：一個 Transaction 到底能看到其他 Transaction 哪些變更？

這就是 Isolation Level。


MySQL 的四種 Isolation Level

InnoDB 支援四個標準 Transaction Isolation Level：

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

InnoDB 預設使用：

REPEATABLE READ

可以查詢目前設定：

SELECT @@transaction_isolation;

READ UNCOMMITTED

隔離程度最低。一個 Transaction 有可能看到另一個尚未 COMMIT 的資料，因此可能產生 Dirty Read（髒讀）。

READ COMMITTED

只能讀到已經 COMMIT 的資料，因此可以避免 Dirty Read。

但是同一個 Transaction 先後兩次讀取同一筆資料，可能得到不同結果，這就是 Non-repeatable Read（不可重複讀）。

REPEATABLE READ

MySQL InnoDB 預設 Isolation Level。

同一個 Transaction 中的 Consistent Read 通常可以維持一致的資料 Snapshot。這背後牽涉到 MVCC（Multi-Version Concurrency Control，多版本並行控制）。


MVCC 是什麼？

MVCC 的基本概念是：資料修改之後，不代表其他 Transaction 都必須立刻看到同一個版本。

InnoDB 會利用 Transaction 資訊以及 Undo 資料，讓不同 Transaction 在適當情況下看到不同版本的 Row。

Transaction A → 看 Version 1
Transaction B → 修改成 Version 2
Transaction C → 可能看 Version 2

這種設計可以讓很多一般 SELECT 不需要因為另一個 Transaction 修改資料就全部互相等待。


Lock 又是什麼？

MVCC 並不代表 Database 完全不需要 Lock。

當資料需要真正被修改時，仍然需要控制同一筆資料現在到底誰可以修改。

UPDATE products
SET stock = stock - 1
WHERE product_id = 10;

InnoDB 可能對相關 Record 建立 Lock。其他 Transaction 如果也要修改同一筆資料，就可能需要等待。


Row Lock 與 Table Lock

Row Lock

鎖定特定資料列。其他 Transaction 還是可以操作其他資料，因此適合高並行環境。

Table Lock

影響整張 Table，相較之下影響範圍比較大。

但 InnoDB 的實際 Lock Model 比「Row Lock / Table Lock」二分法更複雜，還包括 Shared Lock、Exclusive Lock、Intention Lock、Record Lock、Gap Lock 與 Next-Key Lock。


SELECT 為什麼有時也需要 Lock？

如果程式邏輯是「查詢庫存 → 判斷庫存 → 扣除庫存」，查詢與後續 UPDATE 其實是同一個業務操作。

這時可以使用 Locking Read：

START TRANSACTION;

SELECT stock
FROM products
WHERE product_id = 10
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE product_id = 10;

COMMIT;

FOR UPDATE 可以讓這個讀取不只是看看資料，而是為接下來的修改建立適當鎖定。


Index 跟 Lock 有什麼關係？

很多人認為 Index 只是效能問題，其實 Index 也會影響 Lock。

UPDATE customers
SET status = 'VIP'
WHERE email = '[email protected]';

如果 email 有適當 Index，MySQL 比較容易定位需要處理的 Index Record；如果沒有適當 Index，就可能必須掃描較大的範圍。

因此 Index 設計除了影響 Query Performance，也可能影響 Lock 的範圍與並行能力。


Lock Wait 是什麼？

Transaction A 先修改某一筆資料，但尚未 COMMIT；Transaction B 也要修改同一筆資料，就可能只能等待 Transaction A 釋放 Lock。

Transaction A
     │
     │ 持有 Lock
     ▼
  Product 1
     ▲
     │ 等待
     │
Transaction B

如果 Transaction 開啟太久，就可能造成其他 Transaction 大量等待。

實務上很重要的原則：Transaction 應盡可能短。


Deadlock 是什麼？

Deadlock 比一般 Lock Wait 更麻煩。

Transaction A：
鎖住 Row 1
等待 Row 2

Transaction B：
鎖住 Row 2
等待 Row 1

雙方互相等待，形成循環，這就是 Deadlock（死結）。

InnoDB 可以偵測 Deadlock，並選擇其中一個 Transaction Rollback，讓另一個 Transaction 繼續。

因此 Application 應該具備 Transaction Retry（交易重試）機制。


如何降低 Deadlock？

Transaction 不要太長

Transaction 開啟期間越長，Lock 被持有的時間通常也越長。

固定資料存取順序

如果所有 Transaction 都按照相同順序操作資料，可以降低形成循環等待的機率。

建立適當 Index

適當 Index 可以降低需要掃描與鎖定的資料範圍。

Application 必須處理 Retry

即使設計良好，Deadlock 仍可能發生。


如何查看 Deadlock？

SHOW ENGINE INNODB STATUS;

可以用來觀察最近一次 Deadlock 資訊。

大量問題需要除錯時，也可以進一步研究：

innodb_print_all_deadlocks

Transaction、Lock 與效能其實是同一件事

SQL 設計不好
   ↓
掃描大量資料
   ↓
Lock 更多 Record
   ↓
Transaction 執行時間增加
   ↓
其他 Transaction 等待
   ↓
系統 Throughput 降低
   ↓
使用者覺得 MySQL 很慢

因此進階 MySQL 管理不能只會 SQL Syntax，還需要理解 Database 在多人同時操作時，內部到底發生什麼事情。

延伸學習

  • MySQL Transaction、COMMIT 與 ROLLBACK
  • ACID 是什麼？
  • MySQL 四種 Isolation Level
  • MVCC 是什麼？
  • Row Lock 與 Table Lock
  • Shared Lock 與 Exclusive Lock
  • SELECT FOR UPDATE
  • Gap Lock
  • Next-Key Lock
  • Deadlock 原因與分析
  • SHOW ENGINE INNODB STATUS
  • Index 如何影響 Lock
  • MySQL 效能調校完整指南
標籤 :

張貼留言

0 留言