1 問題現象

自發布了 INSERT 并發死鎖問題的文章，收到了多次死鎖問題的交流。一個具體案例如下：

研發反饋應用發生死鎖，收集如下診斷內容：

------------------------
LATESTDETECTEDDEADLOCK
------------------------
2023-07-0406400x7fc07dd0e700
***(1)TRANSACTION:
TRANSACTION182396268,ACTIVE0secfetchingrows
mysqltablesinuse1,locked1
LOCKWAIT21lockstruct(s),heapsize3520,2rowlock(s),undologentries1
MySQLthreadid59269692,OSthreadhandle140471135803136,queryid3738514953192.168.0.215user1updating
deletefromltb2wherec='CCRSFD07E'andj='Y15'andb>='20230717'andd!='1'ande!='1'
***(1)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid603pageno86nbits248indexPRIMARYoftable`testdb`.`ltb2`trxid182396268lock_modeXlocksrecbutnotgapwaiting
***(2)TRANSACTION:
TRANSACTION182396266,ACTIVE0secfetchingrows,threaddeclaredinsideInnoDB1729
mysqltablesinuse1,locked1
28lockstruct(s),heapsize3520,2rowlock(s),undologentries1
MySQLthreadid59261188,OSthreadhandle140464721291008,queryid3738514964192.168.0.214user1updating
updateltb2setf='0',g='0',is_value_date='0',h='0',i='0'wherec='22115001B'andj='Y4'andb>='20230717'
***(2)HOLDSTHELOCK(S):
RECORDLOCKSspaceid603pageno86nbits248indexPRIMARYoftable`testdb`.`ltb2`trxid182396266lock_modeXlocksrecbutnotgap
***(2)WAITINGFORTHISLOCKTOBEGRANTED:
RECORDLOCKSspaceid603pageno86nbits248indexPRIMARYoftable`testdb`.`ltb2`trxid182396266lock_modeXlocksrecbutnotgapwaiting
***WEROLLBACKTRANSACTION(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

表結構和索引：

CREATETABLE`ltb2`(
`ID`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID',
`j`varchar(16)DEFAULTNULLCOMMENT'',
`c`varchar(32)NOTNULLDEFAULT''COMMENT'',
`b`dateNOTNULLDEFAULT'2019-01-01'COMMENT'',
`f`varchar(1)NOTNULLDEFAULT''COMMENT'',
`g`varchar(1)NOTNULLDEFAULT''COMMENT'',
`d`varchar(1)NOTNULLDEFAULT''COMMENT'',
`e`varchar(1)NOTNULLDEFAULT''COMMENT'',
`h`varchar(1)NOTNULLDEFAULT''COMMENT'',
`i`varchar(1)DEFAULTNULLCOMMENT'',
`LAST_UPDATE_TIME`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改時間',
PRIMARYKEY(`ID`),
UNIQUEKEY`uidx_1`(`b`,`c`)
)ENGINE=InnoDBAUTO_INCREMENT=270983DEFAULTCHARSET=utf8mb4COMMENT='';

關鍵信息梳理

	事務 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 的執行情況。

2 SQL 執行情況

執行計劃

以上兩個 SQL 發現都有列 b、c 作為條件，且該列構成了索引唯一索引 uidx_1。簡化 SQL 改為查詢語句，并確認執行計劃：

mysql>descselect*fromltb2whereb>='20230717'andc='code001';

#部分結果
+------+-------------------+------+---------+
|type|possible_keys|key|Extra|
+------+-------------------+------+---------+
|ALL|uidx_1|NULL|Usingwhere|
+------+-------------------+------+---------+

注意：自 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)fromltb2whereb>='20230717';

+------------+
|count(1)|
+------------+
|1275|
+------------+

計算滿足 b 列條件的數據占比為 1275/4509 = 28%，占比差不多達到了 1/3。此時也的確不應使用該使用索引。

難道已經是作為 MySQL 5.7 的數據庫，優化器還是這么簡單？

ICP 特性

帶著問題，將條件設置一個更大的值（但小于該列的最大值），再次執行驗證查詢語句：

mysql>descselect*fromltb2whereb>='20990717';

