MySQL存储过程死锁咨询：回滚后内部函数操作未回滚原因

1. 为何存储过程因死锁回滚后，内部函数的操作未被回滚？ #

可能的核心原因如下：

• 存储引擎不支持事务：如果tableC或tableD使用的是MyISAM这类非事务型存储引擎，函数中对它们的INSERT/UPDATE操作会立即生效，完全不受存储过程事务回滚的影响。这类引擎不支持事务ACID特性，所有DML操作都会自动提交，不会纳入事务上下文管理。

• 错误处理逻辑存在语法缺陷：你的存储过程中GET DIAGNOSTICS语句缺少SET关键字，正确语法应为：

  GET DIAGNOSTICS CONDITION 1
  SET err_code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
  

  语法错误会导致err_code始终保持默认值00000，此时IF err_code != '00000'条件不成立，存储过程会执行COMMIT而非ROLLBACK。最终函数的操作被提交，而死锁导致的部分操作（如tableA/tableB的插入）被MySQL自动回滚，造成“函数操作未回滚”的假象。

• 隐式提交触发：如果函数内部间接执行了会触发隐式提交的语句（如DDL、LOCK TABLES、UNLOCK TABLES等），会导致当前事务被提前提交，后续的ROLLBACK无法回滚已经提交的函数操作。

2. 存储过程为何会产生死锁？ #

死锁是多个事务互相持有对方所需锁资源且无法释放导致的，结合你的代码场景，可能的触发原因包括：

• 跨表操作顺序不一致：如果存在其他并发事务（或同一存储过程的并发调用），对相关表的操作顺序与你的存储过程不一致。例如：

  • 你的存储过程操作顺序：INSERT tableA → INSERT tableB → INSERT tableC → UPDATE tableD
  • 另一个事务操作顺序：UPDATE tableD → INSERT tableA
    当两个事务交叉执行时，会互相持有对方需要的锁，最终触发死锁。

• 无索引导致锁范围过大：如果updateJournal函数中的UPDATE tabled ....语句未使用有效索引（比如未通过orderId关联索引），MySQL会执行全表扫描并锁定表中所有行，大幅提升了与其他事务锁冲突的概率，容易引发死锁。

• 事务持有锁时间过长：你的存储过程在单个事务中连续执行多个DML操作，还嵌套了函数调用，整个事务的执行周期较长，持有锁的时间越久，并发场景下与其他事务发生锁冲突的可能性就越高。

• 热点行并发更新：如果多个并发请求同时触发存储过程，且都需要更新tableD中的同一行（或同一批热点行），就容易出现死锁：比如两个事务都完成了tableA/tableB的插入，同时尝试更新tableD的同一行，彼此持有部分锁资源且互相等待。

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