深入剖析SQL死鎖-兩條SQL之間的死鎖原因
作者：陳凱
在業(yè)務(wù)實(shí)現(xiàn)中，若數(shù)據(jù)存在則修改，不存在則插入，通常大家會選用 INSERT INTO... ON DUPLICATE KEY UPDATE 語句讓數(shù)據(jù)庫實(shí)現(xiàn)此功能。近期在進(jìn)行開發(fā)批量取消預(yù)約物流的場景中，由于一個(gè)物流對應(yīng)多個(gè)訂單，同時(shí)取消多個(gè)物流時(shí)，接口可能會變慢。
1.問題背景
在業(yè)務(wù)實(shí)現(xiàn)中，若數(shù)據(jù)存在則修改，不存在則插入，通常大家會選用 INSERT INTO... ON DUPLICATE KEY UPDATE 語句讓數(shù)據(jù)庫實(shí)現(xiàn)此功能。近期在進(jìn)行開發(fā)批量取消預(yù)約物流的場景中，由于一個(gè)物流對應(yīng)多個(gè)訂單，同時(shí)取消多個(gè)物流時(shí)，接口可能會變慢。故采用了多線程，每個(gè)物流取消任務(wù)對應(yīng)一個(gè)線程。然而，在測試過程中出現(xiàn)了意外問題，當(dāng)同時(shí)取消兩個(gè)物流單時(shí)發(fā)生了數(shù)據(jù)庫死鎖。
2.問題復(fù)現(xiàn)
2.1 SHOW ENGINE INNODB STATUS尋找死鎖語句
通過SHOW ENGINE INNODB STATUS命令獲取MySQL的死鎖日志。
MySQL版本5.7
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-12-05 21:18:45 0x7fecb4759700
*** (1) TRANSACTION:
TRANSACTION 1366772472, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3
MySQL thread id 7821432, OS thread handle 140654140307200, query id 1454780802 192.168.26.25 vault-0iPqpD update
insert into recycle_order_extend (id, recycle_order_id, param_id,
value_ids, es_type) values
(
376510847366725657, 376473627618443479, 100031,
'[]', 1
)
,
(
376510847369871385, 376473627618443479, 100030,
'[]', 1
)
on duplicate key update value_ids=values(`value_ids`), update_time=values(`update_time`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6314 page no 4758 n bits 192 index PRIMARY of table `dbzz_hunter_partner`.`recycle_order_extend` trx id 1366772472 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 000019d6cf63; asc c;;
2: len 7; hex aa000340390eea; asc @9 ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1366772473, ACTIVE 1 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 8
MySQL thread id 7821433, OS thread handle 140654616614656, query id 1454780841 192.168.26.25 vault-0iPqpD update
insert into recycle_order_extend (id, recycle_order_id, param_id,
value_ids, es_type) values
(
376510847508283417, 376473608578400471, 100031,
'[]', 1
)
,
(
376510847509331993, 376473608578400471, 100030,
'[]', 1
)
on duplicate key update value_ids=values(`value_ids`), update_time=values(`update_time`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6314 page no 4758 n bits 192 index PRIMARY of table `dbzz_hunter_partner`.`recycle_order_extend` trx id 1366772473 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 000019d6cf63; asc c;;
2: len 7; hex aa000340390eea; asc @9 ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6314 page no 4758 n bits 192 index PRIMARY of table `dbzz_hunter_partner`.`recycle_order_extend` trx id 1366772473 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 000019d6cf63; asc c;;
2: len 7; hex aa000340390eea; asc @9 ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (1)通過日志很容易的找到產(chǎn)生死鎖的SQL語句。
在事務(wù)A執(zhí)行回收單號為376473627618443479對應(yīng)的數(shù)據(jù)修改時(shí)，會先處理單號376473601396703447對應(yīng)的數(shù)據(jù)（通過SQL語句打印得知執(zhí)行順序），所以是如下執(zhí)行流程。
事務(wù)A:
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473601396703447, 100030, '[]', 1 ),
( 376510847369871385, 376473601396703447, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();
----------------------------------------------------------------------------------------------------
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847508283417, 376473608578400471, 100030, '[]', 1 ),
( 376510847509331993, 376473608578400471, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();
事務(wù)B:
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473627618443479, 100030, '[]', 1 ),
( 376510847369871385, 376473627618443479, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();2.2 問題復(fù)現(xiàn)
通過上面的日志找到了產(chǎn)生死鎖的SQL語句，那么我們就將測試數(shù)據(jù)導(dǎo)入到本地（本地?cái)?shù)據(jù)庫8.0版本），嘗試復(fù)現(xiàn)問題。
步驟 | 事務(wù)A | 事務(wù)B |
1 | begin; | begin; |
2 | INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type ) VALUES ( 376510847366725657, 376473601396703447, 100030, '[]', 1 ), ( 376510847369871385, 376473601396703447, 100031, '[]', 1 ) ON DUPLICATE KEY UPDATE value_ids=values( | - |
3 | - | INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type ) VALUES ( 376510847366725657, 376473627618443479, 100030, '[]', 1 ), ( 376510847369871385, 376473627618443479, 100031, '[]', 1 ) ON DUPLICATE KEY UPDATE value_ids=values( |
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type ) VALUES ( 376510847508283417, 376473608578400471, 100030, '[]', 1 ), ( 376510847509331993, 376473608578400471, 100031, '[]', 1 ) ON DUPLICATE KEY UPDATE value_ids=values( | - |
現(xiàn)象是事務(wù)A會把事務(wù)B進(jìn)行阻塞，并不會死鎖。
獲取當(dāng)前鎖的占用情況；
SELECT engine_transaction_id,index_name,lock_type,lock_mode,lock_status,lock_data FROM performance_schema.data_locks;
圖片
發(fā)現(xiàn)事務(wù)B會因?yàn)槭聞?wù)A持有的間隙鎖導(dǎo)致自己需要執(zhí)行的插入意向鎖獲取失敗，進(jìn)而進(jìn)行阻塞。
3.問題思考
通過上述的問題驗(yàn)證，發(fā)現(xiàn)根本無法復(fù)現(xiàn)問題，因?yàn)槭聞?wù)A一定會阻塞事務(wù)B，但是為什么又會出現(xiàn)死鎖的問題呢？
于是編寫本地測試用例，同時(shí)啟動(dòng)兩個(gè)線程模仿修改表的任務(wù)，發(fā)現(xiàn)確實(shí)會死鎖，并且這次的死鎖現(xiàn)象更奇怪，僅僅A線程和B線程各執(zhí)行了一條SQL就產(chǎn)生了死鎖，并且持有鎖和等待鎖都是RECORD LOCKS space id 98 page no 5231 n bits 152 index PRIMARY。
MySQL8.0版本
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-12-25 15:09:06 0x16c60f000
*** (1) TRANSACTION:
TRANSACTION 195596, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 51, OS thread handle 6136246272, query id 403 localhost 127.0.0.1 root update
insert into recycle_order_extend (id, recycle_order_id, param_id,
value_ids, es_type) values
(
376510847366725657, 376473601396703447, 100031,
'[]', 1
)
,
(
376510847369871385, 376473601396703447, 100030,
'[]', 1
)
on duplicate key update value_ids=values(`value_ids`), update_time=values(`update_time`)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 98 page no 5231 n bits 152 index PRIMARY of table `tishu`.`recycle_order_extend` trx id 195596 lock_mode X locks gap before rec
Record lock, heap no 83 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 00000002efac; asc ;;
2: len 7; hex 8100008cbb0ec4; asc ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 98 page no 5231 n bits 152 index PRIMARY of table `tishu`.`recycle_order_extend` trx id 195596 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 83 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 00000002efac; asc ;;
2: len 7; hex 8100008cbb0ec4; asc ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 195597, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 50, OS thread handle 6135132160, query id 402 localhost 127.0.0.1 root update
insert into recycle_order_extend (id, recycle_order_id, param_id,
value_ids, es_type) values
(
376510847366725657, 376473627618443479, 100031,
'[]', 1
)
,
(
376510847369871385, 376473627618443479, 100030,
'[]', 1
)
on duplicate key update value_ids=values(`value_ids`), update_time=values(`update_time`)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 98 page no 5231 n bits 152 index PRIMARY of table `tishu`.`recycle_order_extend` trx id 195597 lock_mode X locks gap before rec
Record lock, heap no 83 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 00000002efac; asc ;;
2: len 7; hex 8100008cbb0ec4; asc ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 98 page no 5231 n bits 152 index PRIMARY of table `tishu`.`recycle_order_extend` trx id 195597 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 83 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 8; hex a6a1360e0ef42300; asc 6 # ;;
1: len 6; hex 00000002efac; asc ;;
2: len 7; hex 8100008cbb0ec4; asc ;;
3: len 8; hex 83dcebce32400001; asc 2@ ;;
4: len 8; hex 80000000000186a8; asc ;;
5: len 5; hex 5b2232225d; asc ["2"];;
6: len 5; hex 99ab64b48a; asc d ;;
7: len 5; hex 99ab7b17aa; asc { ;;
8: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------這時(shí)候就產(chǎn)生了一個(gè)疑問，有沒有可能我們傳過去是一個(gè)SQL語句，但是對于MySQL處理是一個(gè)記錄一個(gè)記錄處理的呢？
既然有疑問，那么我們就下載MySQL源碼，通過源碼來證明自己的猜想。
4.MySQL8.0源碼
4.1 準(zhǔn)備工作
- MySQL8.0版本的源碼，CLion開發(fā)工具
- 配置工具鏈
-DHOMEBREW_HOME=/opt/homebrew/opt -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DDOWNLOAD_BOOST_TIMEOUT=60000 -DWITH_BOOST=boost -DCMAKE_INSTALL_PREFIX=build_out -DMYSQL_DATADIR=build_out/data -DSYSCONFDIR=build_out/etc -DMYSQL_TCP_PORT=3310 -DMYSQL_UNIX_ADDR=mysql-debug.sock- CMake編譯項(xiàng)目
- 初始化MySQL
--initialize-insecure- 啟動(dòng)MySQL
修改程序?qū)崊?/p>
--defaults-file=/Users/chenkai/student/Mysql/mysql-8.0.33/cmake-build-debug/build_out/etc/my.cnf4.2 源碼閱讀
通過參考資料和自己Debug代碼得到調(diào)用鏈路
精簡版本
調(diào)用鏈路
-sql_insert.cc#Sql_cmd_insert_values::execute_inner->遍歷values執(zhí)行
-handler.cc#ha_write_row->操作一行記錄
-ha_innodb.cc#ha_innobase::write_row->在InnoDB database中存儲一行數(shù)據(jù)，針對這張表對應(yīng)的handle
-row0mysql.cc#row_insert_for_mysql->執(zhí)行insert操作
-row0ins.cc#x->向一個(gè)table中插入一行
-row0ins.cc#row_ins_index_entry_step->向表中插入index，每個(gè)索引需要單獨(dú)插入,一個(gè)索引執(zhí)行一次
-row0ins.cc#row_ins_clust_index_entry->向表中插入聚集索引
-row0ins.cc#row_ins_sec_index_multi_value_entry->向表中插入多個(gè)value的二級索引
-row0ins.cc#row_ins_sec_index_entry->向表中插入二級索引
-row0mysql.cc#row_mysql_handle_errors->處理存在重復(fù)key的情況
-que0que.cc#que_run_threads_low->真正選擇線程去執(zhí)行操作（牽扯到對剛插入的索引回滾，所以需要加間隙鎖，防止幻讀）
-handler.cc#handler::ha_index_read_idx_map 檢索原有記錄主鍵并加鎖，防止被修改
-handler.cc#ha_update_row->修改一行記錄
-row0upd.cc#row_upd->修改table中的一行
-row0upd.cc#row_upd_clust_step->修改聚集索引
-row0upd.cc#row_upd_sec_step->修改二級索引
-row0upd.cc#row_upd_sec_index_entry->更新單個(gè)二級索引詳細(xì)版本
調(diào)用鏈路
sql_parse.cc#mysql_execute_command->按照不同的SQL分類，進(jìn)入不同的執(zhí)行流程
-sql_select.cc#Sql_cmd_dml::execute->執(zhí)行sql
-sql_insert.cc#Sql_cmd_insert_values::execute_inner->遍歷values執(zhí)行
-sql_insert.cc#write_record->執(zhí)行sql中每一條記錄
-handler.cc#ha_write_row->操作一行記錄
-ha_innodb.cc#ha_innobase::write_row->在InnoDB database中存儲一行數(shù)據(jù)，針對這張表對應(yīng)的handle
-row0mysql.cc#row_insert_for_mysql->執(zhí)行insert操作
-row0mysql.cc#row_insert_for_mysql_using_ins_graph->使用graph結(jié)構(gòu)存儲insert的信息
-row0mysql.cc#row_mysql_convert_row_to_innobase->將一行數(shù)據(jù)從MySQL格式轉(zhuǎn)換為innodb格式
-row0ins.cc#x->向一個(gè)table中插入一行
-row0ins.cc#row_ins->向表中插入一行
-row0ins.cc#row_ins_index_entry_step->向表中插入index，每個(gè)索引需要單獨(dú)插入,一個(gè)索引執(zhí)行一次
-row0ins.cc#row_ins_index_entry->向表中插入index
-row0ins.cc#row_ins_clust_index_entry->向表中插入聚集索引
-row0mysql.cc#row_ins_clust_index_entry_low->嘗試將entry插入到聚集索引中
開始事務(wù)
-btr0cur.cc#btr_cur_optimistic_insert->插入一個(gè)page到一個(gè)索引tree
-btr0cur.cc#btr_cur_ins_lock_and_undo->檢查lock和寫undolog和加lock
-lock0lock.cc#lock_rec_insert_check_and_lock->檢查lock和加lock
結(jié)束事務(wù)
-row0ins.cc#row_ins_sec_index_multi_value_entry->向表中插入多個(gè)value的二級索引
-row0ins.cc#row_ins_sec_index_entry->向表中插入二級索引
-row0ins.cc#row_ins_sec_index_entry_low->嘗試將entry插入到二級索引中
-row0ins.cc#row_ins_scan_sec_index_for_duplicate->掃描給定索引項(xiàng)處的唯一非聚集索引，以確定該項(xiàng)的鍵值是否發(fā)生唯一性沖突。對可能重復(fù)的記錄設(shè)置共享鎖
-row0mysql.cc#row_mysql_handle_errors->處理存在重復(fù)key的情況
-trx0roll.cc#trx_rollback_to_savepoint->回滾事務(wù)到某個(gè)保存點(diǎn)（插入時(shí)主鍵或者唯一索引沖突之前的保存點(diǎn)）
-trx0roll.cc#trx_rollback_to_savepoint_low->執(zhí)行真正的回滾操作
-que0que.cc#que_run_threads->調(diào)度一個(gè)線程執(zhí)行操作(在處理重復(fù)key的情況下，是調(diào)用undo線程執(zhí)行回滾操作)
-que0que.cc#que_run_threads_low->真正選擇線程去執(zhí)行操作（牽扯到對剛插入的索引回滾，所以需要加間隙鎖，防止幻讀）
-handler.cc#handler::ha_index_read_idx_map 檢索原有記錄主鍵并加鎖，防止被修改
-handler.cc#ha_update_row->修改一行記錄
-ha_innodb.cc#ha_innobase::update_row->在InnoDB database中修改一行數(shù)據(jù)，針對這張表對應(yīng)的handle
-row0mysql.cc#row_update_for_mysql->執(zhí)行update操作
-row0mysql.cc#row_update_for_mysql_using_upd_graph->使用graph結(jié)構(gòu)存儲update的信息
-row0upd.cc#row_upd_step->修改table中的一行
-row0upd.cc#row_upd->修改table中的一行
-row0upd.cc#row_upd_clust_step->修改聚集索引
#開始事務(wù)
-row0upd.cc#row_upd_del_mark_clust_rec->標(biāo)記刪除舊聚集索引
-btr0cur.cc#btr_cur_del_mark_set_clust_rec->設(shè)置二級索引記錄的刪除標(biāo)記
-lock0lock.cc#ock_clust_rec_modify_check_and_lock->檢查lock和加lock
-row0upd.cc#row_upd_clust_rec_by_insert->插入新的聚集索引,標(biāo)記刪除舊聚集索引
-btr0cur.cc#btr_cur_del_mark_set_clust_rec->設(shè)置二級索引記錄的刪除標(biāo)記
-lock0lock.cc#lock_clust_rec_modify_check_and_lock->檢查lock和加lock
-row0ins.cc#row_ins_clust_index_entry->向表中插入聚集索引
#提交事務(wù)
-row0upd.cc#row_upd_sec_step->修改二級索引
-rrow0upd.cc#row_upd_del_multi_sec_index_entry->刪除多個(gè)二級索引
-rrow0upd.cc#row_upd_multi_sec_index_entry->更新多個(gè)二級索引
-row0upd.cc#row_upd_sec_index_entry->更新單個(gè)二級索引
-row0upd.cc#row_upd_sec_index_entry_low->嘗試修改entry中的二級索引
#開始事務(wù)
-btr0cur.cc#btr_cur_del_mark_set_sec_rec->設(shè)置二級索引記錄的刪除標(biāo)記
-lock0lock.cc#lock_sec_rec_modify_check_and_lock->檢查lock和加lock
-row0ins.cc#row_ins_sec_index_entry->向表中插入二級索引
-row0ins.cc#row_ins_sec_index_entry_low->嘗試將entry插入到二級索引中
-row0ins.cc#row_ins_scan_sec_index_for_duplicate->掃描給定索引項(xiàng)處的唯一非聚集索引，以確定該項(xiàng)的鍵值是否發(fā)生唯一性沖突。對可能重復(fù)的記錄設(shè)置共享鎖
#提交事務(wù)4.3 Debug代碼后得到執(zhí)行SQL加鎖的過程
執(zhí)行SQL語句
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473601396703447, 100030, '[]', 1 ),
( 376510847369871385, 376473601396703447, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();加鎖過程
類型 模式 索引 數(shù)據(jù)
TABLE ｜ IX ｜ NULL ｜ null
執(zhí)行第一條value的插入主鍵索引前 row0ins.cc#row_ins_clust_index_entry
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
執(zhí)行第一條value的插入二級索引后 row0ins.cc#row_ins_sec_index_entry
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X,GAP ｜ PRIMARY ｜ 2783565478700000000
執(zhí)行第一條value處理存在重復(fù)key的情況 row0mysql.cc#row_mysql_handle_errors
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X,GAP ｜ PRIMARY ｜ 2783565478700000000
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984043204793
執(zhí)行第一條value檢索原有記錄操作后 handler.cc#handler::ha_index_read_idx_map
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X,GAP ｜ PRIMARY ｜ 376510847369871385
RECORD ｜ X,GAP ｜ PRIMARY ｜ 2783565478700000000
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984043204793
執(zhí)行第二條value的插入主鍵索引后 row0ins.cc#row_ins_clust_index_entry
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100031,376501984042156217
RECORD ｜ X,GAP ｜ PRIMARY ｜ 376510847369871385
RECORD ｜ X,GAP ｜ PRIMARY ｜ 2783565478700000000
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984043204793
執(zhí)行第二條value的插入二級索引后 row0ins.cc#row_ins_sec_index_entry
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100031,376501984042156217
RECORD ｜ X,GAP ｜ PRIMARY ｜ 376510847369871385
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984043204793
執(zhí)行第er條value處理存在重復(fù)key的情況 row0mysql.cc#row_mysql_handle_errors
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100031,376501984042156217
RECORD ｜ X,GAP ｜ PRIMARY ｜ 2783565478700000000
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984042156217
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984043204793
執(zhí)行第二條value檢索原有記錄操作后 handler.cc#handler::ha_index_read_idx_map
TABLE ｜ IX ｜ NULL ｜ null
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
RECORD ｜ X ｜ uniq_recycle_param ｜ 376473601396703447,100031,376501984042156217
RECORD ｜ X,GAP ｜ PRIMARY ｜ 2783565478700000000
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984042156217
RECORD ｜ X,REC_NOT_GAP ｜ PRIMARY ｜ 376501984043204793
完成4.4 結(jié)論
1.在MySQL執(zhí)行過程中會遍歷每一個(gè)value執(zhí)行。
2.執(zhí)行過程中加鎖是分批加鎖的，比如handler.cc#handler::ha_index_read_idx_map會檢查原有記錄加行鎖防止修改，row0mysql.cc#row_mysql_handle_errors處理存在重復(fù)key的情況會加間隙鎖。
3.處理SQL過程中并不會加鎖，保證同時(shí)只能處理一條SQL。
4.內(nèi)部有迷你事務(wù)，本質(zhì)是對記錄所在的頁加一個(gè) RW-X-LATCH 鎖保證共享資源（如頁、行或元數(shù)據(jù)）的訪問，以避免數(shù)據(jù)競爭和不一致，從而保證對同一記錄加鎖不是并發(fā)的。
5 問題驗(yàn)證
通過上述結(jié)論以及SQL語句的調(diào)用鏈路，我們可以在執(zhí)行row0mysql.cc#row_mysql_handle_errors睡眠1s，這樣我們可以模仿事務(wù)A和事務(wù)B都拿到間隙鎖進(jìn)而產(chǎn)生死鎖的現(xiàn)象。
圖片
5.1 復(fù)現(xiàn)A事務(wù)執(zhí)行兩條，B事務(wù)執(zhí)行一條產(chǎn)生死鎖的過程
過程：
步驟1:先在A事務(wù)中執(zhí)行
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473601396703447, 100030, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();
到斷點(diǎn)
步驟2:當(dāng)?shù)綌帱c(diǎn)后在事務(wù)B中執(zhí)行
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473627618443479, 100030, '[]', 1 ),
( 376510847369871385, 376473627618443479, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();
步驟3:
然后再到事務(wù)A中執(zhí)行
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847508283417, 376473608578400471, 100030, '[]', 1 ),
( 376510847509331993, 376473608578400471, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();當(dāng)執(zhí)行完步驟3后就會發(fā)生死鎖，且死鎖原因也是因?yàn)槭聞?wù)A和B都獲取到了間隙鎖。
5.2 復(fù)現(xiàn)A事務(wù)執(zhí)行一條，B事務(wù)執(zhí)行一條產(chǎn)生死鎖的過程
過程：
步驟1:先在A事務(wù)中執(zhí)行
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473601396703447, 100030, '[]', 1 ),
( 376510847369871385, 376473601396703447, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();
到斷點(diǎn)
步驟2:當(dāng)?shù)綌帱c(diǎn)后在事務(wù)B中執(zhí)行
INSERT INTO recycle_order_extend ( id, recycle_order_id, param_id, value_ids, es_type )
VALUES
( 376510847366725657, 376473627618443479, 100030, '[]', 1 ),
( 376510847369871385, 376473627618443479, 100031, '[]', 1 )
ON DUPLICATE KEY UPDATE value_ids=values(`value_ids`), update_time = now();當(dāng)執(zhí)行完步驟2后就會發(fā)生死鎖，且死鎖原因也是因?yàn)槭聞?wù)A和B都獲取到了間隙鎖。
6 參考
MAC 下編譯調(diào)試 MySQL8.0 源碼（https://blog.csdn.net/qq_40161813/article/details/127823025）
mysql 8.0 一條insert語句的具體執(zhí)行流程分析(二)（https://blog.csdn.net/zgaoq/article/details/120371555）
關(guān)于作者
陳凱 俠客匯Java開發(fā)工程師
責(zé)任編輯：武曉燕
來源：
轉(zhuǎn)轉(zhuǎn)技術(shù)
