深入剖析SQL死鎖-兩條SQL之間的死鎖原因

作者：陳凱 2024-12-27 00:00:00

在業務實現中，若數據存在則修改，不存在則插入，通常大家會選用 INSERT INTO... ON DUPLICATE KEY UPDATE 語句讓數據庫實現此功能。近期在進行開發批量取消預約物流的場景中，由于一個物流對應多個訂單，同時取消多個物流時，接口可能會變慢。

1.問題背景

在業務實現中，若數據存在則修改，不存在則插入，通常大家會選用 INSERT INTO... ON DUPLICATE KEY UPDATE 語句讓數據庫實現此功能。近期在進行開發批量取消預約物流的場景中，由于一個物流對應多個訂單，同時取消多個物流時，接口可能會變慢。故采用了多線程，每個物流取消任務對應一個線程。然而，在測試過程中出現了意外問題，當同時取消兩個物流單時發生了數據庫死鎖。

2.問題復現

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)

通過日志很容易的找到產生死鎖的SQL語句。

在事務A執行回收單號為376473627618443479對應的數據修改時，會先處理單號376473601396703447對應的數據（通過SQL語句打印得知執行順序），所以是如下執行流程。

事務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();
事務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 問題復現

通過上面的日志找到了產生死鎖的SQL語句，那么我們就將測試數據導入到本地（本地數據庫8.0版本），嘗試復現問題。

