MySQL 并發 replace into 導致 insert intention 與 gap lock 形成死鎖
引言
本文介紹一個在 pt-osc 執行期間發生的死鎖案例，其實之前的文章 并發 replace into 導致 supremum X 鎖與插入意向鎖形成死鎖 中也分析過相關案例，但由于理解不到位導致根因分析并不全面，因此本文進一步分析該類型的死鎖，包括死鎖發生的原因與優化方法。
現象
時間：2024-03-13 20:48:29
數據庫版本：MySQL 5.7.21
現象：pt-osc 執行 DDL 期間多次發生死鎖
分析
死鎖日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-13 20:48:29 0x7ff6fb2f8700
*** (1) TRANSACTION:
TRANSACTION 385752159, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 17811400, OS thread handle 140698772580096, query id 25019811085 x.x.x.x eclp_po1_rw update
REPLACE INTO `eclp_po1`.`_po_main_new` (`id`, `parent_no`, `po_no`, `unit_flag`, `unit_rule`, `bill_of_lading`, `dept_no`, `dept_id`, `dept_name`, `seller_id`, `seller_no`, `seller_name`, `org_id`, `org_no`, `org_name`, `distribution_id`, `distribution_no`, `distribution_name`, `warehouse_id`, `warehouse_no`, `warehouse_name`, `out_warehouse_no`, `out_warehouse_name`, `target_warehouse_id`, `expect_arrival_time`, `po_type`, `po_status`, `po_cancel_status`, `po_sign`, `out_source_no`, `out_po_no`, `out_seller_no`, `difference_remark`, `contacts`, `contacts_address`, `supplier_id`, `supplier_no`, `supplier_name`, `supplier_contacts`, `approval_time`, `approval_user`, `po_dl_result`, `po_dl_message`, `receive_level`, `isv_replenish_type`, `temperature`, `temperature_value`, `create_time`, `products_code`, `receive_box_number`, `products_name`, `update_time`, `create_user`, `update_user`, `yn`, `ts`,
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752159 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 16; hex 45504c34343138303834393836363939; asc xxx;;
1: len 8; hex 80000404aa62df4b; asc b K;;
*** (2) TRANSACTION:
TRANSACTION 385752158, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 17811470, OS thread handle 140698752878336, query id 25019811042 x.x.x.x eclp_po1_rw update
REPLACE INTO `eclp_po1`.`_po_main_new` (`id`, `parent_no`, `po_no`, `unit_flag`, `unit_rule`, `bill_of_lading`, `dept_no`, `dept_id`, `dept_name`, `seller_id`, `seller_no`, `seller_name`, `org_id`, `org_no`, `org_name`, `distribution_id`, `distribution_no`, `distribution_name`, `warehouse_id`, `warehouse_no`, `warehouse_name`, `out_warehouse_no`, `out_warehouse_name`, `target_warehouse_id`, `expect_arrival_time`, `po_type`, `po_status`, `po_cancel_status`, `po_sign`, `out_source_no`, `out_po_no`, `out_seller_no`, `difference_remark`, `contacts`, `contacts_address`, `supplier_id`, `supplier_no`, `supplier_name`, `supplier_contacts`, `approval_time`, `approval_user`, `po_dl_result`, `po_dl_message`, `receive_level`, `isv_replenish_type`, `temperature`, `temperature_value`, `create_time`, `products_code`, `receive_box_number`, `products_name`, `update_time`, `create_user`, `update_user`, `yn`, `ts`,
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752158 lock_mode X locks gap before rec
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 16; hex 45504c34343138303834393836363939; asc xxx;;
1: len 8; hex 80000404aa62df4b; asc b K;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752158 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 16; hex 45504c34343138303834393836363939; asc xxx;;
1: len 8; hex 80000404aa62df4b; asc b K;;
*** WE ROLL BACK TRANSACTION (1)其中：
- REPLACE INTO _po_main_new，表明是 pt-osc 的 SQL，其中 SQL 截斷；
- eclp_po1_rw，顯示是業務賬號，表明對應 pt-osc 增量數據同步階段；
- index po_no，加鎖索引；
- info bits 0，表明沒有發生標記刪除。結合顯示 ACTIVE 0 sec inserting，因此判斷對應 insert，不是 update；
- 日志顯示間隙鎖與插入意向鎖沖突導致死鎖。
表結構
mysql> show create table eclp_po1.po_main \G
*************************** 1. row ***************************
Table: po_main
Create Table: CREATE TABLE `po_main` (
`id` bigint(20) NOT NULL COMMENT '主鍵',
`parent_no` varchar(50) DEFAULT NULL COMMENT '采購父單號',
`po_no` varchar(50) NOT NULL COMMENT '采購單號（ECLP采購單號）',
...
PRIMARY KEY (`id`),
UNIQUE KEY `po_no` (`po_no`) USING BTREE,
...其中：
- index po_no，二級唯一索引
復現
測試數據
數據庫版本版本：5.7.24
事務隔離級別：RR
測試表結構
mysql> show create table t_lock \G
*************************** 1. row ***************************
Table: t_lock
Create Table: CREATE TABLE `t_lock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT '0',
`b` int(11) DEFAULT '0',
`c` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from t_lock;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 5 | 5 | 5 | 5 |
| 9 | 9 | 9 | 9 |
+----+------+------+------+
3 rows in set (0.00 sec)流程
三個事務并發向同一個間隙插入數據。
時刻 | session 1 | session 2 | session 3 |
1 | begin; replace into t_lock values(2,2,2,2); | ||
2 | begin; replace into t_lock values(3,3,3,3); blocked | ||
3 | begin; replace into t_lock values(4,4,4,4); blocked | ||
4 | rollback; / commit; | ||
5 | Query OK, 1 row affected | Deadlock found |
其中：
- replace 對應 insert；
- 與 insert 不同之處在于事務 1 提交以后也會觸發死鎖，原因是事務 2 與 3 持有的間隙鎖與事務 1 的回滾操作無關；
- 研發反饋不存在回滾操作，因此判斷提交操作觸發死鎖。
由于死鎖日志與前文相同，因此這里不再展示，但是為了分析死鎖，下面分析鎖等待的原因。
鎖信息
時刻 3 查看事務 1 與 2 的鎖信息
圖片
其中：
- 事務 2 與 3 的鎖信息相同，因此僅展示事務 1 與 2；
- 事務 1 與 2 均已持有間隙鎖，但是事務 2 發生插入意向鎖等待；
時刻 3 查看事務 2 與 3 的鎖等待信息
圖片
其中：
- 顯示有三組鎖等待，右圖中展示事務間鎖等待的關系，其中箭頭指向表示事務等待的鎖；
- 事務 1 同時阻塞事務 2 與 3；
- 事務 3 同時等待事務 1 與 2，顯然事務 3 指向事務 2 是死鎖檢測時環中的第一條邊，下一條邊將從事務 2 指回事務 3；
- 事務 1 提交或回滾后，事務 2 與 3 同時持有間隙鎖，且均等待插入意向鎖，因此導致死鎖。
原理
pt-osc
online ddl 不會導致死鎖，兩者最大的區別是 pt-osc 執行 DDL 時產生的主從延遲小，原因是 online ddl 是在主庫執行完成后從庫開始執行，因此天然存在延遲。
為解決這個問題，pt-osc 支持主從“同時”執行，缺點是全量數據和增量數據可能發生亂序。為解決這個問題，將全量數據中 insert 轉換成 insert ignore，將增量數據中 insert / update 轉換成 replace into，從而實現數據的最終一致性，缺點是改寫 SQL 后加鎖變復雜，可能導致死鎖。
replace 語句加鎖流程
參考【許海波】大佬的文章 REPLACE語句死鎖與官方修復剖析，5.7.19 版本中 replace 語句加鎖流程見下圖。
圖片
整理加鎖規則見下表。
索引類型 | 是否沖突 | 加鎖類型 |
主鍵索引 | 否 | 不加鎖 |
是 | next-key lock | |
二級唯一鍵 | 否 | gap lock |
是 | next-key lock |
其中：
- 二級唯一鍵沒有沖突時加 gap lock 最不合理；
- 5.7.26 中針對該問題進行了修復。
在分析修復實現前首先介紹該修復帶來的新問題，具體是 5.7.26 中新增的鎖。
不同版本 insert 加鎖對比
參考以下兩篇文章：
- 【操盛春】大佬的文章 答讀者問：唯一索引沖突，為什么主鍵的 supremum 記錄會加 next-key 鎖？
- 【高鵬】大佬的文章 MySQL:新版本RR模式下特殊的鎖行為一列
其中都提到了一個現象，插入二級唯一鍵時如果發生唯一鍵沖突，內部回滾刪除主鍵時會給回滾記錄加鎖，并在回滾完成后將鎖繼承到下一行，加鎖類型是 gap lock。注意如果下一行是 sup 偽列，加鎖類型是 next-key lock，從而導致大于當前最大主鍵的新插入的主鍵值均無法插入，出現大面積堵塞。
其中的重點是主鍵刪除過程中的以下兩步操作：
- 將隱式鎖轉換成顯式鎖
- 發生鎖繼承
文中提到這個現象是在 5.7.29+ 版本中發現，5.7.22 中沒有該現象。
因此下面測試對比 5.7.24 與 5.7.33 兩個版本中 insert 發生唯一鍵沖突時的加鎖規則，其中使用兩個事務依次模擬主鍵沖突與二級唯一鍵沖突。
由于 insert 沒有唯一鍵沖突時使用隱式鎖，可以認為不加鎖，因此僅測試唯一鍵沖突的場景。
測試數據
drop table `e`;
CREATE TABLE `e` (
`id` int NOT NULL AUTO_INCREMENT,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
insert into e(c,d) values(10,10),(20,20);主鍵沖突
insert into e(id,c,d) values(11,11,11);結果
圖片
其中：
- 主鍵沖突時，5.7.24 next-key lock，5.7.33 record lock。
二級唯一鍵沖突
inser into e(c,d) values(10,10);結果
圖片
其中：
- 二級唯一鍵沖突時，5.7.24 next-key lock，5.7.33 也是 next-key lock；
- 5.7.33 新增主鍵索引上的鎖，具體是 X 型 next-key lock，且加鎖到右邊界 supremum，因此大于等于 12 的主鍵值都無法插入；
- 5.7.33 主鍵索引上新增鎖的原因是主鍵回滾，期間先將隱式鎖轉換成顯式鎖，然后發生鎖繼承；
- 5.7.24 同樣主鍵回滾，但是主鍵索引上沒有鎖的原因是使用隱式鎖，沒有轉換成顯式鎖，因此鎖繼承時不加鎖；
- 因此判斷高版本中針對二級唯一鍵沖突檢查加鎖沒有優化，針對主鍵沖突檢查加鎖粒度減小，針對主鍵回滾加鎖粒度增大。
但是通常主鍵值自增，因此二級索引加鎖粒度變大可能導致死鎖增多。
代碼分析
下面分析 5.7.26 中修改唯一鍵沖突時加鎖規則對應的 commit，具體是 Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK。
相關描述如下所示。
Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK
PROBLEM
-------
When we do a partial rollback of the tuple due to "on duplicate
key update" clause we were not maintaining serilizability,
so another connection waiting on the row could update it and
cause wrong results.
FIX
---
1) During partial rollback ,while deleting the tuple convert implicit
lock on the tuple to explicit lock ,so that no connection can
get hold of the tuple during delete. This lock is later inherited
by the next record.
...其中：
- 問題，insert duplicate 語句在死鎖回滾事務時沒有保證串行，導致回滾期間其他鎖等待事務可以更新數據；
- 修復，將 delete 持有的隱式鎖轉換成顯式鎖，并隨后發生鎖繼承，從而阻塞其他事務在回滾期間更新數據。
release notes 中顯示 5.7.26 中該 commit 用于修復 bug，但是暫未根據 commit 找到對應要修復的 bug。
Two sessions concurrently executing an INSERT ... ON DUPLICATE KEY UPDATE operation generated a deadlock. During partial rollback of a tuple, another session could update it. The fix for this bug reverts fixes for Bug #11758237, Bug #17604730, and Bug #20040791. (Bug #25966845)
下面分析該 commit 中的代碼實現。
最明顯的是新增row_convert_impl_to_expl_if_needed函數，相關代碼如下所示。
首先是row_convert_impl_to_expl_if_needed函數的定義。
void
row_convert_impl_to_expl_if_needed(
/*===============================*/
btr_cur_t* cursor, /*!< in: cursor to record */
undo_node_t* node) /*!< in: undo node */
{
ulint* offsets = NULL;
// IODKU 表示 insert duplicate
/* In case of partial rollback implicit lock on the
record is released in the middle of transaction, which
can break the serializability of IODKU and REPLACE
statements. Normal rollback is not affected by this
becasue we release the locks after the rollback. So
to prevent any other transaction modifying the record
in between the partial rollback we convert the implicit
lock on the record to explict. When the record is actually
deleted this lock be inherited by the next record. */
// 判讀事務隔離級別
if (!node->partial
|| (node->trx == NULL)
|| node->trx->isolation_level < TRX_ISO_REPEATABLE_READ){
return;
}
...
// 將隱式鎖轉換成顯式鎖
lock_rec_convert_active_impl_to_expl(block, rec, index,
offsets,node->trx,heap_no);
}其中：
- RR 事務隔離級別下調用lock_rec_convert_active_impl_to_expl函數將隱式鎖轉換成顯式鎖；
- RC 事務隔離級別下不轉換。
然后是row_convert_impl_to_expl_if_needed函數的調用。
圖片
其中：
- 刪除主鍵與二級索引時都調用row_convert_impl_to_expl_if_needed函數，包括 insert、insert duplicate、replace。
到目前為止，鎖的粒度在增大，但實際上并非始終如此，具體在唯一性檢查時鎖的粒度有減小。
首先是主鍵唯一性檢查前加鎖，對比 5.7.24 與 5.7.26 代碼中row_ins_duplicate_error_in_clust函數。
圖片
其中：
- 對于 RR，5.7.24，lock_type = next-key lock；
- 5.7.26，lock_type = record lock，包括 insert、insert duplicate、replace。
因此主鍵索引加鎖粒度減小，具體是從 next-key lock 減小為 record lock，也就是移除了 gap lock。
然后是二級唯一鍵唯一性檢查前加鎖，對比 5.7.24 與 5.7.26 代碼中row_ins_sec_index_entry_low函數。
圖片
其中：
- 唯一鍵沖突時，加鎖類型不變，保持 next-key lock，包括 insert、insert duplicate、replace；
- 唯一鍵不沖突時，5.7.24 中 insert 不加鎖，insert duplicate / replace 加 gap lock，5.7.26 中統一不加鎖。
因此對于 insert 語句，唯一鍵沖突時，5.7.26 中未必鎖減少，下面測試對于 replace 語句，加鎖規則的變化。
不同版本 replace 加鎖對比
下表中對比 insert 與 insert on duplicate 的加鎖規則。
場景 | 唯一鍵不沖突 | 唯一鍵沖突 |
insert | 隱式鎖 | S 型 next-key lock |
insert on duplicate（5.7.26-） | X 型 gap lock | X 型 next-key lock |
insert on duplicate（5.7.26+） | 不加鎖 | X 型 next-key lock |
其中：
- 主要改動點是將唯一鍵不沖突時的間隙鎖移除，下面測試驗證。
操作流程
session 1 | session 2 |
begin; replace into t_lock values(2,2,2,2); | |
begin; replace into t_lock values(3,3,3,3); |
5.7.33 二級唯一鍵不沖突時不加鎖。
圖片
因此，5.7.33 沒有沖突時不加鎖，有沖突時加鎖不變，且主鍵回滾后鎖粒度增大。
總結
回顧 insert 二級唯一鍵沖突后的異常處理，堆棧如下所示。
// 判斷是否報錯，唯一鍵沖突時 err = DB_DUPLICATE_KEY
if (err != DB_SUCCESS)
// 處理異常
row_mysql_handle_errors
// 插入記錄導致唯一索引沖突，需要回滾
trx_rollback_to_savepoint
// 回滾 insert 操作
row_undo_ins
// 刪除主鍵索引
row_undo_ins_remove_clust_rec
// 5.7.24 中沒有該函數，5.7.26 中新函數
row_convert_impl_to_expl_if_needed
// 把主鍵索引記錄上的隱式鎖轉換為顯式鎖
lock_rec_convert_impl_to_expl
// 先進行樂觀刪除，如果樂觀刪除失敗，后面會進行悲觀刪除
btr_cur_optimistic_delete
// 鎖繼承
lock_rec_inherit_to_gap
// 真正刪除
page_cur_delete_rec其中：
- 刪除主鍵前先將要刪除記錄上的隱式鎖轉換成顯式鎖，作為過渡，用于下一步的鎖轉移；
- 然后在真正刪除前將要刪除記錄上的鎖轉移到下一行記錄，加鎖類型為 gap lock，其中如果下一行是 sup 偽列，加鎖類型修改為 next-key lock。
總結下 5.7.26 中唯一性檢查加鎖規則的調整：
- 主鍵索引，沒有唯一鍵沖突時不加鎖，有唯一鍵沖突時移除了 gap lock，不論隔離級別，全部加 record lock，而不是 next-key lock；
- 二級唯一索引，沒有唯一鍵沖突時不加鎖，不論隔離級別，移除了 gap lock，有唯一鍵沖突時保持不變，不論隔離級別，依然是 next-key lock；
- 刪除主鍵索引與二級索引時都需要先將插入時的隱式鎖轉換成顯式鎖，因此在刪除前鎖繼承時將產生 gap lock，從而阻塞并發插入。
到這里，可以提出一個問題，那就是加鎖規則的調整可以保證數據唯一嗎？
參考 #issue 68021 MySQL unique check 的問題，首先解釋低版本中加 next-key lock 的原因。
原因是二級唯一索引插入記錄時，分為兩個階段，包括唯一性檢查與插入操作，因此通過在兩階段之間加鎖保證操作的原子性，禁止其他事務在同一間隙中插入數據，從而避免檢查沒有沖突，但是插入時出現沖突。
文中介紹針對二級索引加 next-key lock 的問題，官方之前做過改動，具體將 next-key lock 改成 record lock，但是導致唯一性約束失效的嚴重 bug#73170，因此后來又將該 fix revert。
具體如下圖所示，其中紅色表示 record 已經被刪除，藍色表示未被刪除。tuple 中第一個元素表示二級唯一鍵，第二個元素表示主鍵。
圖片
其中：
- 假如同時插入兩個 record (13000, 99)、( 13000, 120)，如果將 next-key lock 改成 record lock；
- 唯一性檢查時分別對 (13000, 100)、(13000, 102)、(13000, 108) ... (13000, 112) 所有的二級索引加 S record lock，insert 時對 (13000, 100) 加 GAP | insert_intention lock；
- 由于鎖不沖突，因此這兩個 record 都可以同時插入成功，就造成了unique key 約束失效了。
因此，5.7.26 中針對二級唯一鍵沖突的場景，加鎖規則不變，依然是 next-key lock，因此不會導致唯一性約束失效。
此外，主鍵唯一性檢查時不需要 gap lock 的原因是理論上同一時間二級唯一鍵可能有重復，而主鍵不會有重復。
原因是二級索引與主鍵索引唯一性檢查與記錄復用判斷的標準不同：
- 二級索引刪除后，再次插入相同唯一鍵時如果主鍵不同，原記錄不可以復用。為了滿足 MVCC 的需求，delete-marked record 不能馬上刪除，因此理論上可能有重復的唯一鍵；
- 主鍵索引刪除后，再次插入相同主鍵時可以直接服用原記錄。為了滿足 MVCC 的需求，delete-marked record 可以從 undo log 中查到，因此理論上不會有重復的主鍵。
結論
死鎖的根本原因是 replace 語句在二級唯一鍵不沖突時申請 gap lock。
針對該問題，5.7.26 進行了優化，具體為：
- 主鍵索引，沒有唯一鍵沖突時不加鎖，有唯一鍵沖突時移除了 gap lock，不論隔離級別，全部加 record lock，而不是 next-key lock；
- 二級唯一索引，沒有唯一鍵沖突時不加鎖，不論隔離級別，移除了 gap lock，有唯一鍵沖突時保持不變，不論隔離級別，依然是 next-key lock；
- 刪除主鍵索引與二級索引時都需要先將插入時的隱式鎖轉換成顯式鎖，因此在刪除前鎖繼承時將產生 gap lock，從而阻塞并發插入。
其中前兩條減小了加鎖粒度，最后一條增大了加鎖粒度，commit 顯示是為了修復 bug，但是具體 bug 暫未找到。
低版本中二級索引唯一性檢查加 next-key lock 的原因是為了保證唯一性檢查與插入操作兩個階段之間操作的原子性。
5.7.26 版本中同樣唯一鍵沖突時，主鍵索引與二級索引加鎖類型不同的原因是：
- 主鍵索引，record lock，原因是理論上不會有重復的主鍵；
- 二級索引，next-key lock，理論上可能有重復的二級唯一鍵。
參考教程
- REPLACE語句死鎖與官方修復剖析
https://zhuanlan.zhihu.com/p/527813412
- 答讀者問：唯一索引沖突，為什么主鍵的 supremum 記錄會加 next-key 鎖？
- MySQL:新版本RR模式下特殊的鎖行為一列
- Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK
https://github.com/mysql/mysql-server/commit/066b6fdd433aa6673622341f1a2f0a3a20018043
- #issue 68021 MySQL unique check 的問題
- Replace into加鎖的探究
https://www.jianshu.com/p/497fd78f0b91
- 并發 replace into 導致 supremum X 鎖與插入意向鎖形成死鎖
- pt-online-schema-change的原理解析與應用說明
https://www.cnblogs.com/xinysu/p/6758170.html
