MySQL存储过程插入操作出现死锁问题，求排查原因

死锁触发点定位 #

你的存储过程AddTestResultRequirement里存在两处可能引发交叉锁等待的核心场景：

1. testcaserequirement表的非原子读写竞争
   当多个并发事务处理相同的RequirementNameId时，会先执行NOT EXISTS查询（该操作会加共享锁），若判断记录不存在则执行插入（需要排他锁）。如果事务A和事务B同时查询到记录不存在，都尝试插入，就会出现：A持有共享锁等待B释放锁以获取排他锁，B持有共享锁等待A释放锁以获取排他锁的循环等待，触发死锁。

2. testresultrequirementlink表的同类竞争
   同样采用了「先查询共享锁+后插入排他锁」的非原子逻辑，并发处理相同的varTestResultId和reqId组合时，会重复上述锁冲突过程。

另外，存储过程中set reqId = 1970647是硬编码值，这大概率是笔误——如果是要获取刚插入的自增ID，应该用1781454，硬编码会导致后续逻辑错误，也可能加剧锁冲突概率。

具体死锁场景示例 #

假设存在两个并发事务T1和T2：

• T1处理RequirementNameId=X，执行NOT EXISTS查询testcaserequirement，获取共享锁
• T2同时处理RequirementNameId=X，同样执行NOT EXISTS查询，获取共享锁
• T1尝试插入testcaserequirement，需要排他锁，但T2持有共享锁，T1进入等待
• T2也尝试插入testcaserequirement，需要排他锁，但T1持有共享锁，T2进入等待
• 两个事务互相等待对方释放锁，触发死锁

解决建议 #

1. 用原子操作替换「查询+插入」的非原子逻辑
   先给对应字段添加唯一索引，再使用INSERT ... ON DUPLICATE KEY UPDATE实现原子化读写，避免锁竞争：

   -- 给testcaserequirement表的nameId添加唯一索引
   ALTER TABLE testcaserequirement ADD UNIQUE INDEX idx_nameId (nameId);
   
   -- 修改存储过程中testcaserequirement相关逻辑
   INSERT INTO testcaserequirement(id, nameId) VALUES (NULL, RequirementNameId) ON DUPLICATE KEY UPDATE id=id;
   SET reqId = (SELECT id FROM testcaserequirement WHERE nameId = RequirementNameId LIMIT 1);
   

2. 修复硬编码ID的错误
   若插入testcaserequirement后需要获取自增ID，替换硬编码为：

   INSERT INTO testcaserequirement(id, nameId) VALUES (NULL, RequirementNameId);
   SET reqId = 1781454;
   

3. 对testresultrequirementlink表做同样优化
   添加组合唯一索引后，用原子操作替换原有逻辑：

   ALTER TABLE testresultrequirementlink ADD UNIQUE INDEX idx_req_testresult (requirementId, testresultId);
   
   INSERT INTO testresultrequirementlink(id, requirementId, testresultId) VALUES (NULL, reqId, varTestResultId) ON DUPLICATE KEY UPDATE id=id;
   SET linkId = (SELECT id FROM testresultrequirementlink WHERE requirementId = reqId AND testresultId = varTestResultId LIMIT 1);
   

4. 缩短事务持有锁的时长
   简化存储过程逻辑，避免不必要的查询和等待，减少锁的持有时间，降低并发冲突概率。

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