- 当前位置：首页 >人工智能 >深入剖析SQL死锁-两条SQL之间的死锁原因
深入剖析SQL死锁-两条SQL之间的死锁原因
发布时间：2025-11-04 07:37:51 来源：云智核 作者：人工智能
1.问题背景
在业务实现中，深入L死锁两锁原若数据存在则修改，剖析不存在则插入，深入L死锁两锁原通常大家会选用 INSERT INTO... ON DUPLICATE KEY UPDATE 语句让数据库实现此功能。剖析近期在进行开发批量取消预约物流的深入L死锁两锁原场景中，由于一个物流对应多个订单，剖析同时取消多个物流时，深入L死锁两锁原接口可能会变慢。剖析故采用了多线程，深入L死锁两锁原每个物流取消任务对应一个线程。剖析然而，深入L死锁两锁原在测试过程中出现了意外问题，剖析当同时取消两个物流单时发生了数据库死锁。深入L死锁两锁原
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,深入L死锁两锁原 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)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.通过日志很容易的找到产生死锁的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();1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.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;1.
图片
发现事务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 ------------1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.这时候就产生了一个疑问，有没有可能我们传过去是一个SQL语句，但是对于MySQL处理是一个记录一个记录处理的呢？
既然有疑问，那么我们就下载MySQL源码，通过源码来证明自己的猜想。
4.MySQL8.0源码
4.1 准备工作
MySQL8.0版本的源码，CLion开发工具配置工具链
修改程序实参
复制--defaults-file=/Users/chenkai/student/Mysql/mysql-8.0.33/cmake-build-debug/build_out/etc/my.cnf1.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->更新单个二级索引1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.详细版本
复制调用链路 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->扫描给定索引项处的唯一非聚集索引，以确定该项的键值是否发生唯一性冲突。对可能重复的记录设置共享锁 #提交事务1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.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();1.2.3.4.5.加锁过程
复制类型 模式 索引 数据 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 完成1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.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();1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.当执行完步骤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();1.2.3.4.5.6.7.8.9.10.11.12.13.14.当执行完步骤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开发工程师
香港云服务器服务器租用益华科技源码下载亿华云源码库IT技术网IT资讯网企商汇益强科技全栈开发极客码头亿华科技益强科技益强编程舍益强数据堂IT资讯网亿华云智能时代云智核益强IT技术网创站工坊极客编程思维库益强资讯优选益强编程堂益华科技技术快报亿华云计算亿华灵动亿华智造汇智坊益强前沿资讯码力社多维IT资讯亿华互联益强智囊团科技前瞻编程之道码上建站益华科技云站无忧
- Copyright © 2025 Powered by 深入剖析SQL死锁-两条SQL之间的死锁原因,云智核 滇ICP备2023006006号-28sitemap