深入剖析SQL死锁-两条SQL之间的死锁原因-51CTO.COM

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开发工程师