登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 业界 ›MySQL INSERT 导致的死锁分析

MySQL INSERT 导致的死锁分析

公西颖初 昨天 20:11
前言

本文选用的 MySQL 版本：8.4.6
使用的数据

表结构：
  1. DROP TABLE IF EXISTS store_snapshot_ext;
  2. DROP TABLE IF EXISTS store_snapshot;
  4. create table store_snapshot
  5. (
  6.     id           varchar(32) not null comment '主键'
  7.         primary key,
  8.     warehouse_id varchar(32) null comment '仓库主键',
  9.     snap_date    datetime    null comment '快照日期',
  10.     create_id    varchar(32) null comment '创建人id',
  11.     create_time  datetime    null comment '创建日期',
  12.     modify_id    varchar(32) null comment '更新人 id',
  13.     modify_time  datetime    null comment '更新时间'
  14. )
  15.     comment '仓库快照';
  17. create table store_snapshot_ext
  18. (
  19.     id             varchar(32) not null comment '主键'
  20.         primary key,
  21.     fk_snapshot_id varchar(32) null comment '快照外键',
  22.     ext_attr       varchar(32) null comment '扩展属性',
  23.     create_id      varchar(32) null comment '创建人id',
  24.     create_time    datetime    null comment '创建日期',
  25.     modify_id      varchar(32) null comment '更新人 id',
  26.     modify_time    datetime    null comment '更新时间',
  27.     constraint store_snapshot_ext___fk_snapshot_id
  28.         foreign key (fk_snapshot_id) references store_snapshot (id)
  29. )
  30.     comment '仓库快照扩展属性';
复制代码
这里使用 Java 语言模拟并发情况下对数据的插入：
  1. import cn.hutool.core.date.DateTime;
  2. import cn.hutool.core.util.RandomUtil;
  3. import org.junit.jupiter.api.Test;
  4. import org.junit.jupiter.api.extension.ExtendWith;
  5. import org.mybatis.spring.annotation.MapperScan;
  6. import org.springframework.boot.SpringApplication;
  7. import org.springframework.boot.autoconfigure.SpringBootApplication;
  8. import org.springframework.boot.test.context.SpringBootTest;
  9. import org.springframework.test.context.junit.jupiter.SpringExtension;
  10. import org.springframework.transaction.PlatformTransactionManager;
  11. import org.springframework.transaction.TransactionDefinition;
  12. import org.springframework.transaction.TransactionStatus;
  13. import org.springframework.transaction.annotation.EnableTransactionManagement;
  14. import org.springframework.transaction.support.DefaultTransactionDefinition;
  15. import org.tea.common.entity.StoreSnapshot;
  16. import org.tea.common.entity.StoreSnapshotExt;
  17. import org.tea.common.mapper.StoreSnapshotExtMapper;
  18. import org.tea.common.mapper.StoreSnapshotMapper;
  20. import javax.annotation.Resource;
  21. import java.util.UUID;
  22. import java.util.concurrent.CountDownLatch;
  24. @ExtendWith(SpringExtension.class)
  25. @SpringBootTest(classes = StoreSnapshotApplication.class)
  26. public class StoreSnapBenchTest {
  28.     @Resource
  29.     private PlatformTransactionManager txManager;
  31.     @Resource
  32.     private StoreSnapshotMapper storeSnapshotMapper;
  34.     @Resource
  35.     private StoreSnapshotExtMapper storeSnapshotExtMapper;
  37.     @Test
  38.     public void batchTest() throws InterruptedException {
  39.         Thread[] ts = new Thread[10];
  40.         CountDownLatch startLatch = new CountDownLatch(1);
  41.         CountDownLatch endLatch = new CountDownLatch(ts.length);
  42.         for (int i = 0; i < ts.length; i++) {
  43.             ts[i] = new Thread(() -> {
  44.                 DefaultTransactionDefinition definition = new DefaultTransactionDefinition();
  45.                 // 设置事务隔离级别为 "可重复读"
  46.                 definition.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);
  47.                 TransactionStatus status = txManager.getTransaction(definition);
  48.                 try {
  49.                     /**
  50.                             提高事务竞争的激烈度
  51.                     */
  52.                     startLatch.await();
  53.                     
  54.                     /**
  55.                             一般情况下，不会使用如下的循环方式来插入数据，这里这么做的目的是为了
  56.                             提高事务的处理时间，增大锁的竞争激烈度
  57.                     */
  58.                     for (int j = 0; j < 2000; j++) {
  59.                         StoreSnapshot snapshot = new StoreSnapshot();
  60.                         // 使用 UUID 的方式来引发 Page 分裂
  61.                         snapshot.setId(UUID.randomUUID().toString().replaceAll("-", ""));
  62.                         snapshot.setWarehouseId("warehouse_1");
  63.                         snapshot.setSnapDate(new DateTime());
  64.                         snapshot.init();
  66.                         storeSnapshotMapper.insertSelective(snapshot);
  68.                         StoreSnapshotExt snapshotExt = new StoreSnapshotExt();
  69.                         snapshotExt.setId(UUID.randomUUID().toString().replaceAll("-", ""));
  70.                         snapshotExt.setExtAttr(RandomUtil.randomString(32));
  71.                         // 注意这里的外键，后文会分析这个外键带来的一些影响
  72.                         snapshotExt.setFkSnapshotId(snapshot.getId());
  73.                         storeSnapshotExtMapper.insertSelective(snapshotExt);
  74.                     }
  75.                     txManager.commit(status);
  76.                 } catch (InterruptedException e) {
  77.                     txManager.rollback(status);
  78.                     throw new RuntimeException(e);
  79.                 } finally {
  80.                     endLatch.countDown();
  81.                 }
  82.             });
  83.         }
  85.         for (Thread t : ts) {
  86.             t.start();
  87.         }
  89.         startLatch.countDown();
  90.         endLatch.await();
  91.     }
  92. }
  94. @SpringBootApplication
  95. @EnableTransactionManagement
  96. @MapperScan("org.tea.*.mapper")
  97. class StoreSnapshotApplication {
  100.     public static void main(String[] args) {
  101.         SpringApplication.run(StoreSnapshotApplication.class, args);
  102.     }
  103. }
