本文分析了 INSERT 及其變種（REPLACE/INSERT ON DUPLICATE KEY UPDATE）的幾個場景的死鎖及如何避免。
作者：張洛丹，DBA 數據庫技術愛好者~
愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。
本文共 3200 字，預計閲讀需要 10 分鐘。
説在前面
本文分析了 INSERT 及其變種（REPLACE/INSERT ON DUPLICATE KEY UPDATE）的幾個場景的死鎖及如何避免：
- 場景一：INSERT 唯一鍵衝突
- 場景二/三：
REPLACE INTO唯一鍵衝突（來自線上業務） - 場景四：
INSERT主鍵衝突（來自官方案例）
其實 Google 一番，也會有大量這樣的文章。本文只是就幾個場景進行了分析，不過一遍走下來，對 INSERT 加鎖情況、如何導致的死鎖也就掌握了，個人能力有限，如文中內容有錯誤和紕漏，也歡迎大佬指出。
有興趣的就繼續往下看吧~
回顧行鎖
在此之前，先淺淺回顧一下 InnoDB 中的行鎖類型。
記錄鎖（RECORD LOCK）
對索引記錄加鎖。
間隙鎖（GAP LOCK，也叫範圍鎖）
對索引記錄的所在間隙加鎖，在 RR 隔離級別下，用於解決幻讀的問題（實際上在 RC 隔離級別下，也會產生間隙鎖）。
S 間隙鎖和 X 間隙鎖是兼容的，不同的事務可以在同一個間隙加鎖。
NEXT-KEY 鎖
相當於 RECORD LOCK + GAP LOCK。
插入意向鎖（INSERT INTENTION LOCK）
GAP 鎖的一種，在執行 INSERT 前，如果待插入記錄的下一條記錄上被加了 GAP 鎖，則 INSERT 語句被阻塞，且生成一個插入意向鎖。
僅會被 GAP 鎖阻塞。
隱式鎖
新插入的記錄，不生成鎖結構，但由於事務 ID 的存在，相當於加了隱式鎖；別的事務要對這條記錄加鎖前，先幫助其生成一個鎖結構，然後再進入等待狀態。
這裏產生死鎖的關鍵就是 GAP 鎖。GAP 鎖是在 RR 隔離級別下用於解決幻讀問題，但是 RC 隔離級別下，在重複鍵檢查和外鍵檢查時也會用到。
再淺淺回顧一下 INSERT 語句加鎖類型：
- 被 GAP 鎖阻塞時，生成一個插入意向鎖。
-
遇到重複鍵衝突時
- 主鍵衝突，產生 S 型記錄鎖（RR 和 RR 隔離級別，實際上在 INSERT 階段時還是會請求 GAP 鎖）。
- 唯一鍵衝突，產生 S 型 NEXT-KEY 鎖（RR 和 RR 隔離級別）。
注意：INSERT 語句正常執行時，不會生成鎖結構。
另外，對於 INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE 稍有一些不同：
鎖類型的不同
INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE 如果遇到重複鍵衝突。
- 如果是主鍵衝突，加 X 型記錄鎖（RR 和 RR 隔離級別，實際上在
INSERT階段時還是會請求 GAP 鎖）。 - 如果是唯一鍵衝突，加 X 型 NEXT-KEY 鎖（RR 和 RR 隔離級別）。
鎖範圍不同
INSERT和INSERT ... ON DUPLICATE KEY UPDATE在插入或UPDATE的行上加 NEXT-KEY 鎖時。-
REPLACE在加 NEXT-KEY 鎖時，會在REPLACE的記錄及其下一條記錄上加 NEXT-KEY 鎖。這裏和官方文檔描述有些不同。如下，官方僅説了會在被
REPLACE的行上加 NEXT-KEY 鎖，但是測試下來其下一行也會加 NEXT-KEY 鎖，具體見後文的場景。
最後淺淺回顧一下死鎖的產生條件以及觀測手段：
死鎖的產生條件
兩個或兩個以上事務，互相等待對方持有的鎖，且持有對方需要的鎖，從而造成循環等待。
死鎖觀測手段
performance_schema.data_locks 查看會話產生的鎖結構信息。
SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
show engine innodb status 查看死鎖信息。
正式開始
正式開始前還是要説一下基本的環境信息：
- MySQL 8.0.32
- transaction_isolation：READ-COMMITTED
準備數據
每個案例初始數據都是這些。
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
a INT NULL,
b INT NULL,
PRIMARY KEY (id),
UNIQUE INDEX uk_a (a ASC)
);
INSERT INTO t1 (id, a, b) VALUES (1, 10, 0);
INSERT INTO t1 (id, a, b) VALUES (2, 20, 0);
INSERT INTO t1 (id, a, b) VALUES (3, 30, 0);
INSERT INTO t1 (id, a, b) VALUES (4, 40, 0);
INSERT INTO t1 (id, a, b) VALUES (5, 50, 0);場景一
| 時刻 | session1 | session2 |
|---|---|---|
| T1 | BEGIN;
INSERT INTO t1(a,b) VALUES (35,0); |
|
| T2 | BEGIN;
INSERT INTO t1(a,b) VALUES (35,0); --被阻塞 |
|
| T3 | INSERT INTO t1(a,b) VALUES (33,0) | |
| T4 | DEADLOCK |
不同時刻持有鎖狀態如下：
説明：示意圖中僅畫出我們分析的唯一索引上的鎖，實際上在對唯一索引加上鎖後，還會對對應的聚簇索引加記錄鎖，對主鍵索引但這裏不去體現了，下文同。
過程解説
T1 時刻
session1 插入記錄成功，此時對應的索引記錄被隱式鎖保護，未生成鎖結構。
T2 時刻
session2 插入記錄檢測到插入值和 session1 唯一鍵衝突。
- session2 幫助 session1 對 a=35 的記錄產生了一個顯式的鎖結構。
- session2 自身產生 S 型的 NEXT-KEY LOCK，請求範圍為 (30,35]，但是其只能獲取到 (30,35) 的 GAP LOCK，而被 session1 的 a=35 的記錄鎖阻塞。
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| xxxxxx2 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx2 | t1 | uk_a | RECORD | S | WAITING | 35, 7 |
| xxxxxx1 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx1 | t1 | uk_a | RECORD | X,REC_NOT_GAP | GRANTED | 35, 7 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.01 sec)T3 時刻
- session1 插入 a=33，被 session2 (30,35)間隙鎖阻塞。
至此，形成閉環鎖等待，死鎖條件達成：
- session1 持有 session2 需要的 a=35 記錄鎖，且請求 session2 持有的 (30,35) GAP 鎖。
- session2 持有 session1 需要的 (30,35) GAP 鎖，且請求 session1 持有的記錄鎖。
下面是打印的死鎖日誌。
針對該場景的死鎖該如何避免：
- 在一個事務中的
INSERT按照主鍵或唯一鍵的順序增序插入，即 session1 可以先插入 a=33 的記錄，再插入 a=35 的記錄，可一定程度避免受到 GAP 鎖的影響。 - 一個事務中只插入一行記錄，且儘快提交。
場景二
| 時刻 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | BEGIN; REPLACE INTO t1 (a, b) VALUES (40, 1); | ||
| T2 | BEGIN; REPLACE INTO t1 (a, b) VALUES (30, 1); -- 被阻塞 | ||
| T3 | BEGIN; REPLACE INTO t1 (a, b) VALUES (40, 1); -- 被阻塞 | ||
| T4 | COMMIT; | ||
| T5 | 2 rows affected; | DEADLOCK,ROLLBACK; |
不同時刻持有鎖狀態如下：
過程解説
T1 時刻
session1 檢測到唯一鍵衝突，對 REPLACE 的記錄和其下一條記錄加 X 型 NEXT-KEY 鎖，即鎖範圍為 (30,40],(40,50]。
注意：這裏和INSERT區分，INSERT遇到唯一鍵衝突被阻塞時，在插入的記錄上加的 NEXT-KEY 鎖，這裏REPLACE是在插入記錄的下一條記錄上加的 NEXT-KEY 鎖（官方文檔描述似乎有欠妥當）。
鎖情況
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| xxxxxx1| t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx1| t1 | uk_a | RECORD | X | GRANTED | 40, 4 |
| xxxxxx1| t1 | uk_a | RECORD | X | GRANTED | 50, 5 |
| xxxxxx1| t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| xxxxxx1| t1 | uk_a | RECORD | X,GAP | GRANTED | 40, 10 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)T2 時刻
session2 遇到唯一鍵衝突，對 REPLACE 的記錄和其下一條記錄加 X 型 NEXT-KEY 鎖，即鎖範圍是 (20,30],(30,40]，對 (20,30],(30,40) 加鎖成功，但是等待 session1 a=40 的記錄鎖。
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| xxxxxx2 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx2 | t1 | uk_a | RECORD | X | GRANTED | 30, 3 |
| xxxxxx2 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| xxxxxx2 | t1 | uk_a | RECORD | X | WAITING | 40, 4 |
| xxxxxx1 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx1 | t1 | uk_a | RECORD | X | GRANTED | 40, 4 |
| xxxxxx1 | t1 | uk_a | RECORD | X | GRANTED | 50, 5 |
| xxxxxx1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| xxxxxx1 | t1 | uk_a | RECORD | X,GAP | GRANTED | 40, 10 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
9 rows in set (0.00 sec)T3 時刻
session3 請求的鎖類型和 session1 相同，鎖範圍為（30,40],(40,50]，在獲取(30,40] NEXT-KEY 鎖時，只獲取到了(30,40) GAP 鎖，等待 session1 a=40 的記錄鎖。
注意：這裏還未對（40，50] 加上鎖，InnoDB 行鎖是逐行獲取的，無法獲取到則被阻塞。
鎖情況
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| xxxxxx3 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx3 | t1 | uk_a | RECORD | X | WAITING | 40, 4 |
| xxxxxx2 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx2 | t1 | uk_a | RECORD | X | GRANTED | 30, 3 |
| xxxxxx2 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 3 |
| xxxxxx2 | t1 | uk_a | RECORD | X | WAITING | 40, 4 |
| xxxxxx1 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx1 | t1 | uk_a | RECORD | X | GRANTED | 40, 4 |
| xxxxxx1 | t1 | uk_a | RECORD | X | GRANTED | 50, 5 |
| xxxxxx1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
| xxxxxx1 | t1 | uk_a | RECORD | X,GAP | GRANTED | 40, 10 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
11 rows in set (0.01 sec)T4 時刻
- session1 提交後，持有的鎖釋放。
- session2 獲取到 a=40 的記錄鎖，至此，session2 持有的鎖為 (20,30],(30,40] NEXT-KEY 鎖 ；session2獲取到鎖後，執行插入操作，由於插入的間隙是 (20,40)，被 session3 的 (30,40) GAP 鎖阻塞，產生插入意向鎖，並進入等待狀態。
至此，形成閉環鎖等待，死鎖條件達成：
- session2 持有 (20,30],(30,40] NEXT-KEY 鎖，請求插入意向鎖，被 session3 的 (30,40) GAP 鎖阻塞。
- session3 持有阻塞 session2 的 (30,40) GAP 鎖，請求 sesion2 持有的 a=40 記錄鎖。
下面是打印的死鎖日誌。
場景三
| 時刻 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | BEGIN; SELECT * FROM t1 WHERE a=40 for UPDATE; | ||
| T2 | BEGIN; REPLACE INTO t1 (a, b) VALUES (30, 1);-- 被阻塞 | ||
| T3 | BEGIN; REPLACE INTO t1 (a, b) VALUES (40, 1); -- 被阻塞 | ||
| T4 | COMMIT; | ||
| T5 | 2 rows affected; | DEADLOCK,ROLLBACK; |
不同時刻持有鎖狀態如下：
該場景和場景二死鎖情況基本相同，只是 session1 持有鎖類型不同，就不一一解説了。
下面是打印的死鎖日誌。
針對場景二和場景三的死鎖該如何避免？
從前面的分析中，可以看到看到在唯一鍵衝突時，INSERT、INSERT ... ON DUPLICATE KEY UPDATE 的加鎖範圍要比 REPLACE 加鎖範圍小，在該場景下，可使用 INSERT ... ON DUPLICATE KEY UPDATE 代替 REPLACE 來避免死鎖，有興趣的可以自己測試下。
場景四
説明
- 本案例測試主鍵衝突的情況，先刪除了表上的唯一鍵，避免干擾。
- 對於唯一鍵衝突的該種場景下同樣會產生死鎖，死鎖情況相同，有興趣可自行驗證。
| 時刻 | session1 | session2 | session3 |
|---|---|---|---|
| T1 | BEGIN;INSERT INTO t1 (id,a, b) VALUES (6,60, 0); | ||
| T2 | BEGIN;INSERT INTO t1 (id,a, b) VALUES(6,70, 0); --被阻塞 | ||
| T3 | BEGIN;INSERT INTO t1 (id,a, b) VALUES(6,80, 0);-- 被阻塞 | ||
| T4 | ROLLBACK; | ||
| T5 | 1 rows affected; | DEADLOCK,ROLLBACK; |
鎖情況
在 T1、T2、T3 階段鎖情況如下，此時並沒有 GAP 鎖，是記錄鎖，相應的鎖狀態如下：
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| xxxxxx3 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx3 | t1 | PRIMARY | RECORD | S,REC_NOT_GAP | WAITING | 6 |
| xxxxxx2 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx2 | t1 | PRIMARY | RECORD | S,REC_NOT_GAP | WAITING | 6 |
| xxxxxx1 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx1 | t1 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 6 |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)T4 時刻
session1 ROLLBACK，session2 和 session3 都獲取到了 S 鎖，在 INSERT 階段，卻產生了 NEXT-KEY 鎖，鎖範圍為 (5,supremum]。
至此，形成閉環鎖等待，死鎖條件達成：
session2 和 session3 分別想要在插入的間隙 (5,supremum) 獲得插入意向鎖，但分別被對方持有的 GAP 鎖阻塞。
下面是打印的死鎖日誌。
觸發死鎖後，我們再看鎖持有情況。
此時 session2 持有 (5,sepremum)，再插入該範圍內的記錄都會被阻塞了。
mysql> SELECT ENGINE_TRANSACTION_ID, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+
| ENGINE_TRANSACTION_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+
| xxxxxx2 | t1 | NULL | TABLE | IX | GRANTED | NULL |
| xxxxxx2 | t1 | PRIMARY | RECORD | S | GRANTED | supremum pseudo-record |
| xxxxxx2 | t1 | PRIMARY | RECORD | X,INSERT_INTENTION | GRANTED | supremum pseudo-record |
| xxxxxx2 | t1 | PRIMARY | RECORD | S,GAP | GRANTED | 6 |
+-----------------------+-------------+------------+-----------+--------------------+-------------+------------------------+
4 rows in set (0.00 sec)小結
從前面的實驗中可以看到無論是 INSERT 還是 REPLACE，在高併發的情況下由於唯一鍵的存在，即使在 RC 隔離級別下，仍然有較大概率會觸發到死鎖。當前只能在業務端做好容錯處理，以下是一些小建議來減少或避免 INSERT 死鎖：
- RC 隔離級別相較 RR 隔離級別產生死鎖的概率小，但仍不可避免。
INSERT ... ON DUPLICATE KEY UPDATE比REPLACE產生死鎖的機率小且更安全高效。- 併發事務按照相同的順序處理數據。
- 事務儘快提交，避免大事務、長事務。
另外，通過前面的實驗，大家可能會有以下疑問：
- 為什麼 RC 隔離級別要使用 GAP 鎖？
- 為什麼主鍵和唯一鍵的處理方式不同？
- ...？？？
有興趣的可以到下面文章尋找答案：
http://mysql.taobao.org/monthly/2022/05/02/