步驟	事務A	事務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(`value_ids`), update_time = now();	-
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(`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();	-

現象是事務A會把事務B進行阻塞，并不會死鎖。

獲取當前鎖的占用情況；

SELECT engine_transaction_id,index_name,lock_type,lock_mode,lock_status,lock_data  FROM performance_schema.data_locks;

圖片

發現事務B會因為事務A持有的間隙鎖導致自己需要執行的插入意向鎖獲取失敗，進而進行阻塞。

3.問題思考

通過上述的問題驗證，發現根本無法復現問題，因為事務A一定會阻塞事務B，但是為什么又會出現死鎖的問題呢？

于是編寫本地測試用例，同時啟動兩個線程模仿修改表的任務，發現確實會死鎖，并且這次的死鎖現象更奇怪，僅僅A線程和B線程各執行了一條SQL就產生了死鎖，并且持有鎖和等待鎖都是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
------------

這時候就產生了一個疑問，有沒有可能我們傳過去是一個SQL語句，但是對于MySQL處理是一個記錄一個記錄處理的呢？

既然有疑問，那么我們就下載MySQL源碼，通過源碼來證明自己的猜想。

4.MySQL8.0源碼

4.1 準備工作

MySQL8.0版本的源碼，CLion開發工具
配置工具鏈

-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編譯項目
初始化MySQL

--initialize-insecure

啟動MySQL

修改程序實參

--defaults-file=/Users/chenkai/student/Mysql/mysql-8.0.33/cmake-build-debug/build_out/etc/my.cnf

4.2 源碼閱讀

通過參考資料和自己Debug代碼得到調用鏈路

精簡版本

調用鏈路
-sql_insert.cc#Sql_cmd_insert_values::execute_inner->遍歷values執行
  -handler.cc#ha_write_row->操作一行記錄
   -ha_innodb.cc#ha_innobase::write_row->在InnoDB database中存儲一行數據，針對這張表對應的handle
    -row0mysql.cc#row_insert_for_mysql->執行insert操作
     -row0ins.cc#x->向一個table中插入一行
      -row0ins.cc#row_ins_index_entry_step->向表中插入index，每個索引需要單獨插入,一個索引執行一次
       -row0ins.cc#row_ins_clust_index_entry->向表中插入聚集索引
       -row0ins.cc#row_ins_sec_index_multi_value_entry->向表中插入多個value的二級索引
       -row0ins.cc#row_ins_sec_index_entry->向表中插入二級索引
     -row0mysql.cc#row_mysql_handle_errors->處理存在重復key的情況
      -que0que.cc#que_run_threads_low->真正選擇線程去執行操作（牽扯到對剛插入的索引回滾，所以需要加間隙鎖，防止幻讀）

  -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->更新單個二級索引

詳細版本

調用鏈路
sql_parse.cc#mysql_execute_command->按照不同的SQL分類，進入不同的執行流程
 -sql_select.cc#Sql_cmd_dml::execute->執行sql
  -sql_insert.cc#Sql_cmd_insert_values::execute_inner->遍歷values執行
   -sql_insert.cc#write_record->執行sql中每一條記錄

    -handler.cc#ha_write_row->操作一行記錄
     -ha_innodb.cc#ha_innobase::write_row->在InnoDB database中存儲一行數據，針對這張表對應的handle
      -row0mysql.cc#row_insert_for_mysql->執行insert操作
       -row0mysql.cc#row_insert_for_mysql_using_ins_graph->使用graph結構存儲insert的信息
        -row0mysql.cc#row_mysql_convert_row_to_innobase->將一行數據從MySQL格式轉換為innodb格式
        -row0ins.cc#x->向一個table中插入一行
         -row0ins.cc#row_ins->向表中插入一行
          -row0ins.cc#row_ins_index_entry_step->向表中插入index，每個索引需要單獨插入,一個索引執行一次
           -row0ins.cc#row_ins_index_entry->向表中插入index
            -row0ins.cc#row_ins_clust_index_entry->向表中插入聚集索引
             -row0mysql.cc#row_ins_clust_index_entry_low->嘗試將entry插入到聚集索引中
              開始事務
              -btr0cur.cc#btr_cur_optimistic_insert->插入一個page到一個索引tree
               -btr0cur.cc#btr_cur_ins_lock_and_undo->檢查lock和寫undolog和加lock
                -lock0lock.cc#lock_rec_insert_check_and_lock->檢查lock和加lock
              結束事務  
            -row0ins.cc#row_ins_sec_index_multi_value_entry->向表中插入多個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->掃描給定索引項處的唯一非聚集索引，以確定該項的鍵值是否發生唯一性沖突。對可能重復的記錄設置共享鎖
        -row0mysql.cc#row_mysql_handle_errors->處理存在重復key的情況
         -trx0roll.cc#trx_rollback_to_savepoint->回滾事務到某個保存點（插入時主鍵或者唯一索引沖突之前的保存點）
          -trx0roll.cc#trx_rollback_to_savepoint_low->執行真正的回滾操作
           -que0que.cc#que_run_threads->調度一個線程執行操作(在處理重復key的情況下，是調用undo線程執行回滾操作)
            -que0que.cc#que_run_threads_low->真正選擇線程去執行操作（牽扯到對剛插入的索引回滾，所以需要加間隙鎖，防止幻讀）
    
    -handler.cc#handler::ha_index_read_idx_map 檢索原有記錄主鍵并加鎖，防止被修改
              
    -handler.cc#ha_update_row->修改一行記錄         
     -ha_innodb.cc#ha_innobase::update_row->在InnoDB database中修改一行數據，針對這張表對應的handle
      -row0mysql.cc#row_update_for_mysql->執行update操作
       -row0mysql.cc#row_update_for_mysql_using_upd_graph->使用graph結構存儲update的信息
        -row0upd.cc#row_upd_step->修改table中的一行
         -row0upd.cc#row_upd->修改table中的一行
          -row0upd.cc#row_upd_clust_step->修改聚集索引
           #開始事務
           -row0upd.cc#row_upd_del_mark_clust_rec->標記刪除舊聚集索引
            -btr0cur.cc#btr_cur_del_mark_set_clust_rec->設置二級索引記錄的刪除標記
             -lock0lock.cc#ock_clust_rec_modify_check_and_lock->檢查lock和加lock
           -row0upd.cc#row_upd_clust_rec_by_insert->插入新的聚集索引,標記刪除舊聚集索引
            -btr0cur.cc#btr_cur_del_mark_set_clust_rec->設置二級索引記錄的刪除標記
             -lock0lock.cc#lock_clust_rec_modify_check_and_lock->檢查lock和加lock
            -row0ins.cc#row_ins_clust_index_entry->向表中插入聚集索引 
           #提交事務
          -row0upd.cc#row_upd_sec_step->修改二級索引
           -rrow0upd.cc#row_upd_del_multi_sec_index_entry->刪除多個二級索引
           -rrow0upd.cc#row_upd_multi_sec_index_entry->更新多個二級索引
           -row0upd.cc#row_upd_sec_index_entry->更新單個二級索引
            -row0upd.cc#row_upd_sec_index_entry_low->嘗試修改entry中的二級索引
             #開始事務 
             -btr0cur.cc#btr_cur_del_mark_set_sec_rec->設置二級索引記錄的刪除標記
              -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->掃描給定索引項處的唯一非聚集索引，以確定該項的鍵值是否發生唯一性沖突。對可能重復的記錄設置共享鎖 
             #提交事務

4.3 Debug代碼后得到執行SQL加鎖的過程

執行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();

加鎖過程

類型      模式              索引                  數據
TABLE  ｜ IX            ｜ NULL               ｜ null
執行第一條value的插入主鍵索引前 row0ins.cc#row_ins_clust_index_entry

TABLE  ｜ IX            ｜ NULL               ｜ null
RECORD ｜ X             ｜ uniq_recycle_param ｜ 376473601396703447,100030,376501984043204793
執行第一條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
執行第一條value處理存在重復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
執行第一條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
執行第二條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
執行第二條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
執行第er條value處理存在重復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
執行第二條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 結論

1.在MySQL執行過程中會遍歷每一個value執行。

2.執行過程中加鎖是分批加鎖的，比如handler.cc#handler::ha_index_read_idx_map會檢查原有記錄加行鎖防止修改，row0mysql.cc#row_mysql_handle_errors處理存在重復key的情況會加間隙鎖。

3.處理SQL過程中并不會加鎖，保證同時只能處理一條SQL。

4.內部有迷你事務，本質是對記錄所在的頁加一個 RW-X-LATCH 鎖保證共享資源（如頁、行或元數據）的訪問，以避免數據競爭和不一致，從而保證對同一記錄加鎖不是并發的。

5 問題驗證

通過上述結論以及SQL語句的調用鏈路，我們可以在執行row0mysql.cc#row_mysql_handle_errors睡眠1s，這樣我們可以模仿事務A和事務B都拿到間隙鎖進而產生死鎖的現象。

圖片

5.1 復現A事務執行兩條，B事務執行一條產生死鎖的過程

過程：

步驟1:先在A事務中執行
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();
到斷點

步驟2:當到斷點后在事務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();

步驟3:
然后再到事務A中執行
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();

當執行完步驟3后就會發生死鎖，且死鎖原因也是因為事務A和B都獲取到了間隙鎖。

5.2 復現A事務執行一條，B事務執行一條產生死鎖的過程

過程：

步驟1:先在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();
到斷點

步驟2:當到斷點后在事務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后就會發生死鎖，且死鎖原因也是因為事務A和B都獲取到了間隙鎖。

6 參考

MAC 下編譯調試 MySQL8.0 源碼（https://blog.csdn.net/qq_40161813/article/details/127823025）

mysql 8.0 一條insert語句的具體執行流程分析(二)（https://blog.csdn.net/zgaoq/article/details/120371555）

關于作者

陳凱俠客匯Java開發工程師

責任編輯：武曉燕來源：轉轉技術

SQL 死鎖數據庫