MySQL数据插入乱序（竞态条件？）原因分析与规避方案

这个乱序和漏数问题是多层面机制共同导致的，和高并发写入场景的特性直接相关：

1. 自增值顺序与事务提交顺序无强绑定
   InnoDB的AUTO_INCREMENT自增值是在INSERT语句执行的初始阶段分配的，一旦分配就不会回滚，和事务最终提交的时机没有原子绑定关系。高并发场景下，先拿到自增值的事务可能因为MDL锁等待、行锁冲突、页分裂、事务内包含其他耗时逻辑、两阶段提交调度等原因，执行过程被阻塞，比后拿到更大自增值的事务更晚完成提交。
   你遇到的记录乱序完全符合这个逻辑：
   • T=0.0s时刻，写入记录1的事务启动，执行INSERT语句第一时间分配到自增值100，随后执行过程被阻塞
   • 阻塞期间，写入记录2的事务启动，分配到下一个自增值101，全程无阻塞快速执行完成，写入行时数据库生成updated_at = 2022-01-01 00:00:00.000000，直接提交
   • T=0.2s时刻，写入记录1的事务阻塞恢复，继续执行写入行的操作，数据库生成updated_at = 2022-01-01 00:00:00.200000，随后提交，最终形成自增值更小但时间戳更大的乱序记录。
2. 时间戳取值无法代表事务提交顺序
   表中updated_at配置的DEFAULT CURRENT_TIMESTAMP(6)是行数据实际执行写入操作时生成的，不是事务提交时生成的，本身就和自增值分配的时机存在时间差；同时现有Hibernate映射存在配置错误：updated_at字段没有添加数据库生成值的标记注解，Hibernate会默认取应用服务器本地时间写入该字段，多实例部署时如果存在时钟漂移，会进一步加剧时间戳和自增值的乱序概率。
3. 增量查询逻辑的隐含假设不成立
   你使用SELECT * FROM TestTable where number >= ?做增量查询，隐含假设是「所有自增值小于本次查询结果最大值的记录，一定在查询执行时已经提交可见」，这个假设在并发写入场景下完全不成立。
   当你在T=0.1s时刻执行查询时，自增值101的记录已经提交可见，自增值100的记录还处于事务阻塞未提交状态，对查询不可见；如果你的增量逻辑把本次查询到的最大值101作为下次查询的游标，下次查询直接从102开始拉取，后续100这条记录提交后就永远不会被查到，形成漏数。
   额外注意：现有Hibernate实体映射还存在主键配置错误——表的实际主键是联合主键(a,b)，但实体类错误将number标注为@Id，会导致Hibernate一级缓存、脏检查逻辑异常，进一步放大写入乱序的概率。

按照改造成本从低到高、效果从临时止血到彻底解决排序：

1. 快速止血方案（改造成本极低） #

调整增量查询的回溯逻辑，不要直接用本次查询到的最大number作为下次查询的起点：每次查询时将查询下界设置为上次查询到的最大number - N，N的取值根据业务最长事务耗时调整（比如取1000，覆盖最多1000条写入的回溯窗口），查询结果在应用层按number做去重，把之前因为事务未提交漏掉的小number记录重新拉回。
这个方案可以快速解决漏数问题，但无法彻底消除乱序，需要根据业务写入压测结果调整N的大小，避免窗口过小依然漏数。

2. 基础配置修复 #

• 修正Hibernate实体映射：如果业务确实需要用number作为单主键，直接修改表结构将主键改为number；如果业务主键是(a,b)，就将实体类的@Id注解标注到对应的两个字段上，把number标记为普通字段。
• 对所有由数据库维护默认值的字段（a、b、number、updated_at），添加@Generated(GenerationTime.ALWAYS)注解，同时配置insertable = false, updatable = false，禁止Hibernate主动向这些字段写入应用层生成的值，完全由数据库维护字段取值，避免应用时钟差带来的时间戳乱序。
• 调整InnoDB自增锁配置：将innodb_autoinc_lock_mode从默认值2（交错模式）调整为1（连续锁模式），该模式下INSERT语句执行期间会持续持有自增锁直到语句执行结束，能大幅降低自增值分配和写入执行的乱序概率，代价是写入吞吐会有10%~20%左右的下降，适合对写入性能要求不是极端高的场景。

3. 彻底解决的架构方案 #

放弃用自增列做增量游标的实现，改用基于binlog的CDC方案（比如Debezium、Canal）同步增量数据：CDC是严格按照binlog中记录的事务提交顺序消费数据，完全和数据库的事务可见顺序一致，从根本上解决乱序、漏数问题，是高并发写场景下增量数据同步的标准实现方案。
如果暂时无法接入CDC，可以将增量查询改为基于时间窗口的双校验逻辑：查询条件同时包含updated_at >= 上次同步时间 - 5s（预留5s的事务提交缓冲窗口）和number范围条件，拉取到数据后在应用层按唯一键去重，同步游标同时记录最大number和最新同步时间，不要单独依赖某一个维度做过滤。

内容的提问来源于stack exchange，提问作者Dan Nemes