2025 iThome 鐵人賽

DAY 11

Software Development

資料庫大哉問系列第 11 篇

Day11 - MySQL 如何避免 DeadLock？(Deadlock Analysis)

17th鐵人賽

vicxu

2025-09-11 08:26:02

92 瀏覽

分享至

使用 Lock 最大的風險就是 DeadLock，最常見案例是「互相持有並等待」：

## transaction A:
BEGIN
SELECT * FROM users WHERE id = 1 FOR UPDATE;  
SELECT * FROM users WHERE id = 2 FOR UPDATE;  
...  
COMMIT;  
  
## transaction B:  
BEGIN
SELECT * FROM users WHERE id = 2 FOR UPDATE;  
SELECT * FROM users WHERE id = 1 FOR UPDATE;  
...  
COMMIT;

當 A & B Transaction 同時執行，A 持有 users id=1 資源並等待獲取 users id=2 資源，而 B 持有 users id=2 資源並等待 users id=1 ，雙方互相等待對方釋放資源，造成 DeadLock。

該 DeadLock 解法很簡單，將 Lock 順序改成一致就可以了：

## transaction A:  
BEGIN
SELECT * FROM users WHERE id = 1 FOR UPDATE;  
SELECT * FROM users WHERE id = 2 FOR UPDATE;  
...  
COMMIT;  
  
## transaction B:  
BEGIN
SELECT * FROM users WHERE id = 1 FOR UPDATE;  
SELECT * FROM users WHERE id = 2 FOR UPDATE;  
...  
COMMIT;

又或者可改成 SELECT * FROM users WHERE id IN (1, 2) FOT UPDATE; 。

但不知你會不會好奇：

SELECT * FROM users WHERE id IN (1, 2) FOT UPDATE; 以及
SELECT * FROM users WHERE id IN (2, 1) FOT UPDATE; 同時執行會 DeadLock 嗎？
IN 裡面的參數順序不同會導致 Lock 順序不一致嗎？

我們如何知道執行中 SQL 是怎麼上鎖的？

performance_schema DB 中 data_locks table 可用來看當前所有 Lock 詳細狀況，該 Table Schema 為：

(作者產圖)

例如：

### 建立一個 users table 有唯一主鍵 id 以及非唯一 index name  
create table users (  
	id int auto_increment,  
	name varchar(100),  
	gender int,  
	key (name),  
	primary key (id)  
);  
  
### 建立三筆資料  
insert into users (id, name, gender) values  
(1, 'apple', 0),(2, 'banana',1),(3, 'apple',1);  
  
### 對 id = 1 唯一索引資料上鎖  
begin;  
select * from users where id = 1 for update;  
....

在 transaction 不 commit 情況下執行 select * from performance_schema.data_locks; 可看到：

(作者產圖)

分別有兩個 Lock：
1. Intention Lock : 第一個是 Table Lock 且 Lock Mode IX 代表 Intention Exclusive Lock。
2. Row Lock : 第二個是使用 PRIMARY Index 的 Record Lock 其 Lock Mode 為 X,REC_NOT_GAP，X 代表 Exclusive Lock 而 REC_NOT_GAP 代表不是 Gap Lock 只鎖單一 Record 不影響 INSERT 行為，最後 LOCK_DATA 代表被鎖住的 Index 資料，也就是 id=1 的資料。

接下來：

### 對 name = 'apple' 非唯一索引資料上鎖  
BEGIN;  
select * from users where name = 'apple' for update;  
...

執行 select * from performance_schema.data_locks; 可看到：

(作者產圖)

出現四個 Lock：

Intention Lock : TABLE 的 Intention Lock 。
name Index Lock : 使用 name Index 對兩筆 name=apple 資料上 Exclusive Record Lock，但因為是非唯一索引，會上 Gap Lock 所以沒有 REC_NOT_GAP
PRIMARKY Lock : 同時也需要對 PRIMARY Index id=1 & id=3 上 Exclusive Record Lock，但 PRIMARY 是唯一索引沒有 Gap Lock 所以有 REC_NOT_GAP
Gap Lock : 最後會在 name Index 上一個 Exclusive Gap Lock，由於 name 排序是 apple => banana，所以 Gap Lock 會鎖住這段 [apple, banana) (包含 apple，不包含 banana) 範圍的 insert 行為