复制代码
在执行完上面的测试用例后，查看 MySQL InnoDB 的状态信息，发现已经出现了死锁：
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2025-08-26 21:01:55 135637563962944
  5. *** (1) TRANSACTION:
  6. TRANSACTION 3866, ACTIVE 2 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 27 lock struct(s), heap size 8312, 220 row lock(s), undo log entries 430
  9. MySQL thread id 17, OS thread handle 135637553456704, query id 8736 localhost ::1 root update
  10. INSERT INTO store_snapshot (create_id, warehouse_id, snap_date, modify_id, id, modify_time, create_time)  VALUES  ('system', 'warehouse_1', '2025-08-26 21:01:54', 'system', '402ce43f650a483eb0c9c5138e50d6f0', '2025-08-26 21:01:54', '2025-08-26 21:01:54')
  12. *** (1) HOLDS THE LOCK(S):
  13. RECORD LOCKS space id 10 page no 11 n bits 240 index PRIMARY of table `lxh_db`.`store_snapshot` trx id 3866 lock_mode X
  14. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  15. 0: len 8; hex 73757072656d756d; asc supremum;;
  18. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  19. RECORD LOCKS space id 10 page no 20 n bits 160 index PRIMARY of table `lxh_db`.`store_snapshot` trx id 3866 lock_mode X locks gap before rec insert intention waiting
  20. Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  21. 0: len 30; hex 343033303963393162373166343731633936323164616565643434666363; asc 40309c91b71f471c9621daeed44fcc; (total 32 bytes);
  22. 1: len 6; hex 000000000f14; asc       ;;
  23. 2: len 7; hex 82000001070630; asc       0;;
  24. 3: len 11; hex 77617265686f7573655f31; asc warehouse_1;;
  25. 4: len 5; hex 99b7755074; asc   uPt;;
  26. 5: len 6; hex 73797374656d; asc system;;
  27. 6: len 5; hex 99b7755074; asc   uPt;;
  28. 7: len 6; hex 73797374656d; asc system;;
  29. 8: len 5; hex 99b7755074; asc   uPt;;
  32. *** (2) TRANSACTION:
  33. TRANSACTION 3860, ACTIVE 3 sec inserting
  34. mysql tables in use 1, locked 1
  35. LOCK WAIT 46 lock struct(s), heap size 24696, 1258 row lock(s), undo log entries 2474
  36. MySQL thread id 11, OS thread handle 135637890971200, query id 12330 localhost ::1 root update
  37. INSERT INTO store_snapshot (create_id, warehouse_id, snap_date, modify_id, id, modify_time, create_time)  VALUES  ('system', 'warehouse_1', '2025-08-26 21:01:55', 'system', '917f3578682c467384e520fd6c00b86d', '2025-08-26 21:01:55', '2025-08-26 21:01:55')
  39. *** (2) HOLDS THE LOCK(S):
  40. RECORD LOCKS space id 10 page no 20 n bits 160 index PRIMARY of table `lxh_db`.`store_snapshot` trx id 3860 lock_mode X locks gap before rec
  41. Record lock, heap no 3 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  42. 0: len 30; hex 336661623363323037333232346465666263363762333630363763623666; asc 3fab3c2073224defbc67b36067cb6f; (total 32 bytes);
  43. 1: len 6; hex 000000000f14; asc       ;;
  44. 2: len 7; hex 82000000932d80; asc      - ;;
  45. 3: len 11; hex 77617265686f7573655f31; asc warehouse_1;;
  46. 4: len 5; hex 99b7755076; asc   uPv;;
  47. 5: len 6; hex 73797374656d; asc system;;
  48. 6: len 5; hex 99b7755076; asc   uPv;;
  49. 7: len 6; hex 73797374656d; asc system;;
  50. 8: len 5; hex 99b7755076; asc   uPv;;
  52. Record lock, heap no 4 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  53. 0: len 30; hex 336664326234626538643463343338326130303764383431366364613536; asc 3fd2b4be8d4c4382a007d8416cda56; (total 32 bytes);
  54. 1: len 6; hex 000000000f14; asc       ;;
  55. 2: len 7; hex 82000000903800; asc      8 ;;
  56. 3: len 11; hex 77617265686f7573655f31; asc warehouse_1;;
  57. 4: len 5; hex 99b7755076; asc   uPv;;
  58. 5: len 6; hex 73797374656d; asc system;;
  59. 6: len 5; hex 99b7755076; asc   uPv;;
  60. 7: len 6; hex 73797374656d; asc system;;
  61. 8: len 5; hex 99b7755076; asc   uPv;;
  63. Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  64. 0: len 30; hex 343033303963393162373166343731633936323164616565643434666363; asc 40309c91b71f471c9621daeed44fcc; (total 32 bytes);
  65. 1: len 6; hex 000000000f14; asc       ;;
  66. 2: len 7; hex 82000001070630; asc       0;;
  67. 3: len 11; hex 77617265686f7573655f31; asc warehouse_1;;
  68. 4: len 5; hex 99b7755074; asc   uPt;;
  69. 5: len 6; hex 73797374656d; asc system;;
  70. 6: len 5; hex 99b7755074; asc   uPt;;
  71. 7: len 6; hex 73797374656d; asc system;;
  72. 8: len 5; hex 99b7755074; asc   uPt;;
  74. Record lock, heap no 93 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  75. 0: len 30; hex 336664396165326435663738346237656161346134323336643031643366; asc 3fd9ae2d5f784b7eaa4a4236d01d3f; (total 32 bytes);
  76. 1: len 6; hex 000000000f14; asc       ;;
  77. 2: len 7; hex 82000000a81784; asc        ;;
  78. 3: len 11; hex 77617265686f7573655f31; asc warehouse_1;;
  79. 4: len 5; hex 99b7755077; asc   uPw;;
  80. 5: len 6; hex 73797374656d; asc system;;
  81. 6: len 5; hex 99b7755077; asc   uPw;;
  82. 7: len 6; hex 73797374656d; asc system;;
  83. 8: len 5; hex 99b7755077; asc   uPw;;
  86. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  87. RECORD LOCKS space id 10 page no 11 n bits 240 index PRIMARY of table `lxh_db`.`store_snapshot` trx id 3860 lock_mode X insert intention waiting
  88. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  89. 0: len 8; hex 73757072656d756d; asc supremum;;
  91. *** WE ROLL BACK TRANSACTION (1)
  92. ------------
  93. TRANSACTIONS
  94. ------------