#部分結果
+----------+---------+---------+
|key_len|rows|Extra|
+----------+---------+---------+
|3|64|UsingIndexcondition|
+----------+---------+---------+

優化器預估返回 64 行，數據占比 64/4509 = 1.4%，因此可以使用索引。但通過執行計劃，從Extra列看到Using index condition提示。該提示則說明使用了索引條件下推（Index Condition Pushdown, ICP）。針對該特性，參考官方簡要說明如下：

使用 Index Condition Pushdown，掃描將像這樣進行：

獲取下一行的索引元組（但不是完整的表行）。

測試 WHERE 條件中應用于此表的部分，并且只能使用索引列的進行檢查。如果不滿足條件，則繼續到下一行的索引元組。

如果滿足條件，則使用索引元組定位并讀取整個表行。

測試適用于此表的 WHERE 條件的其余部分。根據測試結果接受或拒絕該行。

既然可以使用到 ICP 特性，進一步執行如下驗證語句：

mysql>descselect*fromltb2whereb>='20990717'andc='code001';

#部分結果
+----------+---------+---------+
|key_len|rows|Extra|
+----------+---------+---------+
|133|64|UsingIndexcondition|
+----------+---------+---------+

發現當新增 c 列作為條件后，并且根據 key_len（索引里使用的字節數）可以判斷，的確使用到了 uidx_1 索引中的 c 列。但 rows 的結果與實際返回結果差異較大（實際執行僅返回 0 行）。

更重要的是，既然具有 ICP 特性，針對原始的 SQL 為什么不能助于 ICP 特性使用到索引呢？

mysql>select*fromltb2whereb>='20230717'andc='code001'

執行計劃跟蹤

繼續帶著問題，通過 MySQL 提供的 OPTIMIZER TRACE，跟蹤執行計劃生成過程。命令如下：

SETOPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SETOPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
--sql-1:
select*fromltb2whereb>='20990717'andc='code001';
--sql-2:
select*fromltb2whereb>='20990717';
--sql-3
select*fromltb2whereb>='20230717'andc='code001';

SELECT*FROMINFORMATION_SCHEMA.OPTIMIZER_TRACEG
SEToptimizer_trace="enabled=off";

由于分析結果較長，截取 SQL-1 和 SQL-2 的部分結果 (rows_estimation 和 considered_execution_plans)。具體內容如下：

SQL-1

select*fromltb2whereb>='20990717'andc='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-2

select*fromltb2whereb>='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-3

select*fromltb2whereb>='20230717'andc='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>descselect*fromltb2FORCEINDEX(uidx_1)whereb>='20230717'andc='code001';

#部分結果
+----------+---------+---------+
|key_len|rows|Extra|
+----------+---------+---------+
|133|1273|UsingIndexcondition|
+----------+---------+---------+

同時，根據執行計劃的輸出結果，rows 列應該是優化器階段的輸出，key_len/Extra 則包括了執行階段的輸出。

小結

綜上所述，對于問題 SQL 和索引結構，由于列 b 為索引的最左列，且查詢時的條件為 b>= '20230717'（非等值條件），數據庫優化器只能“使用”到 b 列。并給予“使用”的列，評估掃碼的行數和 cost。

如果優化器評估后，使用索引的成本更低，則可以使用該索引，并利用 ICP 特性進一步提高查詢性能；

如果優化器評估后，使用全表掃描或的成本更低，那數據庫就會選擇使用全表掃描。

3 SQL 優化方案

根據第 2 部分明確了問題的原因后，通過調整索引，解決最左列尾范圍查詢的問題即可解決該問題。具體如下：

altertableltb2dropindexuidx_1;
altertableltb2addindexuidx_1(c,b);
altertableltb2addindexidx_(b);

死鎖為何發生

自此，完成了 SQL 執行計劃問題的分析和解決。但直接的問題是死鎖，因查詢語句無法使用索引，正常就應該使用全表掃描。但是全表掃描為什么會出現死鎖呢？

在此，對死鎖過程進行大膽猜想：

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 第二次獲取鎖時，被堵塞。

死鎖發生！

審核編輯：劉清