作者：李錫超，一個愛笑的江蘇蘇寧銀行 數據庫工程師，主要負責數據庫日常運維、自動化建設、DMP 平臺運維。擅長 MySQL、Python/ target=_blank class=infotextkey>Python、Oracle，愛好騎行、研究技術。
愛可生開源社區出品
本文約 2100 字，預計閱讀需要 7 分鐘。
1問題現象
自發布了 INSERT 并發死鎖問題的文章，收到了多次死鎖問題的交流。一個具體案例如下：
研發反饋應用發生死鎖，收集如下診斷內容：
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-07-04 06:02:40 0x7fc07dd0e700
*** (1) TRANSACTION:
TRANSACTION 182396268, ACTIVE 0 sec fetching rows
mysql tables inuse 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 3520, 2 row lock(s), undo logentries 1
MySQL thread id 59269692, OS thread handle 140471135803136, query id 3738514953 192.168.0.215 user1 updating
delete from ltb2 wherec = 'CCRSFD07E'and j = 'Y15'and b >= '20230717'and d != '1'and e != '1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2`trx id 182396268 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 182396266, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1729
mysql tables inuse 1, locked 1
28 lock struct(s), heap size 3520, 2 row lock(s), undo logentries 1
MySQL thread id 59261188, OS thread handle 140464721291008, query id 3738514964 192.168.0.214 user1 updating
update ltb2 setf = '0', g = '0', is_value_date = '0', h = '0', i = '0'wherec = '22115001B'and j = 'Y4'and b >= '20230717'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2`trx id 182396266 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2`trx id 182396266 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------
基本環境信息以上 space id 603 page no 86 n bits 248，其中 space id 表示表空間 ID，page no 表示記錄鎖在表空間內的哪一頁，n bits 是鎖位圖中的位數，而不是頁面偏移量。記錄的頁偏移量一般以 heap no 的形式輸出，但此例并未輸出該信息。
確認如下問題相關信息：
-
數據庫版本：Percona MySQL 5.7
-
事務隔離級別：Read-Commited
-
表結構和索引：
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`j` varchar(16) DEFAULT NULL COMMENT '',
`c` varchar(32) NOT NULL DEFAULT ''COMMENT '',
`b` date NOT NULL DEFAULT '2019-01-01'COMMENT '',
`f` varchar(1) NOT NULL DEFAULT ''COMMENT '',
`g` varchar(1) NOT NULL DEFAULT ''COMMENT '',
`d` varchar(1) NOT NULL DEFAULT ''COMMENT '',
`e` varchar(1) NOT NULL DEFAULT ''COMMENT '',
`h` varchar(1) NOT NULL DEFAULT ''COMMENT '',
`i` varchar(1) DEFAULT NULL COMMENT '',
`LAST_UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
PRIMARY KEY (`ID`),
UNIQUE KEY `uidx_1` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=270983 DEFAULT CHARSET=utf8mb4 COMMENT='';
關鍵信息梳理
| 事務 T1 | |
|---|---|
| 語句 | delete from ltb2 where c = 'code001' and j = 'Y15' and b >= '20230717' and d != '1' and e != '1' |
| 關聯對象及記錄 | space id 603 page no 86 n bits 248 index PRIMARY of table testdb.ltb2 |
| 持有的鎖 | 未知 |
| 等待的鎖 | lock_mode X locks rec but not gap waiting |
| 事務 T2 | |
|---|---|
| 語句 | update ltb2 set f = '0', g = '0', is_value_date = '0', h = '0', i = '0' where c = '22115001B' and j = 'Y4' and b >= '20230717' |
| 關聯對象及記錄 | space id 603 page no 86 n bits 248 index PRIMARY of table testdb.ltb2 |
| 持有的鎖 | lock_mode X locks rec but not gap |
| 等待的鎖 | lock_mode X locks rec but not gap waiting |
可以看到在主鍵索引上發生了死鎖，但是在查詢的條件中，并未使用主鍵列。
那為什么會在主鍵列出現死鎖？在分析死鎖根因問題前，需要先清楚 SQL 的執行情況。
2SQL 執行情況執行計劃
以上兩個 SQL 發現都有列 b、c 作為條件，且該列構成了索引唯一索引 uidx_1。簡化 SQL 改為查詢語句，并確認執行計劃：
mysql> desc select* fromltb2 whereb >= '20230717'andc = 'code001';
# 部分結果
+----- -+-------------------+------+---------+
| type | possible_keys | key | Extra |
+----- -+-------------------+------+---------+
| ALL | uidx_1 | NULL | Using where |
+----- -+-------------------+------+---------+
注意：自 MySQL 5.6 開始可以直接查看 UPDATE/DELETE/INSERT 等語句的執行計劃。因個人習慣、避免誤操作等原因，還是習慣改為 SELECT 查看執行計劃。
執行計劃中可能的索引有 uidx_1(b,c)，但實際并未使用該索引，而是采用全表掃描方式執行。
根據經驗，由于列 b 為索引的最左列。但查詢的條件為 b>= '20230717'，即該條件不是等值查詢。因此數據庫可能只能“使用”到 b 列。為進一步確認不使用 b 列索引的原因，查詢數據分布：
mysql> selectcount(1) fromltb2;
+------------+
| count(1) |
+------------+
| 4509 |
+------------+
mysql> selectcount(1) fromltb2 whereb >= '20230717';
+------------+
| count(1) |
+------------+
| 1275 |
+------------+
計算滿足 b 列條件的數據占比為 1275/4509 = 28%，占比差不多達到了 1/3。此時也的確不應使用該使用索引。
難道已經是作為 MySQL 5.7 的數據庫，優化器還是這么簡單？
ICP 特性
帶著問題，將條件設置一個更大的值（但小于該列的最大值），再次執行驗證查詢語句：
mysql> desc select* fromltb2 whereb >= '20990717';
# 部分結果
+----------+---------+---------+
| key_len | rows | Extra |
+----------+---------+---------+
| 3 | 64 | Using Index condition |
+----------+---------+---------+
優化器預估返回 64 行，數據占比 64/4509 = 1.4%，因此可以使用索引。但通過執行計劃，從 Extra 列看到 Using index condition 提示。該提示則說明使用了索引條件下推（Index Condition Pushdown, ICP）。針對該特性，參考官方簡要說明如下：
使用 Index Condition Pushdown，掃描將像這樣進行：
-
獲取下一行的索引元組（但不是完整的表行）。
-
測試 WHERE 條件中應用于此表的部分，并且只能使用索引列的進行檢查。如果不滿足條件，則繼續到下一行的索引元組。
-
如果滿足條件，則使用索引元組定位并讀取整個表行。
-
測試適用于此表的 WHERE 條件的其余部分。根據測試結果接受或拒絕該行。
既然可以使用到 ICP 特性，進一步執行如下驗證語句：
mysql> desc select* fromltb2 whereb >= '20990717'andc = 'code001';
# 部分結果
+----------+---------+---------+
| key_len | rows | Extra |
+----------+---------+---------+
| 133 | 64 | Using Index condition |
+----------+---------+---------+
發現當新增 c 列作為條件后，并且根據 key_len（索引里使用的字節數）可以判斷，的確使用到了 uidx_1 索引中的 c 列。但 rows 的結果與實際返回結果差異較大（實際執行僅返回 0 行）。
更重要的是，既然具有 ICP 特性，針對原始的 SQL 為什么不能助于 ICP 特性使用到索引呢？
mysql> select * from ltb2 whereb >= '20230717'and c = 'code001'
執行計劃跟蹤
繼續帶著問題，通過 MySQL 提供的 OPTIMIZER TRACE，跟蹤執行計劃生成過程。命令如下：
SETOPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SETOPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
-- sql-1:
select* fromltb2 whereb >= '20990717'andc = 'code001';
-- sql-2:
select* fromltb2 whereb >= '20990717';
-- sql-3
select* fromltb2 whereb >= '20230717'andc = 'code001';
SELECT* FROMINFORMATION_SCHEMA.OPTIMIZER_TRACEG
SEToptimizer_trace="enabled=off";
由于分析結果較長，截取 SQL-1 和 SQL-2 的部分結果 (rows_estimation 和 considered_execution_plans)。具體內容如下：
SQL-1select * from ltb2 whereb >= '20990717'and c = 'code001'
# 分析結果
"analyzing_range_alternatives":{
"range_scan_alternatives":[
{
"index":"uidx_1",
"ranges":[
"0xe76610 <= b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows":64,
"cost": 77.81,
"chosen": true
}
] /* range_scan alternatives */
}
"best_access_path":{
"considered access_paths":[
"rows_to_scan": 64,
"access_type":"range",
"range_details":{
"used index";"uidx 1"
} /* range_details */,
"resulting_rows": 64,
"cost": 90.61,
"chosen": true
}
] /* considered access_paths */
} /* best access_path */,
SQL-2select * from ltb2 whereb >= '20990717'
# 分析結果
"analyzing_range_alternatives":{
"range_scan_alternatives":[
{
"index":"uidx_1",
"ranges":[
"0xe76610 <= b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows":64,
"cost": 77.81,
"chosen": true
}
] /* range_scan alternatives */
}
"considered access_paths":[
{
"rows_to_scan": 64,
"access_type":"range",
"range_details":{
"used index":"uidx_1"
} /* range_details */,
"resulting_rows": 64,
"cost": 90.61,
"chosen": true
}
] /* considered access_paths */,
根據以上信息：兩個 SQL 的 cost 部分是完全相同的，且在優化器分析階段只能識別到 b 的條件。分析階段，只能根據優化器認為可用的列來計算 cost。ICP 特性，應該是在執行階段采用用到的特性。
同時，根據 SQL-3 的執行跟蹤結果，對比全表掃描和索引掃描的 cost，截取部分結果如下：
SQL-3select * from ltb2 whereb >= '20230717'and c = 'code001';
# 全表掃描結果
"range_analysis": {
"table _scan": {
"rows": 4669，
"cost": 1018.9
} /* table_scan */，
# 索引掃描評估結果
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index":"uidx_1",
"ranges":[
"@xe7ce0f] <= b"
] /* ranges */,
"index dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
" rows": 1273,
"cost": 1528.6,
"chosen": false,
"cause":"cost"
}
] /* range scan_alternatives */,
# 最優執行計劃
"best_access_path": {
"considered access_paths":[
{
"rows_to_scan": 4669,
"access_type":"scan",
"resulting_rows": 4669,
"cost": 1016.8,
"chosen": true
}
] /* considered access_paths *//* best access_path */
}
由于優化器階段使用使用列 b，使用索引的成本高于全表掃描。那最終數據庫就會選擇使用全表掃描。除非應用使用 hint 強制索引：
mysql> desc select * from ltb2 FORCE INDEX (uidx_1) whereb >= '20230717'and c = 'code001';
# 部分結果
+----------+---------+---------+
| key_len | rows | Extra |
+----------+---------+---------+
| 133 | 1273 | Using Index condition |
+----------+---------+---------+
同時，根據執行計劃的輸出結果，rows 列應該是優化器階段的輸出，key_len/Extra 則包括了執行階段的輸出。
小結
綜上所述，對于問題 SQL 和索引結構，由于列 b 為索引的最左列，且查詢時的條件為 b>= '20230717'（非等值條件），數據庫優化器只能“使用”到 b 列。并給予“使用”的列，評估掃碼的行數和 cost。
如果優化器評估后，使用索引的成本更低，則可以使用該索引，并利用 ICP 特性進一步提高查詢性能；
如果優化器評估后，使用全表掃描或的成本更低，那數據庫就會選擇使用全表掃描。
3SQL 優化方案
根據第 2 部分明確了問題的原因后，通過調整索引，解決最左列尾范圍查詢的問題即可解決該問題。具體如下：
altertableltb2 dropindexuidx_1;
altertableltb2 addindexuidx_1(c,b);
altertableltb2 addindexidx_(b);
死鎖為何發生
自此，完成了 SQL 執行計劃問題的分析和解決。但直接的問題是死鎖，因查詢語句無法使用索引，正常就應該使用全表掃描。但是全表掃描為什么會出現死鎖呢？
在此，參考《故障分析 | 從 Insert 并發死鎖分析 Insert 加鎖源碼邏輯》的經驗，對死鎖過程進行大膽猜想：
T1 時刻
trx-2 執行了 UPDATE，在處理行時，在 row_search_mvcc 函數中，查詢到數據。獲取了對應行的 LOCK_X,LOCK_REC_NOT_GAP 鎖；
T2 時刻
trx-1 執行了 DELETE，在處理行時，在 row_search_mvcc 函數中，查詢到數據，嘗試獲取行的 LOCK_X,LOCK_REC_NOT_GAP。但由于 trx-1 已經持有了該鎖，因此被堵塞。并會創建一個鎖（以指示鎖等待）；
T3 時刻
trx-2 繼續執行 UPDATE 操作。由于是該操作除了在 T1 時刻的操作外，在其它位置，還需要獲取鎖（lock_mode X locks rec but not gap）。但由于 T2 時刻，trx-1 嘗試獲取該鎖而被堵塞，并且也增加了一個鎖。
假如此時，此處的實現機制和 INSERT 死鎖案例一樣，也沒有先進行沖突檢查。而只是看記錄上是否存在鎖的話，那么此時也會看到該記錄上有 trx-1 事務的鎖。從而導致 trx-2 第二次獲取鎖時，被堵塞。
死鎖發生！
以上僅根據經驗進行的猜想，真正的原因還需要進一步分析和驗證。有興趣的讀者結合如下幾個問題，進一步研究。
-
以上各步驟獲取鎖的位置，是否正確？
-
T3 時刻，update操作在其它的什么位置再次獲取了鎖？
-
T3 時刻，發起的假設是否成立？如成立，具體邏輯是什么？不成立，那正確的邏輯是什么？
-
T3 時刻，如果假設不成立，那死鎖的原因又是什么？
-
以上都是針對于唯一索引/主鍵索引的執行邏輯分析的。那結合該案例，全表掃描和索引查詢的執行邏輯是否存在差異？差異的地方在哪里？
-
除了調整索引，還能通過什么方式避免該問題發生？