复制代码
可以看到，在日志中，事务 3866 持有一个页号为 11，supremum (相当于双链表的尾部哑节点) 的间隙锁，在等待页号为 20，主键为 40309c91b71f471c9621daeed44fcc 的间隙锁；同时，事务 3860 持有页号为 20，主键为 40309c91b71f471c9621daeed44fcc 的间隙锁，同时在等待页号为 10 的 supremum 的间隙锁。这两个事务构成了循环回路，并且在相互等待，因此形成了死锁，具体的图示如下所示：
1.jpg

具体分析


  • 查询语句的加锁
    MySQL 的的一般 SELECT 语句在非串行化隔离级别下是通过一致性读的方式进行读取，本身不会对记录加锁，但是在存在外键约束的情况下，依旧会对关联的外键约束记录上加上 S 型的记录锁，如果关联的外键约束没有被找到，在"可重复读"的隔离级别下，会在外键记录附近加上间隙锁
    由于这里插入语句的外键都能被找到，因此这里的外键不是产生间隙锁的原因
  • INSERT 语句的加锁
    单纯的 INSERT 语句在插入时加上的是一种特殊的记录锁，不同事务的插入意向锁不会相互阻塞，但是在插入的记录行的所处位置存在间隙锁的情况下，会为当前的 INSERT 记录加上 插入意向锁
    在主键重复的情况下，隔离级别为 "可重复读" 或 "串行化" 的情况下，会为插入的记录加上 S 型的 Next-Key 锁。在这种情况下，如果原来待插入行的事务回滚了，由于本身持有的 S 型锁无法再获取到独占锁，就有可能会引发死锁[2]。
    由于这里的主键都是 UUID，不存在重复主键，并且结合相关的日志信息，并不是由于单纯的 INSERT 语句导致的死锁
  • Page 的分裂
    当插入的记录的主键不连续时，MySQL 为了维护聚簇索引的顺序，可能会引发页的分裂。在事务隔离级别为 "可重复读"或"串行化" 的情况下，对分裂的数据进行迁移的过程中，相当于对数据执行了更新的操作，按照 MySQL 对于 UPDATE 语句的加锁情况[2]，会在记录上加上 Next-Key (记录锁和间隙锁)，因此事务 3860会持有记录40309c91b71f471c9621daeed44fcc的间隙锁；而为了防止在分裂维护过程中重新插入数据，可能不得不为相关页记录的 supremum 加上间隙锁，以维护页分裂的执行过程
    这个过程可能如下：
    插入记录前：
    2.png

    插入记录导致页分裂后
    3.png

    注意: 这里关于分页而产生的间隙锁为实际实验推断，并无实际文档与之关联。当事务隔离级别为 "读提交" 或插入的记录的主键存在顺序时，都不会出现上文描述的死锁出现
解决方案

实际上，如果事务执行速度特别快，并且在并发量不高的情况下，这种类型的死锁很难被检测到，因为需要处理的事务跨多个页，并且需要关联到两个不同页的锁本身就很难。因此，将逐行的 INSERT 语句替换为批量提交后也可以很大程度上解决这一问题
为了尽可能地避免这一类问题，推荐的一些方案如下：

  • 如果没有特殊必要，可以使用隔离级别较低的事务隔离级别，因为这样可以减少实际事务处理过程中锁的数量，降低锁冲突的可能性
  • 尽量使用有序的主键，不管是从性能上还是实际业务角度，都没有理由选择 UUID 的理由
  • 如果可以，适当减少事务的粒度，如：将一个大事务分成几个小事务，在性能和一致性上做一定的权衡
参考：
[1] https://mp.weixin.qq.com/s/9LRFYGquXWpMCeyAonNcMQ
[2] https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
公西颖初
昨天 20:11

0

粉丝关注

27

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