隨後執行，INSERT INTO users (name) VALUES ('apple'), ('apple2') 在 [apple, banana) 範圍 Insert 資料，卡住後會發現 Lock 多了兩個：

(作者產圖)

Intention Lock : 多一個 Intention Lock，Intention Lock 跟 Intention Lock 不會互斥，所以是 GRANTED 狀態
WAITING Lock : 多一個 Exclusive, Gap, Insert Intention Lock 狀態是 WAITING，代表 Insert 操作被其他 Gap Lock 卡住了。

另外如果將 select * from users where name = "apple" for update;換成 SELECT * FROM users WHERE name = "banana" FOR UPDATE ，由於排序上 banana 是最後一筆資料，Gap Lock 會變這樣：

(作者產圖)

supremum pseudo-record 代表向上無限衍生且不存在的紀錄，對該紀錄上 Exclusive Lock 會導致 INSERT 資料若排序在 banana 後面都會卡住 (e.g INSERT INTO users (name) VALUES ('dog'), ('cat') )。

總結幾個常見的 Lock Mode

X : Exclusive Lock 也就是 Write Lock
S : Shared Lock 也就是 Read Lock
I : Table Intention Lock
REC_NOT_GAP : 只鎖單筆資料，不鎖範圍
GAP : 不鎖單筆資料，鎖一個範圍不能 Insert 新資料
INSERT_INTENTION : 代表 Insert 正在等其他 Gap Lock 釋放
X 搭配 supremum pseudo-record : 對無限延伸的範圍上 Gap Lock

那麼 MySQL Lock 順序是如何執行的？

當執行

BEGIN;  
SELECT * FROM users WHERE id IN (1, 2, 3) FOR UPDATE;  
...

如上圖，可看到 id (1,2,3) 都有上鎖成功，然後執行

BEGIN;  
SELECT * FROM users WHERE id IN (3, 2, 1) FOR UPDATE;  
...

如果上鎖順序是相反，會拿到 id=3 的鎖並卡住：

(作者產圖)

但實際上 (如上圖) 是拿到 id=1 的鎖卡住，由此可見上鎖順序跟 SQL 寫法無關。

那麼上鎖順序跟什麼有關？

答案是跟 Index Tree Scan 順序有關，id 的 Index 順序是 1->2->3 ，上鎖順序同樣會是 1->2->3，但如果第二個 SQL 加上 ORDER BY 上鎖順序就會相反了：

BEGIN
SELECT * FROM users WHERE id (1, 2, 3) ORDER BY id DESC FOR UPDTE;

如上圖，會拿到 id=3 的鎖並卡住。

除了 ORDER BY 會影響上鎖順序外，建立 Index 時使用逆序設定也會影響：

### 將所有 index 改成 unique，並將 nick_name index 順序設定成 DESC  
create table users (  
	id int auto_increment,  
	name varchar(100),  
	gender int,  
	nick_name varchar(100),  
	unique key (name),  
	unique key (nick_name DESC),  
	primary key (id)  
);

insert into users (id, name, nick_name, gender) values
 (1, 'apple', 'apple', 0),(2, 'banana', 'banana', 1);
 
begin;  
select * from users where name IN ('apple', 'banana') for update;  
....  
  
begin;  
select * from users where nick_name IN ('apple', 'banana') for update;  
....

此時查詢 Lock 狀態會發現 (上圖)，由於 nick_name Index 是 Desc，所以會從 id=2 的資料開始上鎖，跟 name Index 順序相反，導致上面 SQL 看起來順序一致，但同時執行時卻造成了 DeadLock。

除了順序情境外，還有什麼會造成 DeadLock 的情境嗎？

在併發寫入情境中，更新順序若相反也會造成 DeadLock，但其實寫入更常見的 DeadLock 是 Gap Lock，例如：

### transaction A  
begin;  
UPDATE users SET gender=0 WHERE id = 1;  
if users not found:  
	INSERT INTO users (gender, id) VALUES (0 , 1);  
  
### transaction B  
begin;  
UPDATE users SET gender=1 WHERE id = 2;  
if users not found:  
	INSERT INTO users (gender, id) VALUES (0 , 2);

