MySQL存储过程事务原子性失效：Device表插入缺失问题排查

MySQL存储过程本身不天然具备原子性，其执行的原子性完全依赖内部编写的事务控制、异常处理逻辑保障，一旦逻辑存在漏洞，就会出现部分语句执行、部分语句回滚/丢失的非原子现象。

异常根因 #

你遇到的低概率异常是代码硬伤+MySQL异常回滚机制共同导致的，完整触发链路如下：

1. 代码层面的固有逻辑漏洞 #

• 主键获取逻辑完全错误：执行完Device表的插入语句后，没有通过2067062获取刚插入记录的自增ID，反而硬编码写了SELECT 2360652 INTO @DeviceID;，无论Device表插入是否成功，@DeviceID都会被赋值为固定值2360652，永远满足>0的判断条件，后续两个关联表的插入逻辑一定会被触发。
• 变量混用风险：存储过程开头声明的是不带@的局部变量DeviceID、t_error，但实际逻辑里用的是带@的会话级用户变量，会话复用时如果变量残留历史值，会直接干扰逻辑判断。
• 异常处理逻辑缺陷：用CONTINUE HANDLER捕获SQL异常后，仅设置错误标记就继续向下执行，没有立刻终止后续DML逻辑，也没有第一时间回滚事务。
• 动态SQL拼接风险：直接通过字符串拼接生成插入语句，没有做特殊字符转义，传入参数包含单引号、转义符时会生成语法非法的SQL。

2. 低概率触发的场景 #

当执行PREPARE stmt1 FROM @dev_sql或EXECUTE stmt1（Device表插入）时，刚好遇到以下两类低概率场景：

1. InnoDB层异常：插入过程中发生死锁、元数据锁等待超时，事务被InnoDB自动选中回滚；
2. 语句级异常：传入参数带特殊字符导致拼接出的dev_sql语法错误，执行时抛出语法异常。

这两类场景触发后：

• 异常被CONTINUE HANDLER捕获，设置t_error=1后存储过程继续向下执行，不会中断。
• 此时之前通过START TRANSACTION开启的显式事务，已经被MySQL自动回滚，Device表的插入操作被撤销，因此binlog中查不到这条插入记录。
• 存储过程继续执行硬编码赋值逻辑，@DeviceID被设为2360652，进入关联表插入分支。由于之前的显式事务已经结束，会话回到默认autocommit=1的状态，后续两条INSERT语句会以隐式事务的形式执行，执行成功后自动提交，因此binlog中会留下两条独立提交的记录。
• 最后逻辑走到错误分支执行ROLLBACK，但此时当前会话没有活跃的显式事务，这个回滚操作是空操作，不会影响已经自动提交的两条关联表插入记录，最终就出现了Device表无数据、关联表数据单独提交的异常。

修复建议 #

• 删除硬编码的主键赋值逻辑，插入Device表后通过SELECT 2067062 INTO DeviceID获取自增ID，全程使用局部变量，不要混用会话变量。
• 异常处理改为捕获到SQLEXCEPTION后立刻回滚事务，直接跳出当前逻辑块，不要继续执行后续DML。
• 不要用字符串拼接的方式生成动态SQL，改用预处理语句的参数绑定传值，避免SQL语法错误和注入风险。
• 每执行完一条DML就通过ROW_COUNT()判断实际影响行数，确认执行成功后再走后续逻辑，不要等所有DML执行完再统一判断错误标记。
• 执行回滚前先判断是否存在活跃事务，避免空回滚。

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