當兩個 Transaction 都 UPDATE 不存在資料時，都會上 Gap Lock 且因為 users 表沒資料，範圍就是 (無限小, 無限大)，此時 A & B Transaction 在執行 INSERT 時就會被對方的 Gap Lock 卡住變成 DeadLock。

另外就是加上面 SQL 精簡成 INSERT INTO users (id, gender) VALUES (1, 0) ON DUPLICATE KEY UPDATE gender=0; ，如果 UPDATE 不存在的值會上 Gap Lock 並 INSERT ，此時若多個 Transaction 執行：

### transaction A  
INSERT INTO users (id, gender) VALUES (1, 0) ON DUPLICATE KEY UPDATE gender=0;  
  
### transaction B  
INSERT INTO users (id, gender) VALUES (2, 0) ON DUPLICATE KEY UPDATE gender=1;  
  
### transaction C  
INSERT INTO users (id, gender) VALUES (3, 0) ON DUPLICATE KEY UPDATE gender=0;

transaction A 獲取 Gap Lock 並進入 insert
transaction B & C 同時獲取 Gap Lock ，並等待 transaction A insert 行為結束，才能 insert
transaction A commit 釋放 Gap Lock ，但由於 transaction B & C 都獲取了 Gap Lock，當他們兩繼續執行 insert 行為時，會被彼此的 Gap Lock 卡住，導致 DeadLock

要解決上述 DeadLock 問題很簡單，就是先執行 INSERT ，出現 ON DUPLICATE KEY ERROR 在執行 UPDATE ，若想避免頻繁出現 ON DUPLICATE KEY ERROR，可用 in memory cache 紀錄一個 flag。

最後是 Foreign Key 造成的 DeadLock 案例：

customers 跟 orders 是一對多關係，orders 表中有 customer_id 欄位關聯到 customers 表的 id。

執行 INSERT INTO orders (customer_id) VALUES (123); 時，為確保 customers id=123 這筆資料存在， MySQL 會對 customers id=123 這筆資料上讀鎖，因此如果執行：

### transaction A  
BEGIN:
INSERT INTO orders (id, customer_id) VALUES (1, 123);
UPDATE customers SET count=count+1 WHERE id = 123;

### transaction B
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (2, 123);
UPDATE customers SET count=count+1 WHERE id = 123;

Transaction A & B 同時執行，A & B 同時 INSERT orders 成功並對 customers 上讀鎖，隨後 A & B 執行 UPDATE customers 時就會被彼此的讀鎖卡住，造成 DeadLock。

如果不幸發生 DeadLock，MySQL 會怎麼處理？

MySQL 有 DeadLock Detection 的功能，會主動偵測 DeadLock 並 Rollback 其中一個 Transaction，讓另一個 Transaction 順利執行，可透過設定 innodb_lock_wait_timeout 參數調整 Transaction 卡住的時間，如果 Transaction 卡住時間超過該參數就會被強制 Rollback。

MySQL 還會紀錄最後一次 DeadLock 的詳細資訊，可以透過執行 SHOW ENGINE INNODB STATUS 指令獲取 InnoDB 的詳細資訊，其中 LATEST DETECTED DEADLOCK Section 為會紀錄最後兩個造成 DeadLock 的 Transaction 資訊：

------------------------  
LATEST DETECTED DEADLOCK  
------------------------  
2020-12-26 00:05:14 0x7f9657cf9700  
*** (1) TRANSACTION:  
TRANSACTION 14048, ACTIVE 1 sec starting index read  
mysql tables in use 1, locked 1  
LOCK WAIT 11 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2  
MySQL thread id 54, OS thread handle 140283242518272, query id 45840 172.22.0.1 api-server updating  
update `products` set `sold` = 32 where `id` = '919'  
  
*** (1) HOLDS THE LOCK(S):  
RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14048 lock mode S locks rec but not gap  
Record lock, heap no 259 PHYSICAL RECORD: n_fields 7; compact format; info bits 0  
0: len 4; hex 00000397; asc ;;  
1: len 6; hex 0000000036d7; asc 6 ;;  
2: len 7; hex 010000013f1e26; asc ? &;;  
3: len 21; hex 50726163746963616c204672657368204d6f757365; asc Practical Fresh Mouse;;  
4: len 4; hex 800000b1; asc ;;  
5: len 4; hex 800000fe; asc ;;  
6: len 4; hex 80000020; asc ;;  
  
  
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14048 lock_mode X locks rec but not gap waiting  
Record lock, heap no 259 PHYSICAL RECORD: n_fields 7; compact format; info bits 0  
0: len 4; hex 00000397; asc ;;  
1: len 6; hex 0000000036d7; asc 6 ;;  
2: len 7; hex 010000013f1e26; asc ? &;;  
3: len 21; hex 50726163746963616c204672657368204d6f757365; asc Practical Fresh Mouse;;  
4: len 4; hex 800000b1; asc ;;  
5: len 4; hex 800000fe; asc ;;  
6: len 4; hex 80000020; asc ;;  
  
  
*** (2) TRANSACTION:  
TRANSACTION 14052, ACTIVE 1 sec starting index read  
mysql tables in use 1, locked 1  
LOCK WAIT 11 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2  
MySQL thread id 57, OS thread handle 140283970258688, query id 45841 172.22.0.1 api-server updating  
update `products` set `sold` = 34 where `id` = '919'  
  
*** (2) HOLDS THE LOCK(S):  
RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14052 lock mode S locks rec but not gap  
Record lock, heap no 259 PHYSICAL RECORD: n_fields 7; compact format; info bits 0  
0: len 4; hex 00000397; asc ;;  
1: len 6; hex 0000000036d7; asc 6 ;;  
2: len 7; hex 010000013f1e26; asc ? &;;  
3: len 21; hex 50726163746963616c204672657368204d6f757365; asc Practical Fresh Mouse;;  
4: len 4; hex 800000b1; asc ;;  
5: len 4; hex 800000fe; asc ;;  
6: len 4; hex 80000020; asc ;;  
  
  
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14052 lock_mode X locks rec but not gap waiting  
Record lock, heap no 259 PHYSICAL RECORD: n_fields 7; compact format; info bits 0  
0: len 4; hex 00000397; asc ;;  
1: len 6; hex 0000000036d7; asc 6 ;;  
2: len 7; hex 010000013f1e26; asc ? &;;  
3: len 21; hex 50726163746963616c204672657368204d6f757365; asc Practical Fresh Mouse;;  
4: len 4; hex 800000b1; asc ;;  
5: len 4; hex 800000fe; asc ;;  
6: len 4; hex 80000020; asc ;;  
  
*** WE ROLL BACK TRANSACTION (2)

以上面的資訊為例，我們來試著分析 DeadLock 的解法：

首先 SQL 執行語法為：(1) Transaction 為 update `products` set `sold` = 32 where `id` = 919 (2) Transaction 為 update `products` set `sold` = 34 where `id` = 919
分析 Transaction 1 Lock 狀況：
- (1) HOLDS THE LOCK(S) 顯示為 lock mode S locks rec but not gap 代表 Transaction 1 有拿到該資料的讀鎖權限
- (1) WAITING FOR THIS LOCK TO BE GRANTED 顯示 lock_mode X locks rec but not gap waiting 代表 Transaction 1 在等待寫鎖權限，且不是因為 Gap Lock 被卡住。
分析 Transaction 2 Lock 狀況：
- (2) HOLDS THE LOCK(S) 和 (2) WAITING FOR THIS LOCK TO BE GRANTED 的資訊顯示跟 Transaction 1 狀況一樣。

透過上面資訊可以推導，這兩個 Transaction 都拿到了讀鎖，並往下執行 UPDATE 獲取寫鎖時互相卡住了，所以完整的 Transaction 應該長這樣：

## Transaction 1  
begin;  
SELECT id, sold FROM products WHERE id = 919 LOCK IN SHARE MODE;  
...  
UPDATE products SET sold = 32 WHERE id = 919  
commit;  
  
## Transaction 2  
begin;  
SELECT id, sold FROM products WHERE id = 919 LOCK IN SHARE MODE;  
...  
UPDATE products SET sold = 34 WHERE id = 919  
commit;

邏輯看起來是同時賣出相同產品，該情況有兩種解法：

將 LOCK IN SHARE MODE 改成 FOR UPDATE 確保先拿讀鎖，誰先拿到先執行 UPDATE
移除 SELECT 語法，使用 UPDATE products SET sold = sold + ? WHERE id = 919 AND sold >= ? 效能較好